如何排除具有空prefetch_related字段的行

由苏浅蓝发布于 2020-05-18 15:11:10

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      
)

Anyway

2020-05-18 15:11:10

补充@Todor答案：您可以创建自定义子查询类型以简化第二种方法并允许重用。

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
)

wquod

2020-05-18 15:11:10

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.

为了使计数工作，请尝试以下操作：

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

为了对子查询执行此操作，您需要这样的操作：

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

苏浅蓝

这家伙很懒，什么都没留下

积分
0
话题
0
评论
3239
注册排名
2337