1

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:

Example of facet filter with counts

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.

1 Answer 1

3

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.

1
  • Thanks James! Works like a charm! 👏
    – Jesse
    Jan 19 at 22:03

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.