31 lines
834 B
SQL
31 lines
834 B
SQL
-- Fix v_funnel_collections to use editorial_collection_products after metadata migration
|
|
|
|
DROP VIEW IF EXISTS public.v_funnel_collections;
|
|
|
|
CREATE VIEW public.v_funnel_collections AS
|
|
SELECT
|
|
ep.id AS collection_id,
|
|
ep.name AS collection_name,
|
|
cp.product_id,
|
|
ep.inserted,
|
|
ep.published,
|
|
ep.updated,
|
|
ep.description
|
|
FROM editorial_pages ep
|
|
JOIN (
|
|
-- Keep output equivalent to the former metadata array (one row per collection/product)
|
|
SELECT DISTINCT
|
|
editorial_page_id,
|
|
product_id
|
|
FROM editorial_collection_products
|
|
) cp ON cp.editorial_page_id = ep.id
|
|
WHERE ep.type = 'collection'
|
|
AND EXISTS (
|
|
SELECT 1
|
|
FROM editorial_content ec
|
|
WHERE ec.editorial_page_id = ep.id
|
|
AND ec.visible = TRUE
|
|
);
|
|
|
|
GRANT SELECT ON TABLE public.v_funnel_collections TO funnel;
|