CODEWITHSUNDEEP
djangoormjoin
bvk
bvk

Reputation: 395

How to avoid n+1 select in Django?

I have a very simple datamodel with a one-to-many relationship between video and comments:

class Video(models.Model):
    url = models.URLField(unique=True)
    .....

class Comment(models.Model):
    title = models.CharField(max_length=128)
    video = models.ForeignKey('Video')
        .....

I want to query for videos and grab the whole object graph (videos with all the comments). Looking at the SQL, I see it does two selects, one for the Videos and one for the Comments. How do I avoid that? I want to do a join and grab everything at once.

Is it possible to do this with Django?

Upvotes: 16

Views: 9544

Answers (4)

Bite code
Bite code

Reputation: 597341

For ForeignKey, you can use select_related():

Comment.objects.select_related('video').all()

It will generate one query only, gathering comments for you as well as videos.

For something more complex (such as M2M), you need an external app such as unjoinify to make optimizations but it uses SQL queries to then put them back in objects.

If you are unconfortable with this (I am), you have some alternatives:

Upvotes: 20

TheDninoo
TheDninoo

Reputation: 1

As I don't have enough reputations to comment, (lost all my previous reputation out of a joke of a fiend ahah). I will just advise people reading the solutions to be careful when using select_related because it loads all your data in the memory, and I you don't have enough memory, it can break your app, so be careful using it

Upvotes: -1

arustgi
arustgi

Reputation: 848

What you need to do is use the select_related on the Comment.

Lets say you need to find the all the videos whose title starts with 'The' and the comments related to it

comments = Comment.objects.filter(video__title__starts_with='The')
           .select_related('video').all()

This will load all the comments and the appropriate Video object for that comment. You will still need to pivot over the Video to iterate over the videos. Use the python itertools.groupby function to do that pivoting in memory.

Upvotes: 4

fijter
fijter

Reputation: 18077

See if select related works the way you expect it to, it was made just for that.

Upvotes: 1

Related Questions