Django propagates filter results
In my Django app I am making a request like below:
Products.objects.filter(category_id=[1, 2, 3])[:6]
I am returning 6 items from category 1. What I want is the shape of each category 2.
Alternatively I tried to use Q
as below:
Products.objects.filter(Q(category_id=1)|Q(category_id=2)|Q(category_id=3))[:6]
I can get all the results and then loop through the results to filter, etc. The selected set of queries is quite large.
How can I make this request with Django ORM with one request without repeating the whole request?
source to share
I don't see a direct way to do this. Some workarounds I can think of:
Direct iteration (1 query):
The most direct way is to include only one request. Remember to use an iterator so you don't have a complete query in memory.
for product in Products.objects.filter(category_id__in=[1, 2, 3]).iterator():
# Add some logic to select your six items.
Of course, depending on the order of iterations, this can be time consuming (for example, if all your Category 1 items were inserted first). Tossing the random order .order('?')
can help, but be careful to have some caveats (it's slow, mostly).
Request each category (as many requests as categories):
[
p
for category_id in [1, 2, 3]
for p in Products.objects.filter(category_id=category_id)[:2]
]
The result is no longer a query, but that might not matter.
Multiple rounds (2 queries if you need the first 2 of them):
Get the first of each category, exclude them, and then get the second.
ids = [1, 2, 3] items = list(Products.objects.filter(category_id__in=ids).distinct('category')) items += list(Products.objects.filter(category_id__in=ids).exclude( id__in=[i.id for i in items], ).distinct('category'))
Same caveat that this is a list, not a set of queries anymore, and also see the limits on distinct(*field)
.
source to share