33 lines
1.6 KiB
SQL
33 lines
1.6 KiB
SQL
DROP MATERIALIZED VIEW IF EXISTS mv_category_locale_safexmlpath_v2;
|
|
CREATE MATERIALIZED VIEW mv_category_locale_safexmlpath_v2 AS
|
|
SELECT
|
|
c.id AS category_id,
|
|
c.locale_id,
|
|
getsafexmlpath(
|
|
( SELECT lower(array_to_string(array_agg(main_1.parent_name), '/'::text))
|
|
FROM ( SELECT node.id,
|
|
( SELECT category_texts_v2.name
|
|
FROM category_texts_v2
|
|
WHERE ((category_texts_v2.locale_id = c.locale_id) AND (category_texts_v2.category_id = parent.id))) AS parent_name
|
|
FROM categories_v2_lft_rgt node,
|
|
categories_v2_lft_rgt parent
|
|
WHERE ((node.lft >= parent.lft) AND (node.lft <= parent.rgt) AND (node.id = c.id))
|
|
ORDER BY parent.lft) main_1
|
|
GROUP BY main_1.id),
|
|
2
|
|
) AS safe_xml_path
|
|
FROM (
|
|
SELECT c_1.id,
|
|
s.id AS locale_id
|
|
FROM locales s
|
|
JOIN category_texts_v2 s_t ON s.id = s_t.locale_id
|
|
JOIN categories_v2_lft_rgt c_1 ON s_t.category_id = c_1.id
|
|
) c;
|
|
|
|
CREATE UNIQUE INDEX ON mv_category_locale_safexmlpath_v2 (category_id, locale_id);
|
|
CREATE INDEX ON mv_category_locale_safexmlpath_v2 (locale_id, safe_xml_path);
|
|
|
|
COMMENT ON MATERIALIZED VIEW mv_category_locale_safexmlpath_v2 IS 'Materialized view for fast lookup of category id from localized safe xml path and locale. Path construction logic mirrors v_esales_categorytree_i18n_unmaterialized but additionally applies the getsafexmlpath(..., 2) function.';
|
|
|
|
ALTER MATERIALIZED VIEW mv_category_locale_safexmlpath_v2 OWNER TO Photowall;
|