Django: Multiple table Model query

Question

I have tried it on the Q object and in the Django ORM but could not generate the query.

class Status(models.Model):
    id = models.AutoField(primary_key=True)
    name = models.AutoField(null=True, max_length=100)
    
    def __str__(self):
        return self.name
        
class Billing(models.Model):
    id = models.AutoField(primary_key=True)
    name = models.AutoField(null=True, max_length=1000)
    sr_number = models.AutoField(null=True, max_length=100)
    
    def __str__(self):
        return self.name
        
class BillingInfo(models.Model):
    id = models.AutoField(primary_key=True)
    billings = models.ForeignKey(Billing, null=True, on_delete=models.SET_NULL)
    net_amount = models.AutoField(null=True, max_length=100)
    company = models.AutoField(null=True, max_length=100)
    
    def __str__(self):
        return self.company
        
class BillingOutput(models.Model):
    id = models.AutoField(primary_key=True)
    billing_infos = models.ForeignKey(BillingInfo, null=True, on_delete=models.SET_NULL)
    lbl_name = models.AutoField(null=True, max_length=100)
    lbl_qty = models.AutoField(null=True, max_length=100)
    status = models.ForeignKey(Status, null=True, on_delete=models.SET_NULL)
    
    def __str__(self):
        return self.lbl_name

I required an equivalent ORM Query for the below raw SQL query:

select bio.* from billing bil
    inner join billing_infos bi on bil.id = bi.billings_id
    inner join billing_output bio on bi.id = bio.billing_infos_id
    where bo.status_id = 1
    order by bio.id desc;

Could someone please help me with this issue?

what is bo.status_id? You did not specify a table bo.

willeM_ Van Onsem
– willeM_ Van Onsem

2022-05-10 11:03:25 +00:00
Commented May 10, 2022 at 11:03 — willeM_ Van Onsem
– willeM_ Van Onsem, Commented May 10, 2022 at 11:03

willeM_ Van Onsem · Accepted Answer · 2022-05-10 11:07:37Z

1

You can use .select_related(…) ^[Django-doc] to retrieve the related billing_infos and billing of that billing_infos:

BillingOutput.objects.select_related(
    'billing_infos__billing'
).filter(status_id=1, billing_infos__billing__isnull=False).order_by('-id')

Note: Django will add by default an AutoField ^[Django-doc] as primary_key=True to a model with name id if you do not specify a primary key yourself, so there is no need to explicitly declare a primary key.

answered May 10, 2022 at 11:07

willeM_ Van Onsem

482k33 gold badges483 silver badges624 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

selva316 Over a year ago

Thanks, @willem-van-onsem. I want to fetch billing_infos table data also. Is that possible? ` select bio.*, bi.* from billing bil inner join billing_infos bi on bil.id = bi.billings_id inner join billing_output bio on bi.id = bio.billing_infos_id where bo.status_id = 1 order by bio.id desc; `

selva316 Over a year ago

The above query is left outer join, For an inner join what do I need to change in the ORM.

willeM_ Van Onsem Over a year ago

@selva316: nothing, the __isnull=False will eventually produce the same result since it filters out NULLs constructed by a LEFT OUTER JOIN. The Django ORM can sometimes optimize this slightly.

selva316 Over a year ago

Thanks, @Willem. I want to add conditions for billing_infos.company='XXX'. Is there any other way to add a where clause for another table?

willeM_ Van Onsem Over a year ago

@selva316: yes, add billing_infos__company='XXX' in the filter(..) clause.

Youssef Soltani · Accepted Answer · 2022-05-10 11:08:40Z

0

Actually you can do this :

qry1 = "select bio.* from billing bil
            inner join billing_infos bi on bil.id = bi.billings_id
            inner join billing_output bio on bi.id = bio.billing_infos_id
            where bo.status_id = 1
            order by bio.id desc;" 
bio = Billing.objects.raw(qry1 , [user_id])

answered May 10, 2022 at 11:08

Youssef Soltani

113 bronze badges

Collectives™ on Stack Overflow

Django: Multiple table Model query

2 Answers 2

5 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

5 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related