Фильтрация характеристик товаров с подсчетом?

Question

[Barancheek]

Слева запрос считает все имеющиеся фасеты (опции) товаров. Справа считает доступные фасеты согласно условиям фильтрации. Как переписать запрос справа, чтобы не вошедшие опции остались в выборке с нулями ?



Запрос и подсчет всех фасетов

--get facets 
select	
	pp.attr_alias,
	pp.option_value,
	pp.option_alias,
    count(pp.option_value)
from "Products" p
JOIN "Product_properties" pp ON pp.prod_id = p.id
WHERE p.cat_id = 1
GROUP BY pp.attr_alias, pp.option_value, pp.option_alias

Запрос и подсчет отфильтрованных фасетов

--get filtered facets 
select
	pp.attr_alias,
	pp.option_value,
	pp.option_alias,
    count(pp.option_value)      
from "Products" p
JOIN "Product_properties" pp ON  pp.prod_id = p.id
WHERE 
p.cat_id = 1
AND
p.id = ANY (
	SELECT prod_id
	FROM "Product_properties" pp
	WHERE pp.attr_alias = 'ram' AND pp.option_alias IN ('2gb','3gb','4gb','8gb')
)
AND
p.id = ANY (
    SELECT prod_id
    FROM "Product_properties" pp
    WHERE pp.attr_alias = 'storage' AND pp.option_alias IN ('16gb','128gb')
)    
GROUP BY pp.attr_alias, pp.option_value, pp.option_alias

Answer 1

[alexalexes]

Из where убрать дополнительное условие фильтрации, и добавить его же в case в count.
Тогда count будет отбивать именно статистику по условиям фильтрации (считается все, что выходит из case не null), но занулять там, где условия не выполняются.

...
count(
case
when p.id = ANY (
	SELECT prod_id
	FROM "Product_properties" pp
	WHERE pp.attr_alias = 'ram' AND pp.option_alias IN ('2gb','3gb','4gb','8gb')
)
AND
p.id = ANY (
    SELECT prod_id
    FROM "Product_properties" pp
    WHERE pp.attr_alias = 'storage' AND pp.option_alias IN ('16gb','128gb')
)    
then 1
end
)
...

PS: Подзапросы Any можно вынести в секцию With, если они дают однотипную выборку и используются во многих местах.

Comments

[Barancheek]

Спасибо. Получилось.
Но блин как же кринжово это выглядит хотя там лишь всего два условия фильтрации из возможных )

[alexalexes]

Реляционные базы - это история про эффективное хранение данных, а не поиск.
Для поиска есть другие эффективные инструменты.
Если задача сводится к разработке одной единственной формы, то можно обойтись и реляционкой, но будет больно сопровождать.

[Barancheek]

да Там может быть множество ANY подзапросов с условиями
и моих знаний sql не хватает, чтобы сделать это эффективнее и красивее )

[Barancheek]

alexalexes, Это учебный пет-проект,
чтобы на гитхаб и в резюме было чем "хвастаться",
вместо банальных CRUD todu list приложений, которыми уже никого не удивить )

[alexalexes]

Barancheek, чисто для справки тогда почитайте что-нибудь про ElasticSearch. Чтобы знать, в каких местах какую солому подкладывать. Не обязательно реализовывать в пет.