Django: Multiple table Model query-CodePudding

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?

CodePudding user response：

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.

CodePudding user response：

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])