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

Question

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

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)

最后，应该包含查询集的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_related和prefetch_related条款我拼凑起来工作。

Answer 1

有很多方法可以获得这些信息——这取决于您想要如何访问。Django文档是一个很好的来源

下面是一个使用queryset.prefetch_related()(通常是最简单的)的例子

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()和"F"表达式

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)

(注意-我不确定dupes将如何在这里工作-可以使用类似postgresql特定的ArrayAgg)

---

如果所有这些都失败了，您可以始终编写原始SQL:

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