Django多表关联查询问题：如何获取Contact关联的Account名称

问题描述 #

作为Django新手，我正在开发一款轻量CRM系统，使用MySQL数据库的关联实体处理多对多关系。目前通过关联实体contactdeal连接deal与contact表，已用select_related实现两者的关联查询，但无法通过Contact表中的account_id外键获取Account表对应的name字段值，寻求解决方法。

相关代码 #

Models #

class Account1(models.Model):
    id = models.AutoField(primary_key=True)
    name = models.CharField(max_length=255, blank=True, null=True)       
    address = models.CharField(max_length=255, blank=True, null=True)    
    city = models.CharField(max_length=255, blank=True, null=True)       
    state = models.CharField(max_length=255, blank=True, null=True)      
    country = models.CharField(max_length=255, blank=True, null=True)    
    description = models.CharField(max_length=255, blank=True, null=True)
    phone = models.CharField(max_length=255, blank=True, null=True)      
    email = models.CharField(max_length=255, blank=True, null=True)
    biztype = models.CharField(max_length=255, blank=True, null=True)
    notes = models.CharField(max_length=255, blank=True, null=True)

    class Meta:
        managed = False
        db_table = 'account'

class Contact1(models.Model):
    contact_id = models.AutoField(primary_key=True)
    first_name = models.CharField(max_length=255, blank=True, null=True)
    last_name = models.CharField(max_length=255, blank=True, null=True)
    account_id = models.ForeignKey(Account1, models.DO_NOTHING, db_column='account_id', blank=False, null=False)
    contact_notes = models.TextField(blank=True, null=True)
    contact_email = models.CharField(max_length=255, blank=True, null=True)
    contact_phone = models.CharField(max_length=255, blank=True, null=True)
    contact_status = models.CharField(max_length=255, blank=True, null=True)
    
    class Meta:
        managed = False
        db_table = 'contact'

class Contactdeal(models.Model):
    contactdeal_id = models.AutoField(primary_key=True)
    contactdeal_deal_fk = models.ForeignKey('Deal', models.DO_NOTHING, db_column='contactdeal_deal_fk', blank=True, null=True)
    contactdeal_contact_fk = models.ForeignKey(Contact1, models.DO_NOTHING, related_name='contactdeal_contact_fk', db_column='contactdeal_contact_fk', blank=True, null=True)

    class Meta:
        managed = False
        db_table = 'contactdeal'

class Deal(models.Model):
    deal_id = models.AutoField(primary_key=True)
    deal_name = models.CharField(max_length=255, blank=True, null=True)
    est_value = models.DecimalField(max_digits=13, decimal_places=2, blank=True, null=True)
    deal_notes = models.TextField(blank=True, null=True)

    class Meta:
        managed = False
        db_table = 'deal'

View #

def deal(request):
    deal = Contactdeal.objects.select_related('contactdeal_contact_fk', 'contactdeal_deal_fk')
    template = loader.get_template('deal.html')
    context = {
        'deal' : deal,
    }
    return HttpResponse(template.render(context, request))

Template #

{% for x in deal %}
<tbody>
<tr class="table-active">
    <td>{{x.contactdeal_deal_fk.deal_name}}</td>
    <td>{{x.contactdeal_deal_fk.est_value}}</td>
    <td>{{x.contactdeal_contact_fk.first_name}}</td>
    <td>{{x.contactdeal_contact_fk.account_id}}</td>
    <td><a href="updateaccount/{{ x.id }}">Update</a></td>
    <td><a href="deleteaccount/{{x.id}}">Delete</a></td>
</tr>
</tbody>
{% endfor %}

解决步骤 #

1. 扩展select_related实现嵌套关联查询 #

当前select_related只关联了contactdeal_contact_fk和contactdeal_deal_fk，但没有关联Contact1对应的Account1表。需要通过**双下划线__**嵌套指定深层关联，这样能一次性拉取所有需要的数据，避免多次查询数据库。

修改View中的查询语句：

def deal(request):
    deal = Contactdeal.objects.select_related(
        'contactdeal_contact_fk',
        'contactdeal_contact_fk__account_id',  # 嵌套关联Account1表
        'contactdeal_deal_fk'
    )
    template = loader.get_template('deal.html')
    context = {
        'deal' : deal,
    }
    return HttpResponse(template.render(context, request))

2. 在模板中正确调用Account的name字段 #

原来的模板中{{x.contactdeal_contact_fk.account_id}}只会输出Account的ID值，要获取对应的name字段，需要访问关联对象的属性：

修改模板中的对应行：

<td>{{x.contactdeal_contact_fk.account_id.name}}</td>

可选优化：优化外键字段名 #

如果觉得account_id作为外键字段名容易混淆（Django中，外键字段实际存储的是关联对象，数据库列名通过db_column指定），可以修改Contact1的外键定义，让字段名更直观：

# 修改Contact1中的外键字段
account = models.ForeignKey(Account1, models.DO_NOTHING, db_column='account_id', blank=False, null=False)

修改后，模板中可以写成：

<td>{{x.contactdeal_contact_fk.account.name}}</td>

这样代码可读性更强，也符合Django的命名习惯。

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