I'm building a facet filter using Craft Commerce products and Craft categories. I have a "Brand" category and each product entry has a brand defined. Same for the other facet filters that are built on categories.
I have a functional facet filter, but I would like to display the actual product counts, like the gray counters in this example:
I currently have the following (simplified) code:
...
{% set productIds = craft.products.relatedTo(category).ids() %}
{% set baseQuery = craft.products.relatedTo(category).relatedTo(filterParameters) %}
{% set brandsFilterOptions = craft.categories.group('brandCategories').relatedTo({ sourceElement: productIds }).all() %}
{% for filter in brandsFilterOptions %}
<div class="flex items-center">
<input type="checkbox" id="brand-{{ filter.id }}" name="brands[]" value="{{ filter.slug }}" {{ filter.slug in brandSlugs ? 'checked' }}>
<label for="brand-{{ filter.id }}">
{{ filter.title }}
<span>({{ baseQuery.relatedTo(filter.id).count() }})</span>
</label>
</div>
{% endfor %}
However, this is executing a query per checkbox item because of the count()
inside a foreach loop. It can easily trigger 30+ queries per page load. Is there a more performant solution possible?
Is there a way to execute a single query per category group using GROUP BY
and COUNT(*)
? Or even a single query for all category items? If I can store the results of a single query in a variable, I can use it as a lookup map when rendering the filters.