Django ORM: Retrieving posts and latest comments without performing N+1 queries -

Django ORM: Retrieving posts and latest comments without performing N+1 queries -

i have standard, basic social application -- status updates (i.e., posts), , multiple comments per post.

given next simplified models, possible, using django's orm, efficiently retrieve posts and latest 2 comments associated each post, without performing n+1 queries? (that is, without performing separate query latest comments each post on page.)

class post(models.model): title = models.charfield(max_length=255) text = models.textfield() class comment(models.model): text = models.textfield() post = models.foreignkey(post, related_name='comments') class meta: ordering = ['-pk']

post.objects.prefetch_related('comments').all() fetches posts , comments, i'd retrieve limited number of comments per post only.

update:

i understand that, if can done @ using django's orm, must done version of prefetch_related. multiple queries totally okay, long avoid making n+1 queries per page.

what typical/recommended way of handling problem in django?

update 2:

there seems no direct , easy way efficiently simple query using django orm. there number of helpful solutions/approaches/workarounds in answers below, including:

caching latest comment ids in database performing raw sql query retrieving comment ids , doing grouping , "joining" in python limiting application displaying latest comment only

i didn't know 1 mark right because haven't gotten chance experiment of these methods yet -- awarded bounty hynekcer presenting number of options.

update 3:

i ended using @user1583799's solution.

prefetch_related('comments') fetch comments of posts.

i had same problem, , database postgresql. found way:

add fieldrelated_replies. note fieldtype arrayfield, back upwards in django1.8dev. re-create the code project(the version of django 1.7), alter 2 lines, works.(or utilize djorm-pg-array )

class post(models.model): related_replies = arrayfield(models.integerfield(), size=10, null=true)

and utilize 2 queries:

posts = model.post.object.filter() related_replies_id = chain(*[p.related_replies p in posts]) related_replies = models.comment.objects.filter( id__in=related_replies_id).select_related('created_by')[::1] # cache queryset p in posts: p.get_related_replies = [r r in related_replies if r.post_id == p.id]

when new comment comes, update related_replies.

django django-models django-queryset