Calculating product counts (efficiently) in faceted search with PHP, MySQL

Question

Say I have a page that displays all available laptops in a webshop. These results can be refined by activating certain filters, such as brand, price, weight, amount of memory, disk space, like so:

Brand
[ ] Dell (16)
[ ] HP (12)
[ ] Lenovo (9)

Memory
( ) ≥ 2 GB (37)
( ) ≥ 4 GB (24)
( ) ≥ 8 GB (8)

Price
( ) ≤ 200 $ (3)
( ) ≤ 300 $ (12)
( ) ≤ 500 $ (22)

Currently, I'm calculating these product counts with a single query, the results of which I loop through in PHP to divide them into these categories (i.e. "Memory, ≥ 4 GB"). The problem with this approach is that when, for example, "≥ 4 GB" is selected and the results are shown, the product counts become illogical.

MySQL (simplified):

SELECT brand, memory, price FROM Laptops WHERE memory >= 4000

PHP (simplified):

while($row = msqli_fetch_assoc($results)) {
    if($row['memory'] >= 2000) {
        $product_count['memory']['2gb']++;
    }
}

Results:

Memory
( ) ≥ 2 GB (24) <-- should be 37
(o) ≥ 4 GB (24)
( ) ≥ 8 GB (8)

Because now I'm using a query with a condition like WHERE memory >= 4000, the product counts are limited to the current 24 results (which match the activated filter), and so "≥ 2 GB" now has a count of 24 as well. It's true that of the 24 results, 24 will have 2 GB or more memory, but that's not relevant to the user, who needs to know how many results would be shown if 2 GB were selected.

I could query the database for each filter separately, leaving out the WHERE condition of that filter so that its product counts are unaffected by its own setting, but still affected by any other filters (like Brand). However, I plan on having at least a dozen filters, so that would result in a dozen more queries, putting a relatively large amount of extra strain on an otherwise fairly simple application.

Is there perhaps a more efficient way to calculate product counts, with fewer queries required?

Answer 1

The fault in my thinking was that I would need an extra query for every possible filter to accurately calculate the product counts. However, the counts are only inaccurate for (radio) options in a currently selected filter (like Memory above), so if I simply recalculate product counts only for the active filters (by checking the current $_GET parameters), that drastically reduces the number of required queries.

So I'll have one query with all filters included, like:

$query = 'SELECT id, brand, memory, price, stock FROM Laptops 
          WHERE stock > 0 AND memory >= 4000 AND price <= 200';

I'll loop over that with PHP to divide the results into the filter categories and calculate their counts, and then for each active filter I'll reset those counts and recalculate them based on a query that has had that filter removed by a regex like preg_replace('(AND memory >= \d+)','',$query);.

$query = 'SELECT id, brand, memory, price, stock FROM Laptops 
          WHERE stock > 0 AND price <= 200';

Based on that query, the counts for Memory will now accurately reflect the results for each option, while still taking into account any of the other filters that are set (i.e. outside the scope of the memory filter):

Memory
( ) ≥ 2 GB (37)
(o) ≥ 4 GB (24)
( ) ≥ 8 GB (8)

Now users know that 4 GB limits the list to 24 items, and that switching to 2 GB will give more results.

If no filters are active, I won't need any extra queries to calculate the product counts. If the user does apply one or more filters, I'll only need one extra query for each of those, and I'm happy with that.