Joining more than 2 tables for reports in django and extract all the fields from the joined table-CodePudding

I am joining the ClientDetails, AssignmentTable and CallDetails table to get a view as to which telecaller a particular client has been assigned to and get the latest call details as well. However I am unable to accomplish that using django ORM.

ISSUE:

I am trying to access the fields inside the assignment table and call table but I am getting only the ids and not the other fields.

Question:

How do I extract all the columns from the assignment and call details table which has the client id as 1?

This is the SQL Query that I am trying to come up with:

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;

Below is the model file:

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

CodePudding user response：

You can use prefetch_related() to achieve this. I just use some sample models here for better understanding.

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)

In your views.py function write the below lines to get the desired results

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

Note: If you use related_name in your ForeignKey relationship, make sure that you access with that name instead of model_set inside prefetch_related()