Batch retrieve the entry count per category item in facet filter

Question

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.

Answer 1

Good question. Try this.

...
{% set productIds = craft.products.relatedTo(category).ids() %}
{% set brandsFilterOptions = craft.categories.group('brandCategories').relatedTo({ sourceElement: productIds }).all() %}
{% set brandsFilterCountLookup = craft.products.with(['brandCategories']).relatedTo(brandsFilterOptions|column('id'))|group(product => product.brandCategories[0].title ?? 'None') %}

{% 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>({{ brandsFilterCountLookup[filter.title]|length }})</span>
        </label>
    </div>
{% endfor %}

If your products can only be categorised under a single category in each facet this should work ok, but it'll probably fall apart if products can be categorised under multiple categories in each facet.

If it doesn't work, I'd probably not worry too much about the per-category queries since they are "only" doing a COUNT(*), which is usually very fast compared to regular SELECT statements.