模型和代码更改以获取带有多个查找表的DB的查询结果

Question

我正在构建的Django应用程序管理客户信息。这个问题的简短版本是如何构建等同于此SQL语句的Django查询...

select cl.id, cl.first, cl.last, ad.zipcode, ph.phone_number, em.email_address
from client.clients as cl
   join client.addresses as ad on cl.id=ad.client_id
   join client.phones as ph on cl.id=ph.client_id
   join client.email_addresses as em on cl.id=em.client_id 
where cl.status_id=1
   and ad.type_id=1
   and ph.type_id=1
   and em.type_id=1;

...给定以下模型，从缩写的客户端开始：

class Client(models.Model):
    id = models.IntegerField(primary_key=True)     
    last = models.CharField(max_length=32)
    first = models.CharField(max_length=32)

地址模型：

class Address(models.Model):
    id = models.IntegerField(primary_key=True)
    client = models.ForeignKey(
        'Client',
        on_delete=models.DO_NOTHING,
        blank=False,
        null=False)
    type = 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,
        blank=False,
        null=False)
    type_id = models.ForeignKey(
        PhoneType,        
        on_delete=models.PROTECT,
        blank=False,
        null=False)
    is_primary = models.BooleanField
    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,
        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)

最后，应包含QuerySet的clientListView：

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

    def get_queryset(self):
        return Client.objects.order_by('-id').filter(status_id=3).select_related(Phone)

上面的get_queryset并没有接近，但是最终，我需要从上面的SQL语句中显示的所有查找表中获取所有相关数据，到目前为止我拼凑在一起的select_recated和prefetch_rected子句的组合已经起作用。

Answer 1

获取此信息的方法有很多 - 这取决于您要访问的方式。 Django文档是一个很好的来源

，这里是一个示例，使用 queryset。 prefetch_releated（）（通常是最简单的），

clients = Client.objects.filter(status_id=3).prefetch_related(
    "addresses",
    "phones",
    "emails",
)  # will run all queries at once here

print(clients[0].emails[0].email_address)  # will not run additional query here

这是一个示例，使用 queryset.values（）

clients = Client.objects.filter(status_id=3).values(
    "id",
    "first",
    "last",
    "addressess__zip_code",
    "phones__phone_number",
    "emails__email_address"
)
print(clients[0]['emails__email_address'])

这是一个示例，使用 queryset.annotate（） and

clients = Client.objects.filter(status_id=3).annotate(
    zip_code=F("addressess__zip_code"),
    phone_number=F("phones__phone_number"),
    email_address=F("emails__email_address"),
)
print(clients[0].email_address)

（注意 - 我不确定骗子在这里如何工作 - 可以使用Postgres -extific

---

如果其他所有失败，您可以随时写 RAW SQL ：

clients = Client.objects.raw_sql("SELECT my_field FROM clients JOIN ...")
print(clients[0]["my_field"])