How to exclude rows with empty prefetch_related field

Question

I used prefetch_related with Prefetch:

prefetch_qs = Offer.objects.filter(price__gt=1000)
prefetch = Prefetch('offers', queryset=prefetch_qs)

How can I exclude rows with empty offers? It doesn't work, because annotate counted all offers (not filtered in prefetch):

filtered_qs = Product.objects.annotate(
    offers_count=Count('offers')
).filter(
    offers_count__gt=0
).prefetch_related(
    prefetch      
)

Todor · Accepted Answer · 2020-05-18T07:25:03.503

4

The Prefetch is being executed as a 2nd query after the products query, thus its not possible to filter out products based on the prefetch. You need to repeat the prefetch filtration as either Subquery or inside the Count which you are trying to make.

In order for the Count to work try the following:

filtered_qs = Product.objects.annotate(
    offers_count=Count('offers', filter=Q(offers__price__gt=1000))
).filter(
    offers_count__gt=0
).prefetch_related(
    prefetch
)

In order to do it with a Subquery you need something like this:

filtered_qs = Product.objects.annotate(
    offers_count=Subquery(
        prefetch_qs.filter(product=OuterRef('pk'))
            .values('product')
            .annotate(count=Count('pk'))
            .values('count')
    )
).filter(
    offers_count__gt=0
).prefetch_related(
    prefetch
)

The Subquery approach may look a little bit hard to understand why its done this way, I've tried to explain it in some old question here

edited May 18 '20 at 07:25

answered May 18 '20 at 06:57

Todor

15,307
5
55
62

In first case it should be `Count('offers', filter=Q(offers__price__gt=1000))`. And I should to add `offers__` to all filters :( – Konstantin Komissarov May 18 '20 at 07:09
subquery case works. But very slow. 4-5s instead 1s before :( – Konstantin Komissarov May 18 '20 at 07:23
1200ms average. Now I wand to find the way to add `offer__` to all my filters. Probably I should to use dict and add `offer__` to it's keys to get new dict – Konstantin Komissarov May 18 '20 at 07:41
Is the performance still bad with `SubqueryCount` approach mentioned by @alexandr-tatarinov ? I would first try to debug where the performance hit come from the subquery approach (its never late to learn more SQL) and only then return to count+filter as a last resort. – Todor May 18 '20 at 07:54
@alexandr-tatarinov method is long too. Django debug toolbar show two big slow queries. – Konstantin Komissarov May 18 '20 at 08:19

score 2 · Answer 2 · answered May 18 '20 at 07:03

2

To supplement @Todor answer: you can create a custom subquery type to simplify the 2nd approach and allow reuse.

class SubqueryCount(Subquery):
    template = '(SELECT COUNT(*) FROM (%(subquery)s) _sub)'
    output_field = IntegerField()

filtered_qs = Product.objects.annotate(
    offers_count=SubqueryCount(prefetch_qs.filter(product=OuterRef('pk'))
).filter(
    offers_count__gt=0
).prefetch_related(
    prefetch
)

answered May 18 '20 at 07:03

Alexandr Tatarinov

3,946
1
15
30

bolshoe spasibo :) – Konstantin Komissarov May 18 '20 at 07:14

How to exclude rows with empty prefetch_related field

2 Answers2