Take the 2-minute tour ×

My data model consists of three main entities:

class User(models.Model):

class Source(models.Model):
    user = models.ForeignKey(User, related_name='iuser')
    country = models.ForeignKey(Country, on_delete=models.DO_NOTHING)
    description = models.CharField(max_length=100)

class Destination(models.Model):
    user = models.ForeignKey(User, related_name='wuser')
    country = models.ForeignKey(Country)

I am trying to create a queryset which is join all sources with destinations by user (many to many). In such a way I would have a table with all possible source/destination combinations for every user. In SQL I would simple JOIN the three tables and select the appropriate information from each table.

My question is how to perform the query? How to access the query data?

share|improve this question
add comment

1 Answer

up vote 0 down vote accepted

In django queries are done on the model object, its well documented. The queries or querysets are lazy and when they execute they generally return a list of dict, each dict in the list contains the field followed by the value eg: [{'user':'albert','country':'US and A :) ','description':'my description'},....].

All possible source,destination combinations for every user?

I think you will have to use a reverse relation ship to get this done eg:

my_joined_query = User.objects.values('user','source__country','source__description','destination__country')

notice that i'm using the smaller case name of the models Source and Destination which have ForeignKey relationship with User this will join all the three tabels go through the documentation its rich.


To make an inner join you will have to tell the query, this can be simply achieved by using __isnull=False on the reverse model name:

my_innerjoined_query = User.objects.filter(source__isnull=False,destination__isnull=False)

This should do a inner join on all the tables.

Then you can select what you want to display by using values as earlier.

hope that helps. :)

share|improve this answer
Thanks! It does the trick. I totally overlooked values function. However, values generates left outer joins which add empty element to the result. Filtering those only makes the query even more complex. Is it possible to make Django do the same thing only with inner joins? –  Xyand Sep 8 '11 at 18:21
use select_related() it will make sure that foreignkeys are not null –  Pannu Sep 8 '11 at 19:19
On what object should I use select_related()? –  Xyand Sep 8 '11 at 19:57
try using select_related instead of values on the same query :) –  Pannu Sep 9 '11 at 4:50
It doesn't work in my case, for two reasons: 1. select_related only supports forward relations 2. It is bound to a single model and doesn't allow choosing fields from different tables like values. So far I am using a simple raw SQL query with 3 inner joins –  Xyand Sep 9 '11 at 5:22
show 2 more comments

Your Answer


