55 lines
2.3 KiB
SQL
55 lines
2.3 KiB
SQL
-- P5-6253: use market_id in materialized view v_esales_category_count
|
|
|
|
drop materialized view if exists v_esales_category_count;
|
|
|
|
create materialized view v_esales_category_count as
|
|
SELECT
|
|
main.id,
|
|
main.market_id,
|
|
main.product_id,
|
|
main.groupid,
|
|
main.wallpapertype_id,
|
|
CASE
|
|
WHEN main.blacklist_id IS NULL AND main.visible = 1 AND main.browsable = 1 THEN true
|
|
ELSE false
|
|
END AS is_visible
|
|
FROM ( SELECT p.id,
|
|
markets.id AS market_id,
|
|
pc.product_id,
|
|
pp.groupid,
|
|
CASE pp.groupid
|
|
WHEN 2 THEN NULL::integer
|
|
ELSE pw.wallpapertype_id
|
|
END AS wallpapertype_id,
|
|
( SELECT product_blacklist.id
|
|
FROM product_blacklist
|
|
WHERE
|
|
product_blacklist.product_id = pc.product_id
|
|
AND product_blacklist.market_id = markets.id
|
|
AND (product_blacklist.group_id = pp.groupid OR product_blacklist.group_id IS NULL)
|
|
) AS blacklist_id,
|
|
prod.visible,
|
|
prod.browsable
|
|
FROM categories p
|
|
JOIN categories c ON c.lft >= p.lft AND c.rgt <= p.rgt
|
|
JOIN product_category pc ON c.id = pc.category_id
|
|
JOIN ( SELECT "product-printproducts".productid,
|
|
"product-printproducts".groupid
|
|
FROM "product-printproducts") pp ON pc.product_id = pp.productid
|
|
JOIN ( SELECT "product-products".productid,
|
|
"product-products".visible,
|
|
"product-products".browsable
|
|
FROM "product-products") prod ON pc.product_id = prod.productid
|
|
LEFT JOIN product_wallpapertypes pw ON pc.product_id = pw.product_id,
|
|
markets) main
|
|
GROUP BY main.id, main.market_id, main.product_id, main.groupid, main.wallpapertype_id, main.visible, main.browsable, main.blacklist_id;
|
|
|
|
|
|
create unique index v_esales_category_count_id_idx
|
|
on v_esales_category_count
|
|
using btree
|
|
(id, market_id, product_id, groupid, wallpapertype_id);
|
|
|
|
|
|
alter materialized view v_esales_category_count OWNER TO photowall;
|