CODEWITHSUNDEEP
djangofilterannotations
sebastian
sebastian

Reputation: 488

Django Aggregate on related field with filter

Following problem:
I have product groups containing products. These products can be visible in the frontend or not. I determine their visibility with the method frontend() (which contains a filter) like so:

product_groups.first().products.frontend()

Now I want to determine if I want to put a link for the product group on the homepage only if there are four or more products in it.
With annotations I would do:

product_groups.annotate(num_products=Count('products')).filter(num_products__gte=4)

But this gives me of cause the count of all products and not the count of products visible in the frontend.

So how do I put the additional filter frontend() into my query? To be clear, I want the Count() not on 'products' but on products.frontend().

Edit:
This is not a duplicate of the suggested question. If the filter function frontend() was simple enough to pull out the filter and stick it in the aggregate function, the suggested question would answer my problem.

My frontend() function is quite complicated and an aggregate of multiple other filter functions. So I would really like to use the frontend() function.

Edit:
This needs to work in Django 1.8.

Upvotes: 0

Views: 209

Answers (1)

dirkgroten
dirkgroten

Reputation: 20682

If you want to reuse the frontend() method on your Product model's Queryset, then you can use Subquery aggregate expressions:

# assumption: `Product` has a fk to `ProductGroup`
# assumption 2: frontend() returns a `Queryset` of `Product` and is a method of `Product` model's default `Queryset`
frontend_products = Product.objects.filter(product_group=OuterRef('pk')).frontend().values('product_group')
total_products = frontend_products.annotate(total=Count('pk')).values('total')
q = product_groups.annotate(num_frontend_products=Subquery(total_products, output_field=IntegerField()))

Note that this will populate num_frontend_products with None instead of 0 for the groups where there isn't any corresponding product. You might want to modify the queryset further with conditional annotations to replace None with 0.

Upvotes: 1

Related Questions