Django使用select_related关联查询无法返回关联表字段问题

场景说明 #

存在canaisAssoc和formatosAssoc两张关联表，需从canaisAssoc关联的Canais_dados表获取nomeCanais字段，从formatosAssoc关联的Formatos_dados表获取nomeFormato字段，用于通过AJAX实现模板下拉框联动。

问题详情 #

执行查询语句：

canaisAssoc.objects.select_related('idCanal').filter(idSite_id = site, ativo = True).all()

打印SQL显示已正确关联Canais_dados表并查询到nomeCanais，在phpMyAdmin中执行该SQL能得到预期结果，但使用Django的serialize方法序列化后，仅返回canaisAssoc自身的idSite、idCanal、ativo字段，未包含关联表的nomeCanais。尝试使用objects.raw执行相同SQL也无法解决该问题。

期望结果 #

JSON返回需包含关联表的字段，示例如下：

{"channel": [{"model": "campanhas.canaisassoc", "pk": 14, "fields": {"idSite": 3, "idCanal": 13, "ativo": true,"nomeCanais": "Canal Y"}},...]}

相关代码 #

Models.py #

class Canais_dados(models.Model):
    id = models.AutoField(primary_key=True)
    nomeCanais = models.TextField()
    ativo = models.BooleanField(default=True)
    def __str__(self):
        return self.nomeCanais

class Formatos_dados(models.Model):
    id = models.AutoField(primary_key=True)
    nomeFormato = models.TextField()
    ativo = models.BooleanField(default=True)
    def __str__(self):
        return self.nomeFormato    

class canaisAssoc(models.Model):
    id = models.AutoField(primary_key=True)
    idSite = models.ForeignKey(Sites_dados, on_delete=models.CASCADE, related_name='idSitesCanaisAssoc',null=False)
    idCanal = models.ForeignKey(Canais_dados, on_delete=models.CASCADE, related_name='idCanaisCanaisAssoc',null=False)
    ativo = models.BooleanField(default=True,null=True)  

class formatosAssoc(models.Model):
    id = models.AutoField(primary_key=True)
    idSite = models.ForeignKey(Sites_dados, on_delete=models.CASCADE, related_name='idSiteFormatoAssoc',null=False)
    idFormato = models.ForeignKey(Formatos_dados, on_delete=models.CASCADE, related_name='idFormatoFormatoAssoc',null=False)
    ativo = models.BooleanField(default=True,null=True)

views.py #

# AJAX
def load_channel(request,site):
  
    channels = canaisAssoc.objects.select_related('idCanal').filter(idSite_id = site, ativo = True).all()
    formats = formatosAssoc.objects.select_related('idFormato').filter(idSite_id = site,ativo = True).all()
    channelName = serialize("json",channels)
    serializedChannel = json.loads(channelName)
    formatName = serialize("json",formats)
    serializedFormat = json.loads(formatName)
    return JsonResponse({'formats':serializedFormat,'channel': serializedChannel})

解决方案 #

Django默认的serialize方法仅序列化模型自身字段，不会自动包含关联对象的字段，可通过以下两种方式解决：

方法1：手动构造字典添加关联字段 #

遍历查询结果，手动从关联对象中提取所需字段并添加到序列化结构中：

def load_channel(request, site):
    channels = canaisAssoc.objects.select_related('idCanal').filter(idSite_id=site, ativo=True)
    formats = formatosAssoc.objects.select_related('idFormato').filter(idSite_id=site, ativo=True)
    
    # 处理渠道数据
    serialized_channel = []
    for channel in channels:
        channel_dict = {
            "model": "campanhas.canaisassoc",
            "pk": channel.pk,
            "fields": {
                "idSite": channel.idSite_id,
                "idCanal": channel.idCanal_id,
                "ativo": channel.ativo,
                "nomeCanais": channel.idCanal.nomeCanais
            }
        }
        serialized_channel.append(channel_dict)
    
    # 处理格式数据
    serialized_format = []
    for fmt in formats:
        fmt_dict = {
            "model": "campanhas.formatosassoc",
            "pk": fmt.pk,
            "fields": {
                "idSite": fmt.idSite_id,
                "idFormato": fmt.idFormato_id,
                "ativo": fmt.ativo,
                "nomeFormato": fmt.idFormato.nomeFormato
            }
        }
        serialized_format.append(fmt_dict)
    
    return JsonResponse({'formats': serialized_format, 'channel': serialized_channel})

方法2：使用自定义Serializer（推荐） #

若项目使用Django REST Framework，可创建自定义Serializer来直接包含关联字段：

# serializers.py
from rest_framework import serializers
from .models import canaisAssoc, formatosAssoc

class CanaisAssocSerializer(serializers.ModelSerializer):
    nomeCanais = serializers.CharField(source='idCanal.nomeCanais')
    
    class Meta:
        model = canaisAssoc
        fields = ['id', 'idSite', 'idCanal', 'ativo', 'nomeCanais']

class FormatosAssocSerializer(serializers.ModelSerializer):
    nomeFormato = serializers.CharField(source='idFormato.nomeFormato')
    
    class Meta:
        model = formatosAssoc
        fields = ['id', 'idSite', 'idFormato', 'ativo', 'nomeFormato']

# views.py修改后代码
from .serializers import CanaisAssocSerializer, FormatosAssocSerializer

def load_channel(request, site):
    channels = canaisAssoc.objects.select_related('idCanal').filter(idSite_id=site, ativo=True)
    formats = formatosAssoc.objects.select_related('idFormato').filter(idSite_id=site, ativo=True)
    
    channel_data = CanaisAssocSerializer(channels, many=True).data
    format_data = FormatosAssocSerializer(formats, many=True).data
    
    # 转换为目标序列化格式
    serialized_channel = []
    for item in channel_data:
        serialized_channel.append({
            "model": "campanhas.canaisassoc",
            "pk": item['id'],
            "fields": {
                "idSite": item['idSite'],
                "idCanal": item['idCanal'],
                "ativo": item['ativo'],
                "nomeCanais": item['nomeCanais']
            }
        })
    
    serialized_format = []
    for item in format_data:
        serialized_format.append({
            "model": "campanhas.formatosassoc",
            "pk": item['id'],
            "fields": {
                "idSite": item['idSite'],
                "idFormato": item['idFormato'],
                "ativo": item['ativo'],
                "nomeFormato": item['nomeFormato']
            }
        })
    
    return JsonResponse({'formats': serialized_format, 'channel': serialized_channel})

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