<p>Good question. Try this.</p>
<pre class="lang-twig prettyprint-override"><code>...
{% 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 =&gt; product.brandCategories[0].title ?? 'None') %}

{% for filter in brandsFilterOptions %}
    &lt;div class=&quot;flex items-center&quot;&gt;
        &lt;input type=&quot;checkbox&quot; id=&quot;brand-{{ filter.id }}&quot; name=&quot;brands[]&quot; value=&quot;{{ filter.slug }}&quot; {{ filter.slug in brandSlugs ? 'checked' }}&gt;
        &lt;label for=&quot;brand-{{ filter.id }}&quot;&gt;
            {{ filter.title }}
            &lt;span&gt;({{ brandsFilterCountLookup[filter.title]|length }})&lt;/span&gt;
        &lt;/label&gt;
    &lt;/div&gt;
{% endfor %}
</code></pre>
<p>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.</p>
<p>If it doesn't work, I'd probably not worry <em>too</em> much about the per-category queries since they are &quot;only&quot; doing a COUNT(*), which is usually very fast compared to regular SELECT statements.</p>

CC BY-SA 4.0
2023-01-19T22:03:17.457Z
Thanks James! Works like a charm! 
Jesse
CC BY-SA 4.0
2023-01-18T17:26:08.383Z
2023-01-18T17:26:08.383Z
James Smith
<p>I'm building a <strong>facet filter</strong> using Craft Commerce products and Craft categories. I have a &quot;Brand&quot; category and each product entry has a brand defined. Same for the other facet filters that are built on categories.</p>
<p>I have a functional facet filter, but I would like to display the actual product counts, like the gray counters in this example:</p>
<p><a href="https://i.sstatic.net/Fz6pa.png" rel="nofollow noreferrer"><img src="https://i.sstatic.net/Fz6pa.png" alt="Example of facet filter with counts" /></a></p>
<p>I currently have the following (simplified) code:</p>
<pre><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 %}
    &lt;div class=&quot;flex items-center&quot;&gt;
        &lt;input type=&quot;checkbox&quot; id=&quot;brand-{{ filter.id }}&quot; name=&quot;brands[]&quot; value=&quot;{{ filter.slug }}&quot; {{ filter.slug in brandSlugs ? 'checked' }}&gt;
        &lt;label for=&quot;brand-{{ filter.id }}&quot;&gt;
            {{ filter.title }}
            &lt;span&gt;({{ baseQuery.relatedTo(filter.id).count() }})&lt;/span&gt;
        &lt;/label&gt;
    &lt;/div&gt;
{% endfor %}
</code></pre>
<p><strong>However, this is executing a query per checkbox item because of the <code>count()</code> inside a foreach loop.</strong> It can easily trigger 30+ queries per page load. Is there a more performant solution possible?</p>
<p>Is there a way to execute a single query per category group using <code>GROUP BY</code> and <code>COUNT(*)</code>? 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.</p>

<plugin-craftcommerce><categories><sql>
Batch retrieve the entry count per category item in facet filter
# Batch retrieve the entry count per category item in facet filter

(Asked by: username_0 on 2023-01-15)

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 %}
    
        
        
            {{ filter.title }}
            ({{ baseQuery.relatedTo(filter.id).count() }})
        
    
{% 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.

**Tags:** plugin-craftcommerce, categories, sql


---

## 1 answers

### Answer by username_1 on 2023-01-18. Score: 3 | Accepted answer

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

> **Comments:**
> **username_0**: Thanks James! Works like a charm!   

---

