3

In a Django app, I am doing query like below:

Products.objects.filter(category_id=[1, 2, 3])[:6]

I am getting back 6 items from category 1. What I want is form each category 2 items.

Alternatively I tried with Q like 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 iterate through the results to filter out and so on. The resulted query set is pretty large.

How can I make that query using Django ORM via single query without iterating through the whole queryset?

Alex Benz
  • 395
  • 4
  • 14

1 Answers1

1

I do not see any direct way to do that. Some workarounds I can think of:

Direct iteration (1 query) :

The most direct way, only involve one query. Remember to use iterator so you don't have the full queryset 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 iteration order it could be time consuming (for instance if all your category 1 items were inserted first). Throwing a random order .order('?') may help but be careful that it have some caveats (mainly, it's slow).


Query each category (as many queries as categories):

[
    p
    for category_id in [1, 2, 3]
    for p in Products.objects.filter(category_id=category_id)[:2]
]

The result is not a queryset anymore, but that may not matter.


Multiple rounds (2 queries if you want the first 2 of each):

Get the first of each categories, exclude them and then get the second ones.

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 it's a list and not a queryset anymore and also see the limitations on distinct(*field).

Seb D.
  • 5,046
  • 1
  • 28
  • 36