Django's Multijoin Queries

Question

What is the best and / or fastest way to execute multijoin queries in Django using ORM and QuerySet API?

Answer 1

If you are trying to join tables associated with ForeignKeys or ManyToManyField relationships, you can use double underscore syntax. For example, if you have the following models:

class Foo(models.Model): name = models.CharField(max_length=255) class FizzBuzz(models.Model): bleh = models.CharField(max_length=255) class Bar(models.Model): foo = models.ForeignKey(Foo) fizzbuzz = models.ForeignKey(FizzBuzz) 

You can do something like:

 Fizzbuzz.objects.filter(bar__foo__name = "Adrian") 

Answer 2

Do not use the API ;-) Seriously, if your JOIN is complex, you should see a significant increase in performance by abandoning SQL rather than using the API. And this does not mean that you need to get dirty dirty SQL all over your beautiful Python code; just create a user manager to handle the JOIN, and then use the rest of your code, not just SQL.

Also, I was just in DjangoCon, where they had a workshop on high-performance Django, and one of the key things that I took away from him was that if performance is a real problem (and you plan to have significant traffic someday ), you really should not do JOINs in the first place, because they make scaling your application, while maintaining decent performance almost impossible.

Here is a Google video made from a conversation: http://www.youtube.com/watch?v=D-4UN4MkSyI&feature=PlayList&p=D415FAF806EC47A1&index=20

Of course, if you know that your application will never have to deal with such a scaling problem, open :-) And if you are also not worried about the efficiency of using the API, then you really do not need to worry about (at least insignificant) ( AFAIK) the difference in performance between using one API method over another.

Just use: http://docs.djangoproject.com/en/dev/topics/db/queries/#lookups-that-span-relationships

Hope this helps (and if it doesn't, we hope some kind of true Jango hacker can jump in and explain why Method X really has a noticeable performance difference).

Answer 3

Use the queryset.query.join method, but only if the other method described here (using double underscores) is not adequate.

Answer 4

The Caktus blog has an answer to this question: http://www.caktusgroup.com/blog/2009/09/28/custom-joins-with-djangos-queryjoin/

Basically, there is a hidden QuerySet.query.join method that allows you to add custom connections.