Django中基于两个公共字段关联Sig与Coord两张表的方法咨询

不推荐你用拼接字段的方式做关联，这种方案存在冗余存储、数据一致性风险、可能出现拼接冲突等问题，有更稳妥的实现方案，你可以根据自己的使用场景选择：

方案1：新增外键关联（适合长期使用、查询频率高的场景，性能最优） #

首先因为你的Coord表中marine_area和habitat的组合是唯一的，先给这个表加联合唯一约束：

class Coord(models.Model):
    id = models.IntegerField(db_column='Id', primary_key=True)
    marine_area = models.CharField(db_column='Marine_Area', max_length=255, blank=True, null=True)
    habitat = models.CharField(db_column='Habitat', max_length=255, blank=True, null=True)
    latitude = models.FloatField(db_column='Latitude', blank=True, null=True)
    longitude = models.FloatField(db_column='Longitude', blank=True, null=True)
    
    class Meta:
        constraints = [
            models.UniqueConstraint(fields=['marine_area', 'habitat'], name='unique_area_habitat')
        ]

然后给Sig表新增指向Coord的外键字段：

class Sig(models.Model):
    id = models.IntegerField(db_column='Id', primary_key=True)
    proportion = models.FloatField(db_column='Prop', blank=True, null=True)
    marine_area = models.CharField(db_column='Marine_Area', max_length=255, blank=True, null=True)
    habitat = models.CharField(db_column='Habitat', max_length=255, blank=True, null=True)
    # 新增外键字段
    coord = models.ForeignKey(Coord, on_delete=models.SET_NULL, null=True, blank=True, related_name='sigs')

执行makemigrations生成迁移文件后，在迁移文件中添加批量填充外键的逻辑，一次性把现有数据的关联关系建好：

# 迁移文件中新增 RunPython 操作，注意替换为你自己的app名称
def populate_coord_relation(apps, schema_editor):
    Sig = apps.get_model('your_app_name', 'Sig')
    Coord = apps.get_model('your_app_name', 'Coord')
    for sig in Sig.objects.all():
        try:
            matched_coord = Coord.objects.get(marine_area=sig.marine_area, habitat=sig.habitat)
            sig.coord = matched_coord
            sig.save()
        except Coord.DoesNotExist:
            pass

class Migration(migrations.Migration):
    # 原有自动生成的依赖等内容
    operations = [
        # 原有自动生成的新增字段操作
        migrations.RunPython(populate_coord_relation),
    ]

后续使用时可以直接通过外键取坐标，比如sig.coord.latitude，查询效率很高，也不存在数据一致性问题。

方案2：查询时直接关联（不需要修改表结构，适合临时取数、可视化的场景） #

如果你只是临时需要关联数据做可视化，不想改动现有表结构，可以直接在查询时做联合条件匹配，用原生SQL更简单直接：

query_sql = """
SELECT s.*, c.Latitude, c.Longitude 
FROM Sig s 
INNER JOIN Coord c ON s.Marine_Area = c.Marine_Area AND s.Habitat = c.Habitat
"""
sig_with_coords = Sig.objects.raw(query_sql)

直接遍历查询结果就能同时拿到proportion和对应经纬度，不需要改动任何现有模型和表结构。

拼接字段方案的弊端补充 #

如果坚持用拼接字段的方式也能实现关联，但存在明显缺陷：

• 冗余存储，拼接字段属于重复数据，后续更新marine_area或habitat时需要同步更新拼接字段，很容易出现数据不一致
• 存在拼接冲突风险，比如marine_area = "a_b" + habitat = "c" 和 marine_area = "a" + habitat = "b_c" 拼接结果完全一致，会导致关联错误
• 字符串索引的查询效率低于联合索引或者外键关联，数据量大的时候性能差距明显

内容的提问来源于stack exchange，提问作者Clementiiineee