[Solved] How to count products to multiple filters

by


For example if this is frontend and their are checkboxes and their are UNchecked atm

Size (group_id)
 10m (option_id: 52) (21 products)
 20m (option_id: 51) (1 product)

Color (group_id)
  Green (option_id: 49) (22 products)
  Black (option_id: 38) (1 product)

SELECT COUNT(DISTINCT CASE WHEN option_id = 52 THEN product_id END) p52,
       COUNT(DISTINCT CASE WHEN option_id = 51 THEN product_id END) p51,
       COUNT(DISTINCT CASE WHEN option_id = 49 THEN product_id END) p49,
       COUNT(DISTINCT CASE WHEN option_id = 38 THEN product_id END) p38
FROM filter_counter;

If a user select 10m (option_id: 52) the filter counter should become like this

Size (group_id)
 10m (option_id: 52) (21 products)
 20m (option_id: 51) (1)

Color (group_id)
  Green (option_id: 49) (2)
  Black (option_id: 38) (0)

SELECT COUNT(DISTINCT CASE WHEN option_id = 52 THEN product_id END) p52,
       COUNT(DISTINCT CASE WHEN option_id = 51 THEN product_id END) p51,
       COUNT(DISTINCT CASE WHEN option_id = 49 THEN product_id END) p49,
       COUNT(DISTINCT CASE WHEN option_id = 38 THEN product_id END) p38
FROM filter_counter
JOIN ( SELECT DISTINCT product_id
       FROM filter_counter
       WHERE option_id IN (52) ) filter1 USING (product_id);

fiddle

If user checks additional filter in the same group then the next value is added into the condition. For example, if he checks option_id = 51 which is in the same group with option_id = 52 then filtering subquery become

JOIN ( SELECT DISTINCT product_id
       FROM filter_counter
       WHERE option_id IN (52, 51) ) filter1 USING (product_id)

If user checks additional filter in another group then additional filterX subquery added. For example, if he checks option_id = 49 then the next subquery added to the query:

JOIN ( SELECT DISTINCT product_id
       FROM filter_counter
       WHERE option_id IN (49) ) filter2 USING (product_id)

is there any way output to become one per line ?

SELECT options.option_id,
       COUNT(DISTINCT CASE WHEN filter_counter.option_id = options.option_id 
                           THEN product_id 
                           END) option_count
FROM filter_counter
CROSS JOIN ( SELECT DISTINCT option_id
             FROM filter_counter ) options
JOIN ( SELECT DISTINCT product_id
       FROM filter_counter
       WHERE option_id IN (52) ) filter1 USING (product_id)
GROUP BY options.option_id;

fiddle

14

solved How to count products to multiple filters