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

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
    Anyway 回复

    补充@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
    wquod 回复

    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