sql - Django prefetch_related with limit

Question

Is there a way to tell prefetch_related to only fetch a limited set of related objects? Lets say I am fetching a list of users and I know I want to fetch their recent comments. Instead of fetching comments for each user in a loop, I use prefetch_related to pre-fetch them at the time of fetching the users. My understanding is that this will fetch all the comments made by any user present in the result of the original query but I only want to show the latest 5 for each user.

How does this affect the performance if the list of comments is really huge? Is there a way to fetch only 5 comments for each user in a single (or 2) query? It doesn't have to be the same query as the original one for fetching users but that would be nice.

I essentially want to turn this

   users = User.objects.all()
   for user in users:
       user.comments.all()[:10]

into something like this

 User.objects.all().prefetch_related('comments', limit=10)

so if a user has 100s or 10000s of comments, they are not all loaded into memory. How would you do something like this in raw SQL?

Answer 1

I think there is a workaround now to in django new version as we have OuterRef and Subquery.

from django.db.models import OuterRef, Subquery, Prefetch

subqry = Subquery(Comment.objects 
    .filter(user_id=OuterRef('user_id')) 
    .values_list('id', flat=True)[:5])

User.objects.prefetch_related(
    Prefetch('comments', queryset=Comment.objects.filter(id__in=subqry)))