CREATE OR REPLACE FUNCTION public.pd_get_product_prices(p_limit integer, p_offset integer, p_sort character varying DEFAULT 'rating'::character varying, p_sort_direction character varying DEFAULT 'asc'::character varying, p_title character varying DEFAULT NULL::character varying, p_status type_productstatus DEFAULT NULL::type_productstatus, p_in_stock boolean DEFAULT NULL::boolean, p_downloadable boolean DEFAULT NULL::boolean, p_virtual boolean DEFAULT NULL::boolean, p_sku character varying DEFAULT NULL::character varying, p_sale_price_from type_money DEFAULT NULL::numeric, p_sale_price_till type_money DEFAULT NULL::numeric, p_rating_from integer DEFAULT NULL::integer, p_rating_till integer DEFAULT NULL::integer, p_category_list integer[] DEFAULT NULL::integer[])
RETURNS TABLE(id bigint, title character varying, status type_productstatus, slug character varying, sku character varying, user_id integer, regular_price type_money, sale_price type_money, in_stock boolean, short_description character varying, downloadable boolean, virtual boolean, rating_count integer, rating_summary integer, rating integer, published_at timestamp without time zone, created_at timestamp without time zone, main_image character varying, product_categories character varying[], product_categories_slug character varying[], product_tags character varying[], product_tags_id smallint[], bookmarks_count bigint, product_attributes jsonb)
LANGUAGE sql
AS $function$
select * from ( SELECT p.id, p.title, p.status, p.slug, p.sku, p.user_id, p.regular_price, p.sale_price, p.in_stock, p.short_description, p.downloadable, p.virtual, p.rating_count, p.rating_summary,
CAST( floor( p.rating_summary / NULLIF(p.rating_count,0) ) AS INTEGER ) as rating,
p.published_at, p.created_at,
(select pi.image from pd_product_image as pi where pi.product_id = p.id and pi.is_main = true ) as main_image,
( select array_agg(c.name) from pd_category as c, pd_product_category as pd where c.id= pd.category_id and pd.product_id = p.id) as product_categories,
( select array_agg(c.slug) from pd_category as c, pd_product_category as pd where c.id= pd.category_id and pd.product_id = p.id) as product_categories_slug,
( select array_agg(t.name) from pd_tag as t, pd_product_tag as pt where t.id= pt.tag_id and pt.product_id = p.id) as product_tags,
( select array_agg(t.id) from pd_tag as t, pd_product_tag as pt where t.id= pt.tag_id and pt.product_id = p.id) as product_tags_id,
( select count(*) from pd_product_bookmark where pd_product_bookmark.product_id = p.id and pd_product_bookmark.user_id = p.user_id ) as bookmarks_count,
( select pa.attributes_data from pd_product_attribute as pa where pa.product_id = p.id) as product_attributes
FROM pd_product AS p LEFT JOIN pd_product_category AS pc ON pc.product_id = p.id
WHERE
( p.status= p_status OR p_status IS NULL ) AND
( p.in_stock = p_in_stock OR p_in_stock IS NULL ) AND
( p.downloadable = p_downloadable OR p_downloadable IS NULL ) AND
( p.virtual = p_virtual OR p_virtual IS NULL ) AND
( LOWER(p.sku) like LOWER(p_sku) OR p_sku IS NULL ) AND
( LOWER(p.title) like LOWER(p_title) OR p_title IS NULL ) AND
( ( p.sale_price >= p_sale_price_from AND p.sale_price <= p_sale_price_till ) OR ( p_sale_price_from IS NULL OR p_sale_price_till IS null ) ) AND
( pc.category_id = ANY (p_category_list) OR p_category_list IS NULL )
GROUP BY p.id
) as rows
WHERE( ( rows.rating >= p_rating_from AND rows.rating <= p_rating_till ) OR ( p_rating_from IS NULL OR p_rating_till IS null ) )
ORDER BY
CASE WHEN p_sort_direction = 'asc' THEN
CASE p_sort
-- sort by numeric fields
WHEN 'rating' THEN rating
WHEN 'sale_price' THEN sale_price
ELSE NULL
END
ELSE
NULL
END
ASC,
CASE WHEN p_sort_direction = 'desc' THEN
CASE p_sort -- sort by numeric fields
WHEN 'rating' THEN rating
WHEN 'sale_price' THEN sale_price
ELSE NULL
END
ELSE
NULL
END
DESC,
CASE WHEN p_sort_direction = 'asc' THEN
CASE p_sort -- sort by string fields
WHEN 'title' THEN title
WHEN 'status' THEN CAST(status AS character varying )
WHEN 'downloadable' THEN CAST(downloadable AS character varying )
WHEN 'virtual' THEN CAST(virtual AS character varying )
WHEN 'in_stock' THEN CAST(in_stock AS character varying )
ELSE NULL
END
ELSE
NULL
END
ASC,
CASE WHEN p_sort_direction = 'desc' THEN
CASE p_sort -- sort by string fields
WHEN 'title' THEN title
WHEN 'status' THEN CAST(status AS character varying )
WHEN 'downloadable' THEN CAST(downloadable AS character varying )
WHEN 'virtual' THEN CAST(virtual AS character varying )
WHEN 'in_stock' THEN CAST(in_stock AS character varying )
ELSE NULL
END
ELSE
NULL
END
DESC,
CASE WHEN p_sort_direction = 'asc' THEN
CASE p_sort
-- sort by timestamp fields
WHEN 'published_at' THEN published_at
WHEN 'created_at' THEN created_at
ELSE NULL
END
ELSE
NULL
END
ASC,
CASE WHEN p_sort_direction = 'desc' THEN
CASE p_sort -- sort by timestamp fields
WHEN 'published_at' THEN published_at
WHEN 'created_at' THEN created_at
ELSE NULL
END
ELSE
NULL
END
DESC
LIMIT p_limit OFFSET p_offset;
$function$