在django中为报表连接2个以上的表，并从连接的表中提取所有字段

Question

我正在加入ClientDetails、AssignmentTable和CallDetails表，以获取特定客户端已分配给哪个电视呼叫器的视图，并获取最新的呼叫详细信息。然而，我无法使用django-ORM来实现这一点。

问题：

我试图访问分配表和调用表中的字段，但我只得到id，而没有得到其他字段。

问题：

如何从客户端id为1的分配和调用详细信息表中提取所有列？

这是我试图提出的SQL查询：

SELECT t1.uid, t1.phone_number, t1.client_name, t1.base, t1.location, t2.assigner, t2.bpo_agent, t2.cro_agent, t3.bpo_status_id, t3.cro_status_id, t3.agent_id_id
FROM public.bpo_app_clientdetails t1
LEFT JOIN public.bpo_app_assignmentdetails t2 ON t1.uid = t2.client_id_id
LEFT JOIN public.bpo_app_calldetails t3 ON t1.uid = t3.client_id_id;

下面是模型文件：

class ClientDetails(models.Model):
uid = models.AutoField(primary_key=True)
phone_number = PhoneNumberField(unique=True)
client_name = models.CharField(max_length=50, blank=True, null=True)
base = models.CharField(max_length=50, blank=True, null=True)
location = models.CharField(max_length=50, blank=True, null=True)
class Meta:
verbose_name_plural = "Client Contact Detail Table"
def __str__(self):
return f"{self.phone_number}, {self.client_name}"

class AssignmentDetails(models.Model):
uid = models.AutoField(primary_key=True)
client_id = models.ForeignKey(
ClientDetails,
on_delete=models.PROTECT,
related_name='assignment_details'
)
date_and_time = models.DateTimeField(auto_now_add=True, blank=True)
assigner = models.ForeignKey(
User,on_delete=models.PROTECT,
related_name='AssignerAgent',
db_column='assigner',
)
bpo_agent = models.ForeignKey(
User,on_delete=models.PROTECT,
related_name='bpoAgent',
db_column='bpo_agent',
)
cro_agent = models.ForeignKey(
User,on_delete=models.PROTECT,
related_name='croAgent',
db_column='cro_agent',
)
class Meta:
verbose_name_plural = "Client Assignment Detail Table"
def __str__(self):
return f"{self.uid}"
class CallDetails(models.Model):
uid = models.AutoField(primary_key=True)
date_and_time = models.DateTimeField(auto_now_add=True, blank=True)
client_id = models.ForeignKey(
ClientDetails,
on_delete=models.PROTECT,
related_name='call_details'
)
agent_id = models.ForeignKey(EmployeeDetails_lk,on_delete=models.PROTECT)
bpo_status = models.ForeignKey(BpoStatus_lk,on_delete=models.PROTECT, blank=True, null=True)
cro_status = models.ForeignKey(CroStatus_lk,on_delete=models.PROTECT, blank=True, null=True)
required_loan_amt = models.CharField(max_length=50, blank=True, null=True)
remarks = models.CharField(max_length=500, blank=True, null=True)
loan_program = models.ForeignKey(LoanProgram_lk, on_delete=models.PROTECT, blank=True, null=True)
disbursement_bank = models.ForeignKey(Banks_lk, on_delete=models.PROTECT, limit_choices_to={'loan_disbursement_status': True}, blank=True, null=True)
class Meta:
verbose_name_plural = "Client Call Detail Table"
def __str__(self):
return f"{self.uid}"

>>> qry=ClientDetails.objects.values('assignment_details','call_details').filter(uid=1)
>>> qry
<QuerySet [{'assignment_details': 1, 'call_details': None}]>
>>> print(a.query)
SELECT "bpo_app_assignmentdetails"."uid", "bpo_app_calldetails"."uid" FROM "bpo_app_clientdetails" LEFT OUTER JOIN "bpo_app_assignmentdetails" ON ("bpo_app_clientdetails"."uid" = "bpo_app_assignmentdetails"."client_id_id") LEFT OUTER JOIN "bpo_app_calldetails" ON ("bpo_app_clientdetails"."uid" = "bpo_app_calldetails"."client_id_id") WHERE "bpo_app_clientdetails"."uid" = 1

Answer 1

您可以使用prefetch_related()来实现这一点。我只是在这里使用一些示例模型来更好地理解。

class Company(models.Model):
name = models.CharField(null=True, blank=True, max_length=100)
class Project(models.Model):
name = models.CharField(null=True, blank=True, max_length=100)
company = models.ForeignKey(Company, on_delete=models.CASCADE)
class Employee(models.Model):
name = models.CharField(null=True, blank=True, max_length=100)
company = models.ForeignKey(Company, on_delete=models.CASCADE)

在您的views.py函数中，写下以下几行以获得所需的结果

companies = Company.objects.filter(id=1).prefetch_related('project_set', 'employee_set')
for company in companies:
print(company.project_set.values()) # This will print this company projects
print(company.employee_set.values()) # This will print this company employees

注意：如果在ForeignKey关系中使用related_name，请确保使用该名称访问，而不是在prefetch_related((中使用model_set