How to avoid n+1 select in django?

Question

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?

e-satis · Answer 1 · 2011-06-17T18:20:41.827

18

For ForeignKey, you can use selected_related():

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

It will generate one query only, gathering coments 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:

django-queryset-transform: not a full solution, but helps
django-batch-select: roughtly a select_related that works with M2M and reverse relations.

edited Jun 17 '11 at 18:20

answered Jun 17 '11 at 16:38

e-satis

551,433
107
289
326

I want to do the reverse, get the comments from the video. I want to query for videos but get all the comments for those videos and result in a sql statement like so: select * from video v join v.id=c.video_id where v.date>some_date. The idea is to get all all the videos and their comments in one query. – bvk Jun 17 '11 at 18:48
Sorry my sql statement I'm looking to produce was not correct. I want to generate the following: select * from Videos v left join Comment c on v.id = c.id where v.date>some_date. Essentially i want all the videos and their comments, even the videos without comments. This example _Comment.objects.filter(video__title__starts_with='The') .select_related('video').all()_ will not get the videos without comments. – bvk Jun 17 '11 at 19:02
This is why I gave you a link to other tools such as django-batch-select that can do this for you: "roughtly a select_related that works with M2M and REVERSE RELATIONS". – e-satis Jun 18 '11 at 08:15
Any updates to M2M relationships? The libraries pointed in here have not been updated in 10+ years... – allexiusw Dec 18 '21 at 17:53

score 4 · Answer 2 · answered Jun 17 '11 at 18:01

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.

score 1 · Answer 3 · answered Jun 17 '11 at 16:30

1

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

answered Jun 17 '11 at 16:30

fijter

17,137
2
23
28

How to avoid n+1 select in django?

3 Answers3

Linked