Django多查找表查询：实现指定SQL的ORM代码及模型修改方案

第一步：修正现有模型定义的错误 #

现有模型存在字段缺失、命名不统一、语法错误问题，先做如下修正：

# client/models.py
from django.db import models

# 假设已存在的查找表模型（你项目中已定义，无需修改）
class ClientStatus(models.Model):
    id = models.IntegerField(primary_key=True)
    name = models.CharField(max_length=32)

class AddressType(models.Model):
    id = models.IntegerField(primary_key=True)
    name = models.CharField(max_length=32)

class PhoneType(models.Model):
    id = models.IntegerField(primary_key=True)
    name = models.CharField(max_length=32)

class EmailType(models.Model):
    id = models.IntegerField(primary_key=True)
    name = models.CharField(max_length=32)

# 修正后的业务模型
class Client(models.Model):
    id = models.IntegerField(primary_key=True)     
    last = models.CharField(max_length=32)
    first = models.CharField(max_length=32)
    # 补全原模型缺失的status_id字段，匹配SQL和原有视图的过滤逻辑
    status_id = models.ForeignKey(
        ClientStatus,
        on_delete=models.DO_NOTHING,
        blank=False,
        null=False
    )

class Address(models.Model):
    id = models.IntegerField(primary_key=True)
    client = models.ForeignKey(
        Client,
        on_delete=models.DO_NOTHING,
        related_name='addresses', # 新增反向查询别名，简化关联查询写法
        blank=False,
        null=False
    )
    # 统一外键命名为type_id，和其他关联表规则一致，匹配SQL过滤字段
    type_id = models.ForeignKey(
        AddressType,
        on_delete=models.DO_NOTHING,
        blank=False,
        null=False
    )
    street = models.CharField(max_length=32, blank=True, null=True)
    city = models.CharField(max_length=32, blank=True, null=True)
    state = models.CharField(max_length=2, blank=True, null=True)
    zipcode = models.CharField(max_length=10, blank=True, null=True)

class Phone(models.Model):
    id = models.IntegerField(primary_key=True)
    client = models.ForeignKey(
        Client,
        on_delete=models.DO_NOTHING,
        related_name='phones',
        blank=False,
        null=False
    )
    type_id = models.ForeignKey(
        PhoneType,        
        on_delete=models.PROTECT,
        blank=False,
        null=False
    )
    # 修正原字段语法错误：缺少括号实例化
    is_primary = models.BooleanField(default=False)
    country_code = models.CharField(max_length=5)
    phone_number = models.CharField(max_length=16)

class EmailAddress(models.Model):
    id = models.IntegerField(primary_key=True)
    client = models.ForeignKey(
        Client,
        on_delete=models.PROTECT,
        related_name='emails',
        blank=False,
        null=False
    )  
    type_id = models.ForeignKey(
        EmailType,        
        on_delete=models.PROTECT,
        blank=False,
        null=False
    )
    email_address = models.CharField(max_length=128, blank=False, null=False)

第二步：编写与目标SQL完全等价的ORM查询 #

原SQL为4表INNER JOIN加等值过滤，select_related仅支持一对一/多对一正向关联，普通prefetch_related会拆分查询无法实现JOIN阶段过滤，正确写法如下：

from django.db.models import F

# 该查询生成的SQL与你给出的原生SQL逻辑完全一致
queryset = Client.objects.filter(
    status_id=1,
    # 关联过滤条件会自动生成INNER JOIN逻辑
    addresses__type_id=1,
    phones__type_id=1,
    emails__type_id=1
).annotate(
    # 将关联表字段提取为当前查询集的属性，和SQL SELECT字段一一对应
    zipcode=F('addresses__zipcode'),
    phone_number=F('phones__phone_number'),
    email_address=F('emails__email_address')
).values(
    'id', 'first', 'last', 'zipcode', 'phone_number', 'email_address'
).order_by('-id')

注意：如果单个客户存在多个type_id=1的地址/电话/邮箱，该查询会和原生SQL行为一致，返回多条对应记录。如果业务上每个客户仅存在一条type_id=1的关联数据，可以在查询末尾加.distinct()去重。

第三步：修改ClientListView代码 #

替换原有错误的get_queryset方法即可：

from django.views.generic import ListView
from django.db.models import F
from .models import Client

class ClientListView(ListView):
    model = Client
    template_name = 'client/client_list.html'
    context_object_name = 'clients'

    def get_queryset(self):
        return Client.objects.filter(
            status_id=1,
            addresses__type_id=1,
            phones__type_id=1,
            emails__type_id=1
        ).annotate(
            zipcode=F('addresses__zipcode'),
            phone_number=F('phones__phone_number'),
            email_address=F('emails__email_address')
        ).values(
            'id', 'first', 'last', 'zipcode', 'phone_number', 'email_address'
        ).order_by('-id')

模板中可直接通过{{ client.zipcode }}、{{ client.phone_number }}、{{ client.email_address }}访问关联字段，无需额外处理。

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