Django Inner Join Queryset

Question

I'm working with Django and I need to do a queryset using two inner joins.

I have three models A, B, and C and I want to do a query like the following in psql:

SELECT DISTINCT a FROM A
INNER JOIN B ON B.a_id = A.id
INNER JOIN C ON C.b_id = B.id;

Models: (only included relevant fields)

class A(models.Model):
    id = models.IntegerField(primary_key=True)

class B(models.Model):
    id = models.IntegerField(primary_key=True)
    a = models.ForeignKey(A, null=True, blank=True,on_delete=models.SET_NULL)

class C(models.Model):
    b = models.ForeignKey(B, null=True, on_delete=models.SET_NULL)

So everything in C links back to one thing in B and everything in B links back to one thing in A. I want to try and get all the distinct elements in A that have something in C.

How do I do this using django queryset? Thanks.

It would be great if you show your models, and tell what you are trying to accomplish — karthikr, Jun 29 '13 at 02:18

score 29 · Accepted Answer · edited Jun 18 '20 at 22:15

29

A.objects.filter(b__c__isnull=False) results a sql w/ same result:

SELECT DISTINCT a.* FROM a 
INNER JOIN b ON (a.id = b.a_id)
INNER JOIN c ON (b.id=c.b_id)
WHERE c.id IS NOT NULL;

P.S. Why do you use IntegerField instead of AutoField for ids?

edited Jun 18 '20 at 22:15

Rob Bednark

25,981
23
80
125

answered Jun 29 '13 at 03:05

okm

23,575
5
83
90

Awesome, thanks! I actually use AutoField, I just copied it over wrong. – user1998511 Jun 29 '13 at 03:19

Django Inner Join Queryset

1 Answers1

Linked