Django ORM: Filter results by values from list, limit answers per value?

I'm using Django 2.0 and have a Content model with a ForeignKey(User, ...). I also have a list of user IDs for which I'd like to fetch that Content, ordered by "newest first", but only up to 25 elements per user. I know I can do this:

Content.objects.filter(user_id__in=[1, 2, 3, ...]).order_by('-id')


...to fetch all the Content objects created by each of these users, plus I'll get it all sorted with newest elements first. But I'd like to fetch up to 25 elements for each of these users (some users might create hundreds of these objects, some might create zero). There's of course the dumb way:

for user in [1, 2, 3, ...]:
Content.objects.filter(user_id=user).order_by('-id')[:25]


This however hits the database as many times as there's objects in the user ID list, and that goes quite high (around 100 or so per page view). Is there any way to optimize this case? (I've tried looking around select_related, but that seems to fetch as many related models as possible.)

select_related

1 Answer
1

There are plenty of ways to form a greatest-n-per-group query, but in this case you could form a union of top-n queries for each user:

contents = Content.objects.
none().
union(*[Content.objects.
filter(user_id=uid).
order_by('-id')[:25] for uid in user_ids],
all=True)


Using prefetch_related() you could then produce a queryset that fetches the users and injects an attribute of latest content:

prefetch_related()

users = User.objects.
filter(id__in=user_ids).
prefetch_related(models.Prefetch(
'content_set',
queryset=contents,
to_attr='latest_content'))


By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.