CODEWITHSUNDEEP
pythondatabasedjangoorm
moriesta
moriesta

Reputation: 1200

prefetch limited number of related objects in django

I want to display list of Posts with 5 latest Comments for each of them. How do I do that with minimum number of db queries?

Post.objects.filter(...).prefetch_related('comment_set')

retrieves all comments while I need only few of them.

Upvotes: 1

Views: 374

Answers (1)

freakish
freakish

Reputation: 56527

I would go with two queries. First get posts:

posts = list(Post.objects.filter(...))

Now run raw SQL query with UNION (NOTE: omitted ordering for simplicity):

sql = "SELECT * FROM comments WHERE post_id=%s LIMIT 5"
query = []
for post in posts:
    query.append( sql % post.id )
query = " UNION ".join(query)

and run it:

comments = Comments.objects.raw(query)

After that you can loop over comments and group them on the Python side.

I haven't tried it, but it looks ok.

There are other possible solutions for your problem (possibly getting down to one query), have a look at this:

http://www.xaprb.com/blog/2006/12/07/how-to-select-the-firstleastmax-row-per-group-in-sql/

Upvotes: 1

Related Questions