Есть каталог вида (id, path, name) в каталоге товары (id,categoryId,name)
categories
====================
|id| path |name
|1 | {1} | Root1
|2 | {2} | Root2
|3 | {1,3} | Cat1_3
|4 | {1,4} | Cat1_4
|5 | {1,3,5} | Cat1_3_5
|6 | {1,4,6} | Cat1_4_6
|7 | {1,3,5,7} | Cat1_3_5_7
products
====================
id| categoryId |name
1 | 3 | p3_1
2 | 3 | p3_2
3 | 6 | p6_1
4 | 6 | p6_2
5 | 7 | p7_1
6 | 7 | p7_2
7 | 7 | p7_3
8 | 2 | p2_1
Задача - выводить подкатегории для заданной с количеством товаров включая все подкатегории - ожидаемый результат
[Root] -
id| path |name | product_count
1 | {1} | Root1, | 7
2 | {2} | Root2, | 1
[1]
id| path |name | product_count
3 | {1,3} | Cat1_3 | 5
4 | {1,4} | Cat1_4 | 2
[1,3]
id| path |name | product_count
5 | {1,3,5} | Cat1_3_5 | 3
[1,4]
id| path |name | product_count
6 | {1,4,6} | Cat1_4_6 | 2
В принципе рабочее решение есть,
WITH product_counter AS
(SELECT
"loc_categories"."path",
coalesce(COUNT("loc_products"."id"), 0) as cnt
FROM "loc_products"
LEFT JOIN loc_categories ON "loc_products"."categoryId" = "loc_categories"."id"
WHERE "loc_products"."amount" > 0
GROUP BY "loc_categories"."path"
)
SELECT
"loc_categories"."id",
"loc_categories"."name",
SUM("pcnt"."cnt") as product_count
FROM "loc_categories"
LEFT JOIN
product_counter AS pcnt ON (ARRAY [loc_categories."id"] && pcnt.path)
WHERE ("loc_categories"."path" @> :inputPath AND array_length(loc_categories.path,1) =:inputLevel)
GROUP BY loc_categories."id"
HAVING SUM("pcnt"."cnt") > 0
А вопрос в том, можно ли как-то красивее решить задачу?
