Inner join query with distinct using Django ORM

Thu 08 July 2010

Disclaimer

I initially wrote a completely wrong article. Sorry for that. Thanks to astopy who let me know. Here under is a revised version.

Revised Article

One point of the Django documentation that I did not get right at first is : how to get the ORM to generate a bit complex join queries. Yeah, sorry, I learnt SQL before ORMs, as many people, and I definitely think in terms of relational models and SQL joins.

For example, given the following models involving deliveries of products in a driver schedule, how do you get the list of products appearing in a schedule ? (I know that this sounds a lot like a Data Base 101 course).

class Product(models.Model):
name = models.CharField(max_length=150)
class Schedule(models.Model):
name = models.CharField(max_length=150)
class Delivery(models.Model):
.... some data fields here
product=models.ForeignKey(Product)
schedule=models.ForeignKey(Schedule)

The answer is deceptively simple, just write :

s = Schedule.objects.get(....)
Product.objects.filter(delivery__schedule = s).distinct()

which roughly will translate into the following SQL :

select distinct(product.*) from product
inner join delivery on product.id = delivery.product_id
inner join schedule on schedule.id = delivery.schedule_id
where schedule.id = some_id

The important thing to notice is that in the line

Product.objects.filter(delivery__schedule = s).distinct()

you can use delivery as the beginning of the lookup parameters, while there is no delivery field in the Product class. Django will understand.

What is maybe a bit misleading (even if it is quite logical if you think about it), is that there actually is a field named delivery_set created on the Product class (it is a RelatedManager), but you can not use it in the lookup parameters. It took me a long time to figure this out, so it might be the case for other people too....

That said, everything is explained in the Django doc here even if for once, I find the explanation a bit perfunctory

Feed - About me

comments powered by Disqus