Get Next Collection
unknown
sql
a year ago
5.0 kB
6
Indexable
CREATE OR REPLACE FUNCTION public.get_next_collection(offset_param integer, user_id_param uuid) RETURNS TABLE(collection_id bigint, score numeric, type text) LANGUAGE plpgsql AS $function$ BEGIN RETURN QUERY with view_virtual_items as ( select i_1.product_id, i_1.ext_product_id, COALESCE(ir.name, i_1.name) AS name, COALESCE((ir.price)::bigint, (i_1.price)::bigint) AS price, ir.original_price, CASE WHEN (ir.v2_main_image_id IS NULL) THEN NULL::json ELSE ir.v2_main_image END AS v2_main_image, COALESCE(ir.product_url, i_1.product_url) AS product_url, COALESCE(ir.brand, i_1.brand) AS brand, COALESCE(ir.is_available, true) AS is_available, COALESCE(ir.category, i_1.category) AS product_category, COALESCE(ir.currency_code, ((i_1.currency_code)::text)::character varying) AS currency_code, i_1.ltk_url, ir.created_at, ir.updated_at, ir.deleted_at, ir.description, ir.sizes, ir.img_ids FROM (products i_1 LEFT JOIN aggregated_products ir ON ((i_1.product_id = ir.product_id))) ), agg_sizes as ( select products.product_id, count(*) filter ( where products_retrieval_sizes_availability.available ) as c_available_sizes, count(*) filter ( where not products_retrieval_sizes_availability.available ) as c_not_available_sizes, count(*) as c_sizes from products left join products_retrieval_sizes_availability on products.product_id = products_retrieval_sizes_availability.product_id group by products.product_id ), collection_views as ( select sciv.collection_id from stats.collection_product_views as sciv where date(sciv.created_at) < date(now()) and sciv.user_id = user_id_param group by sciv.collection_id ), collection_style_views as ( select sciv.collection_id from stats.collection_style_views as sciv where date(sciv.created_at) < date(now()) and sciv.user_id = user_id_param group by sciv.collection_id ), col_with_sizes as ( select collections.id as collection_id, collections.type, count(*) filter ( where coalesce( ( 100 * agg_sizes.c_available_sizes / agg_sizes.c_sizes )::numeric > 45::numeric, true ) and coalesce(agg_p.is_available, true) ) as c_available_collection_items, count (*) filter ( where agg_p.V2_main_image::json->>'photograph_type_id'::text = '7' ) as c_available_style_items from collections left join collection_products on collections.id = collection_products.collection_id left join aggregated_products as agg_p on agg_p.product_id = collection_products.product_id left join agg_sizes on agg_sizes.product_id = agg_p.product_id where collections.type = 'plain' group by collections.id, collections.type ), col_style_with_sizes as ( select collections.id as collection_id, collections.type, count(*) filter ( where coalesce( ( 100 * agg_sizes.c_available_sizes / agg_sizes.c_sizes )::numeric > 45::numeric, true ) and coalesce(agg_p.is_available, true) ) as c_available_collection_items, count (*) filter ( where agg_p.V2_main_image::json->>'photograph_type_id'::text = '7' or agg_p.V2_main_image::json->>'photograph_type_id' is null ) as c_available_style_items from collections left join style_collection_products as iis on collections.id = iis.collection_id left join aggregated_products as agg_p on agg_p.product_id = iis.product_id left join agg_sizes on agg_sizes.product_id = iis.product_id where collections.type = 'style' group by collections.id, collections.type ), available_col_style_main_item as ( select DISTINCT(iis.collection_id) from style_collection_products iis left join view_virtual_items items on items.product_id = iis.main_product_id where items.is_available = true and (items.v2_main_image::json->>'photograph_type_id'::text = '7' or items.v2_main_image::json->>'photograph_type_id' is null) ) select cs.collection_id, cs.score, c.type from model.collection_score as cs LEFT JOIN collections as c on c.id = cs.collection_id LEFT JOIN col_with_sizes on col_with_sizes.collection_id = cs.collection_id LEFT JOIN col_style_with_sizes as csws on csws.collection_id = cs.collection_id left join collection_views as cv on cv.collection_id = cs.collection_id left join collection_style_views as c_sv on c_sv.collection_id = cs.collection_id left join available_col_style_main_item as av_m_i on av_m_i.collection_id = cs.collection_id where (col_with_sizes.c_available_collection_items >= 2 and col_with_sizes.c_available_style_items > 0 and cv.collection_id is null) or (csws.c_available_collection_items >= 0 and csws.c_available_style_items > 0 and c_sv.collection_id is null and av_m_i.collection_id is not null) order by cs.score desc offset offset_param limit 1; END; $function$ ;
Editor is loading...
Leave a Comment