Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Django Inner Join Queryset

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.

like image 770
user1998511 Avatar asked Jun 29 '13 02:06

user1998511


People also ask

How do you inner join in Django ORM?

In Django, there is a method called select_related that will allow us to make inner-join. Here our goal is to select and display the name of the city, state, and country of a publication but all of them reside at different tables. I am going to show how you can easily solve this problem.

How do I create a join query in Django?

10. Join Queries. Join can be done with select_related method: Django defines this function as Returns a QuerySet that will “follow” foreign-key relationships, selecting additional related-object data when it executes its query.

How do I merge two QuerySets in Django?

The SolutionThe Python union operator can be used to combine QuerySets that belong to the same model. You can also use the chain() method from the Itertools module, which allows you to combine two or more QuerySets from different models through concatenation.

How do I merge two Django models?

1 Answer. Show activity on this post. In your models Device and History models are related with a foreign key from History to DeviceModel, this mean when you have a History object you can retrieve the Device model related to it, and viceversa (if you have a Device you can get its History).


1 Answers

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?

like image 134
okm Avatar answered Oct 22 '22 14:10

okm