Django double join via multiple models

Question

Situation

I have 3 models. Model A, model B and model C.

class A(Model):
    B_id = ForeignKey(B)
    id = IntegerField()
    pub_date = DateField()

class B(Model):
    id = IntegerField(primary_key = True)

class C(Model):
    B_id = ForeignKey(B)
    pub_date = DateField()
    information = TextField()

Model A and model C both have a field called B_id, which is a foreign key to model B. So there is no direct connection from B to A or C.

Goal

I'm given an ID from model A. Now I need all informations from C where the pub_date matches the pub_date in A and the B_id matches the B_id in A.

In SQL, this would be a pretty easy query:

SELECT C.information FROM A JOIN C USING(B_id, pub_date) WHERE A.id = 1234;

But I simply can't figure out how to do this in Django ORM syntax, as a filter()-clause only works if there is a direct connection path from C to A.

--

Edit: Please note that there is more than one row in A that matches one id.

Tim Yates · Accepted Answer · 2011-05-26 15:08:28Z

4

EDIT:

Try this:

 A.objects.filter(B_id__c__pub_date=F('pub_date'))

I'm not 100% sure about the capitalization on the filter string, but the F() predicate is what you are looking for. See the docs.

edited May 26, 2011 at 15:08

answered May 26, 2011 at 14:40

Tim Yates

5,3112 gold badges32 silver badges29 bronze badges

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

4 Comments

Danilo Bargen Over a year ago

I'm sorry for not mentioning it before, but there is more than one row in A that matches one id (I updated the question to reflect this fact). At first I tried to get all the A objects and use an __in= filter condition, but then it won't do the comparison pairwise and I might get more results than expected.

Tim Yates Over a year ago

@Danilo Makes sense looking at your join statement. I just saw "I'm given an ID". See my update.

Danilo Bargen Over a year ago

Cool, thanks :) I had to change the query a little, but the following way it seems to work: C.objects.filter(B_id__A__id=1234, B_id__A__pub_date=F('pub_date'). The raw SQL that is generated also looks quite OK, even though an unnecessary inner join was made. But that won't do any harm :)

Danilo Bargen Over a year ago

Edit: Sorry, I removed my last comment about the query not working. It works perfectly, it was an issue with my database.

Collectives™ on Stack Overflow

Django double join via multiple models

Situation

Goal

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

Situation

Goal

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related