0

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.

sebastian
  • 478
  • 8
  • 22
  • Whatever filter is in `frontend()` you can apply it to `Count` as well: `Count(products, filter=Q(...))` – dirkgroten Oct 01 '19 at 09:43
  • @dirkgroten that's what I thought. But I cannot use `frontend()` directly? The filters I've got in `frontend()` are quite complicated and an aggregate of other functions themself. – sebastian Oct 01 '19 at 09:51
  • Possible duplicate of [How to filter objects for count annotation in Django?](https://stackoverflow.com/questions/30752268/how-to-filter-objects-for-count-annotation-in-django) – Julien Kieffer Oct 01 '19 at 10:01

1 Answers1

1

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.

dirkgroten
  • 20,112
  • 2
  • 29
  • 42