1
2.80 ms
SELECT t0 . product AS product_1 , t0 . url AS url_2 , t0 . article AS article_3 , t0 . barcode AS barcode_4 , t0 . sort AS sort_5 , t0 . profile AS profile_6 , t0 . event AS event_7 FROM product_info t0 WHERE t0 . url = ? LIMIT 1
Parameters :
[
"triangle_effexsport_th202 "
]
SELECT t0 . product AS product_1 , t0 . url AS url_2 , t0 . article AS article_3 , t0 . barcode AS barcode_4 , t0 . sort AS sort_5 , t0 . profile AS profile_6 , t0 . event AS event_7 FROM product_info t0 WHERE t0 . url = 'triangle_effexsport_th202' LIMIT 1 ; Copy
2
89.58 ms
SELECT product . id , product . event , product_active . active , product_active . active_from , product_active . active_to , product_seo . title AS seo_title , product_seo . keywords AS seo_keywords , product_seo . description AS seo_description , product_trans . name AS product_name , product_desc . preview AS product_preview , product_desc . description AS product_description , product_info . url , product_offer . id as product_offer_uid , product_offer . const as product_offer_const , product_offer . value as product_offer_value , product_offer . postfix as product_offer_postfix , category_offer . reference AS product_offer_reference , category_offer_trans . name as product_offer_name , category_offer_trans . postfix as product_offer_name_postfix , product_variation . id as product_variation_uid , product_variation . const as product_variation_const , product_variation . value as product_variation_value , product_variation . postfix as product_variation_postfix , category_variation . reference as product_variation_reference , category_variation_trans . name as product_variation_name , category_variation_trans . postfix as product_variation_name_postfix , product_modification . id as product_modification_uid , product_modification . const as product_modification_const , product_modification . value as product_modification_value , product_modification . postfix as product_modification_postfix , category_modification . reference as product_modification_reference , category_modification_trans . name as product_modification_name , category_modification_trans . postfix as product_modification_name_postfix ,
COALESCE (
product_modification . article ,
product_variation . article ,
product_offer . article ,
product_info . article
) AS product_article
,
CASE
WHEN product_modification_image . ext IS NOT NULL THEN
JSON_AGG
(DISTINCT
JSONB_BUILD_OBJECT
(
'product_img_root' , product_modification_image . root ,
'product_img' , CONCAT ( '/upload/product_modification_images' , '/' , product_modification_image . name ),
'product_img_ext' , product_modification_image . ext ,
'product_img_cdn' , product_modification_image . cdn
)
)
WHEN product_variation_image . ext IS NOT NULL THEN
JSON_AGG
(DISTINCT
JSONB_BUILD_OBJECT
(
'product_img_root' , product_variation_image . root ,
'product_img' , CONCAT ( '/upload/product_variation_images' , '/' , product_variation_image . name ),
'product_img_ext' , product_variation_image . ext ,
'product_img_cdn' , product_variation_image . cdn
)
)
WHEN product_offer_images . ext IS NOT NULL THEN
JSON_AGG
(DISTINCT
JSONB_BUILD_OBJECT
(
'product_img_root' , product_offer_images . root ,
'product_img' , CONCAT ( '/upload/product_offer_images' , '/' , product_offer_images . name ),
'product_img_ext' , product_offer_images . ext ,
'product_img_cdn' , product_offer_images . cdn
)
)
WHEN product_photo . ext IS NOT NULL THEN
JSON_AGG
(DISTINCT
JSONB_BUILD_OBJECT
(
'product_img_root' , product_photo . root ,
'product_img' , CONCAT ( '/upload/product_photo' , '/' , product_photo . name ),
'product_img_ext' , product_photo . ext ,
'product_img_cdn' , product_photo . cdn
)
)
ELSE NULL
END
AS product_images ,
COALESCE (
NULLIF (product_modification_price . price , 0 ),
NULLIF (product_variation_price . price , 0 ),
NULLIF (product_offer_price . price , 0 ),
NULLIF (product_price . price , 0 ),
0
) AS product_price
,
COALESCE (
NULLIF (product_modification_price . old , 0 ),
NULLIF (product_variation_price . old , 0 ),
NULLIF (product_offer_price . old , 0 ),
NULLIF (product_price . old , 0 ),
0
) AS product_old_price
,
CASE
WHEN product_modification_price . price IS NOT NULL AND product_modification_price . price > 0
THEN product_modification_price . currency
WHEN product_variation_price . price IS NOT NULL AND product_variation_price . price > 0
THEN product_variation_price . currency
WHEN product_offer_price . price IS NOT NULL AND product_offer_price . price > 0
THEN product_offer_price . currency
WHEN product_price . price IS NOT NULL AND product_price . price > 0
THEN product_price . currency
ELSE NULL
END AS product_currency
, JSON_AGG (
DISTINCT JSONB_BUILD_OBJECT (
'value' , product_region_delivery . value ,
'day' , product_region_delivery . day
)) FILTER (WHERE product_region_delivery . day IS NOT NULL )
AS product_region_delivery , JSON_AGG (
DISTINCT JSONB_BUILD_OBJECT (
'total' , stock . total ,
'reserve' , stock . reserve
)) FILTER (WHERE stock . total > stock . reserve )
AS product_quantity_stocks , JSON_AGG (
DISTINCT JSONB_BUILD_OBJECT (
'total' , COALESCE (
product_modification_quantity . quantity ,
product_variation_quantity . quantity ,
product_offer_quantity . quantity ,
product_price . quantity ,
0
),
'reserve' , COALESCE (
product_modification_quantity . reserve ,
product_variation_quantity . reserve ,
product_offer_quantity . reserve ,
product_price . reserve ,
0
)
) )
AS product_quantity , category . id AS category_id , category_trans . name AS category_name , category_info . url AS category_url , category_info . minimal AS category_minimal , category_info . input AS category_input , category_info . threshold AS category_threshold , category_info . step AS category_step ,
CASE
WHEN category_cover . name IS NOT NULL THEN
CONCAT ( '/upload/product_category_cover' , '/' , category_cover . name )
ELSE NULL
END AS category_cover_path
, category_cover . ext AS category_cover_ext , category_cover . cdn AS category_cover_cdn , JSON_AGG
( DISTINCT
JSONB_BUILD_OBJECT
(
'0' , category_section_field . sort ,
'field_uid' , category_section_field . id ,
'field_const' , category_section_field . const ,
'field_name' , category_section_field . name ,
'field_alternative' , category_section_field . alternative ,
'field_public' , category_section_field . public ,
'field_card' , category_section_field . card ,
'field_type' , category_section_field . type ,
'field_trans' , category_section_field_trans . name ,
'field_value' , product_property . value
)
)
AS category_section_field , product_invariable . id AS product_invariable_id , project_profile_discount . value AS project_discount FROM product INNER JOIN product_active product_active ON product_active . event = product . event LEFT JOIN product_seo product_seo ON product_seo . event = product . event AND product_seo . local = ? LEFT JOIN product_trans product_trans ON product_trans . event = product . event AND product_trans . local = ? LEFT JOIN product_description product_desc ON product_desc . event = product . event AND product_desc . device = ? LEFT JOIN product_info product_info ON product_info . product = product . id INNER JOIN product_offer product_offer ON product_offer . event = product . event AND LOWER (product_offer . value ) = ? AND ( LOWER (product_offer . postfix ) = ? OR product_offer . postfix IS NULL ) LEFT JOIN product_price product_price ON product_price . event = product . event LEFT JOIN product_photo product_photo ON product_photo . event = product . event LEFT JOIN user_profile_region product_profile_region ON product_profile_region . value = ? INNER JOIN product_categories_product product_event_category ON product_event_category . event = product . event AND product_event_category . root = true INNER JOIN users_profile project_profile ON
project_profile . id = ? LEFT JOIN product_category_offers category_offer ON category_offer . id = product_offer . category_offer INNER JOIN product_variation product_variation ON product_variation . offer = product_offer . id AND LOWER (product_variation . value ) = ? AND ( LOWER (product_variation . postfix ) = ? OR product_variation . postfix IS NULL ) LEFT JOIN product_offer_price product_offer_price ON product_offer_price . offer = product_offer . id LEFT JOIN product_offer_images product_offer_images ON product_offer_images . offer = product_offer . id LEFT JOIN product_variation_images product_variation_image ON product_variation_image . variation = product_variation . id LEFT JOIN product_offer_quantity product_offer_quantity ON product_offer_quantity . offer = product_offer . id LEFT JOIN product_category_offers_trans category_offer_trans ON category_offer_trans . offer = category_offer . id AND category_offer_trans . local = ? LEFT JOIN product_category_variation category_variation ON category_variation . id = product_variation . category_variation INNER JOIN product_modification product_modification ON product_modification . variation = product_variation . id AND LOWER (product_modification . value ) = ? AND ( LOWER (product_modification . postfix ) = ? OR product_modification . postfix IS NULL ) LEFT JOIN product_variation_price product_variation_price ON product_variation_price . variation = product_variation . id LEFT JOIN product_category_variation_trans category_variation_trans ON category_variation_trans . variation = category_variation . id AND category_variation_trans . local = ? LEFT JOIN product_variation_quantity product_variation_quantity ON product_variation_quantity . variation = product_variation . id LEFT JOIN product_category_modification category_modification ON category_modification . id = product_modification . category_modification LEFT JOIN product_modification_price product_modification_price ON product_modification_price . modification = product_modification . id LEFT JOIN product_modification_images product_modification_image ON product_modification_image . modification = product_modification . id LEFT JOIN product_invariable product_invariable ON
product_invariable . product = product . id AND
(
(product_offer . const IS NOT NULL AND product_invariable . offer = product_offer . const ) OR
(product_offer . const IS NULL AND product_invariable . offer IS NULL )
)
AND
(
(product_variation . const IS NOT NULL AND product_invariable . variation = product_variation . const ) OR
(product_variation . const IS NULL AND product_invariable . variation IS NULL )
)
AND
(
(product_modification . const IS NOT NULL AND product_invariable . modification = product_modification . const ) OR
(product_modification . const IS NULL AND product_invariable . modification IS NULL )
)
LEFT JOIN product_category_modification_trans category_modification_trans ON category_modification_trans . modification = category_modification . id AND category_modification_trans . local = ? LEFT JOIN product_modification_quantity product_modification_quantity ON product_modification_quantity . modification = product_modification . id INNER JOIN users_profile product_region_total ON product_region_total . event = product_profile_region . event LEFT JOIN user_profile_delivery product_region_delivery ON product_region_delivery . event = product_profile_region . event LEFT JOIN product_stock_total stock ON
stock . profile = product_region_total . id AND
stock . product = product . id
AND
CASE
WHEN product_offer . const IS NOT NULL
THEN stock . offer = product_offer . const
ELSE stock . offer IS NULL
END
AND
CASE
WHEN product_variation . const IS NOT NULL
THEN stock . variation = product_variation . const
ELSE stock . variation IS NULL
END
AND
CASE
WHEN product_modification . const IS NOT NULL
THEN stock . modification = product_modification . const
ELSE stock . modification IS NULL
END
INNER JOIN product_category category ON category . id = product_event_category . category LEFT JOIN product_category_trans category_trans ON category_trans . event = category . event AND category_trans . local = ? LEFT JOIN product_category_info category_info ON category_info . event = category . event LEFT JOIN product_category_section category_section ON category_section . event = category . event LEFT JOIN product_category_cover category_cover ON category_cover . event = category . event LEFT JOIN product_category_section_field category_section_field ON category_section_field . section = category_section . id LEFT JOIN product_category_section_field_trans category_section_field_trans ON category_section_field_trans . field = category_section_field . id
AND category_section_field_trans . local = ? LEFT JOIN product_property product_property ON product_property . event = product . event AND product_property . field = category_section_field . const LEFT JOIN user_profile_discount project_profile_discount ON
project_profile_discount . event = project_profile . event WHERE product . id = ? GROUP BY product_photo . ext , product_offer_images . ext , product_variation_image . ext , product_modification_image . ext , product . id , product . event , product_active . active , product_active . active_from , product_active . active_to , product_seo . title , product_seo . keywords , product_seo . description , product_trans . name , product_desc . preview , product_desc . description , product_info . url , product_offer . id , product_offer . const , product_offer . value , product_offer . postfix , category_offer . reference , category_offer_trans . name , category_offer_trans . postfix , product_variation . id , product_variation . const , product_variation . value , product_variation . postfix , category_variation . reference , category_variation_trans . name , category_variation_trans . postfix , product_modification . id , product_modification . const , product_modification . value , category_modification . reference , category_modification_trans . name , category_modification_trans . postfix , product_modification . article , product_variation . article , product_offer . article , product_info . article , product_modification_price . price , product_variation_price . price , product_offer_price . price , product_price . price , product_modification_price . old , product_variation_price . old , product_offer_price . old , product_price . old , product_modification_price . currency , product_variation_price . currency , product_offer_price . currency , product_price . currency , category . id , category_trans . name , category_info . url , category_info . minimal , category_info . input , category_info . threshold , category_info . step , category_cover . name , category_cover . ext , category_cover . cdn , product_invariable . id , project_profile_discount . value
Parameters :
[
"ru "
"ru "
"pc "
"19 "
"111w "
"201042a6-c35d-7bc4-9cb9-ef8bc1c8711e "
"019469c3-700f-76a9-9b34-ccde7b4e6f49 "
"255 "
"111w "
"ru "
"55 "
"111w "
"ru "
"ru "
"ru "
"ru "
"018954cb-0a6e-744a-97f0-128e7f05d76d "
]
SELECT
product . id ,
product . event ,
product_active . active ,
product_active . active_from ,
product_active . active_to ,
product_seo . title AS seo_title ,
product_seo . keywords AS seo_keywords ,
product_seo . description AS seo_description ,
product_trans . name AS product_name ,
product_desc . preview AS product_preview ,
product_desc . description AS product_description ,
product_info . url ,
product_offer . id as product_offer_uid ,
product_offer . const as product_offer_const ,
product_offer . value as product_offer_value ,
product_offer . postfix as product_offer_postfix ,
category_offer . reference AS product_offer_reference ,
category_offer_trans . name as product_offer_name ,
category_offer_trans . postfix as product_offer_name_postfix ,
product_variation . id as product_variation_uid ,
product_variation . const as product_variation_const ,
product_variation . value as product_variation_value ,
product_variation . postfix as product_variation_postfix ,
category_variation . reference as product_variation_reference ,
category_variation_trans . name as product_variation_name ,
category_variation_trans . postfix as product_variation_name_postfix ,
product_modification . id as product_modification_uid ,
product_modification . const as product_modification_const ,
product_modification . value as product_modification_value ,
product_modification . postfix as product_modification_postfix ,
category_modification . reference as product_modification_reference ,
category_modification_trans . name as product_modification_name ,
category_modification_trans . postfix as product_modification_name_postfix ,
COALESCE (
product_modification . article , product_variation . article ,
product_offer . article , product_info . article
) AS product_article ,
CASE WHEN product_modification_image . ext IS NOT NULL THEN
JSON_AGG (
DISTINCT JSONB_BUILD_OBJECT (
'product_img_root' ,
product_modification_image . root ,
'product_img' ,
CONCAT (
'/upload/product_modification_images' ,
'/' , product_modification_image . name
),
'product_img_ext' ,
product_modification_image . ext ,
'product_img_cdn' ,
product_modification_image . cdn
)
)
WHEN product_variation_image . ext IS NOT NULL THEN
JSON_AGG (
DISTINCT JSONB_BUILD_OBJECT (
'product_img_root' ,
product_variation_image . root ,
'product_img' ,
CONCAT (
'/upload/product_variation_images' ,
'/' , product_variation_image . name
),
'product_img_ext' ,
product_variation_image . ext ,
'product_img_cdn' ,
product_variation_image . cdn
)
)
WHEN product_offer_images . ext IS NOT NULL THEN
JSON_AGG (
DISTINCT JSONB_BUILD_OBJECT (
'product_img_root' ,
product_offer_images . root ,
'product_img' ,
CONCAT (
'/upload/product_offer_images' ,
'/' , product_offer_images . name
),
'product_img_ext' ,
product_offer_images . ext ,
'product_img_cdn' ,
product_offer_images . cdn
)
)
WHEN product_photo . ext IS NOT NULL THEN
JSON_AGG (
DISTINCT JSONB_BUILD_OBJECT (
'product_img_root' ,
product_photo . root ,
'product_img' ,
CONCAT (
'/upload/product_photo' , '/' , product_photo . name
),
'product_img_ext' ,
product_photo . ext ,
'product_img_cdn' ,
product_photo . cdn
)
)
ELSE
NULL
END AS product_images ,
COALESCE (
NULLIF (
product_modification_price . price ,
0
),
NULLIF (
product_variation_price . price , 0
),
NULLIF (product_offer_price . price , 0 ),
NULLIF (product_price . price , 0 ),
0
) AS product_price ,
COALESCE (
NULLIF (
product_modification_price . old ,
0
),
NULLIF (product_variation_price . old , 0 ),
NULLIF (product_offer_price . old , 0 ),
NULLIF (product_price . old , 0 ),
0
) AS product_old_price ,
CASE WHEN product_modification_price . price IS NOT NULL
AND product_modification_price . price > 0 THEN
product_modification_price . currency
WHEN product_variation_price . price IS NOT NULL
AND product_variation_price . price > 0 THEN
product_variation_price . currency
WHEN product_offer_price . price IS NOT NULL
AND product_offer_price . price > 0 THEN
product_offer_price . currency
WHEN product_price . price IS NOT NULL
AND product_price . price > 0 THEN
product_price . currency
ELSE
NULL
END AS product_currency ,
JSON_AGG (
DISTINCT JSONB_BUILD_OBJECT (
'value' , product_region_delivery . value ,
'day' , product_region_delivery . day
)
) FILTER (
WHERE
product_region_delivery . day IS NOT NULL
) AS product_region_delivery ,
JSON_AGG (
DISTINCT JSONB_BUILD_OBJECT (
'total' , stock . total , 'reserve' , stock . reserve
)
) FILTER (
WHERE
stock . total > stock . reserve
) AS product_quantity_stocks ,
JSON_AGG (
DISTINCT JSONB_BUILD_OBJECT (
'total' ,
COALESCE (
product_modification_quantity . quantity ,
product_variation_quantity . quantity ,
product_offer_quantity . quantity ,
product_price . quantity , 0
),
'reserve' ,
COALESCE (
product_modification_quantity . reserve ,
product_variation_quantity . reserve ,
product_offer_quantity . reserve ,
product_price . reserve , 0
)
)
) AS product_quantity ,
category . id AS category_id ,
category_trans . name AS category_name ,
category_info . url AS category_url ,
category_info . minimal AS category_minimal ,
category_info . input AS category_input ,
category_info . threshold AS category_threshold ,
category_info . step AS category_step ,
CASE WHEN category_cover . name IS NOT NULL THEN
CONCAT (
'/upload/product_category_cover' ,
'/' , category_cover . name
)
ELSE
NULL
END AS category_cover_path ,
category_cover . ext AS category_cover_ext ,
category_cover . cdn AS category_cover_cdn ,
JSON_AGG (
DISTINCT JSONB_BUILD_OBJECT (
'0' ,
category_section_field . sort ,
'field_uid' ,
category_section_field . id ,
'field_const' ,
category_section_field . const ,
'field_name' ,
category_section_field . name ,
'field_alternative' ,
category_section_field . alternative ,
'field_public' ,
category_section_field . public ,
'field_card' ,
category_section_field . card ,
'field_type' ,
category_section_field . type ,
'field_trans' ,
category_section_field_trans . name ,
'field_value' ,
product_property . value
)
) AS category_section_field ,
product_invariable . id AS product_invariable_id ,
project_profile_discount . value AS project_discount
FROM
product
INNER JOIN product_active product_active ON product_active . event = product . event
LEFT JOIN product_seo product_seo ON product_seo . event = product . event
AND product_seo . local = ?
LEFT JOIN product_trans product_trans ON product_trans . event = product . event
AND product_trans . local = ?
LEFT JOIN product_description product_desc ON product_desc . event = product . event
AND product_desc . device = ?
LEFT JOIN product_info product_info ON product_info . product = product . id
INNER JOIN product_offer product_offer ON product_offer . event = product . event
AND LOWER (product_offer . value ) = ?
AND (
LOWER (product_offer . postfix ) = ?
OR product_offer . postfix IS NULL
)
LEFT JOIN product_price product_price ON product_price . event = product . event
LEFT JOIN product_photo product_photo ON product_photo . event = product . event
LEFT JOIN user_profile_region product_profile_region ON product_profile_region . value = ?
INNER JOIN product_categories_product product_event_category ON product_event_category . event = product . event
AND product_event_category . root = true
INNER JOIN users_profile project_profile ON project_profile . id = ?
LEFT JOIN product_category_offers category_offer ON category_offer . id = product_offer . category_offer
INNER JOIN product_variation product_variation ON product_variation . offer = product_offer . id
AND LOWER (product_variation . value ) = ?
AND (
LOWER (product_variation . postfix ) = ?
OR product_variation . postfix IS NULL
)
LEFT JOIN product_offer_price product_offer_price ON product_offer_price . offer = product_offer . id
LEFT JOIN product_offer_images product_offer_images ON product_offer_images . offer = product_offer . id
LEFT JOIN product_variation_images product_variation_image ON product_variation_image . variation = product_variation . id
LEFT JOIN product_offer_quantity product_offer_quantity ON product_offer_quantity . offer = product_offer . id
LEFT JOIN product_category_offers_trans category_offer_trans ON category_offer_trans . offer = category_offer . id
AND category_offer_trans . local = ?
LEFT JOIN product_category_variation category_variation ON category_variation . id = product_variation . category_variation
INNER JOIN product_modification product_modification ON product_modification . variation = product_variation . id
AND LOWER (product_modification . value ) = ?
AND (
LOWER (product_modification . postfix ) = ?
OR product_modification . postfix IS NULL
)
LEFT JOIN product_variation_price product_variation_price ON product_variation_price . variation = product_variation . id
LEFT JOIN product_category_variation_trans category_variation_trans ON category_variation_trans . variation = category_variation . id
AND category_variation_trans . local = ?
LEFT JOIN product_variation_quantity product_variation_quantity ON product_variation_quantity . variation = product_variation . id
LEFT JOIN product_category_modification category_modification ON category_modification . id = product_modification . category_modification
LEFT JOIN product_modification_price product_modification_price ON product_modification_price . modification = product_modification . id
LEFT JOIN product_modification_images product_modification_image ON product_modification_image . modification = product_modification . id
LEFT JOIN product_invariable product_invariable ON product_invariable . product = product . id
AND (
(
product_offer . const IS NOT NULL
AND product_invariable . offer = product_offer . const
)
OR (
product_offer . const IS NULL
AND product_invariable . offer IS NULL
)
)
AND (
(
product_variation . const IS NOT NULL
AND product_invariable . variation = product_variation . const
)
OR (
product_variation . const IS NULL
AND product_invariable . variation IS NULL
)
)
AND (
(
product_modification . const IS NOT NULL
AND product_invariable . modification = product_modification . const
)
OR (
product_modification . const IS NULL
AND product_invariable . modification IS NULL
)
)
LEFT JOIN product_category_modification_trans category_modification_trans ON category_modification_trans . modification = category_modification . id
AND category_modification_trans . local = ?
LEFT JOIN product_modification_quantity product_modification_quantity ON product_modification_quantity . modification = product_modification . id
INNER JOIN users_profile product_region_total ON product_region_total . event = product_profile_region . event
LEFT JOIN user_profile_delivery product_region_delivery ON product_region_delivery . event = product_profile_region . event
LEFT JOIN product_stock_total stock ON stock . profile = product_region_total . id
AND stock . product = product . id
AND CASE WHEN product_offer . const IS NOT NULL THEN
stock . offer = product_offer . const
ELSE
stock . offer IS NULL
END
AND CASE WHEN product_variation . const IS NOT NULL THEN
stock . variation = product_variation . const
ELSE
stock . variation IS NULL
END
AND CASE WHEN product_modification . const IS NOT NULL THEN
stock . modification = product_modification . const
ELSE
stock . modification IS NULL
END
INNER JOIN product_category category ON category . id = product_event_category . category
LEFT JOIN product_category_trans category_trans ON category_trans . event = category . event
AND category_trans . local = ?
LEFT JOIN product_category_info category_info ON category_info . event = category . event
LEFT JOIN product_category_section category_section ON category_section . event = category . event
LEFT JOIN product_category_cover category_cover ON category_cover . event = category . event
LEFT JOIN product_category_section_field category_section_field ON category_section_field . section = category_section . id
LEFT JOIN product_category_section_field_trans category_section_field_trans ON category_section_field_trans . field = category_section_field . id
AND category_section_field_trans . local = ?
LEFT JOIN product_property product_property ON product_property . event = product . event
AND product_property . field = category_section_field . const
LEFT JOIN user_profile_discount project_profile_discount ON project_profile_discount . event = project_profile . event
WHERE
product . id = ?
GROUP BY
product_photo . ext ,
product_offer_images . ext ,
product_variation_image . ext ,
product_modification_image . ext ,
product . id ,
product . event ,
product_active . active ,
product_active . active_from ,
product_active . active_to ,
product_seo . title ,
product_seo . keywords ,
product_seo . description ,
product_trans . name ,
product_desc . preview ,
product_desc . description ,
product_info . url ,
product_offer . id ,
product_offer . const ,
product_offer . value ,
product_offer . postfix ,
category_offer . reference ,
category_offer_trans . name ,
category_offer_trans . postfix ,
product_variation . id ,
product_variation . const ,
product_variation . value ,
product_variation . postfix ,
category_variation . reference ,
category_variation_trans . name ,
category_variation_trans . postfix ,
product_modification . id ,
product_modification . const ,
product_modification . value ,
category_modification . reference ,
category_modification_trans . name ,
category_modification_trans . postfix ,
product_modification . article ,
product_variation . article ,
product_offer . article ,
product_info . article ,
product_modification_price . price ,
product_variation_price . price ,
product_offer_price . price ,
product_price . price ,
product_modification_price . old ,
product_variation_price . old ,
product_offer_price . old ,
product_price . old ,
product_modification_price . currency ,
product_variation_price . currency ,
product_offer_price . currency ,
product_price . currency ,
category . id ,
category_trans . name ,
category_info . url ,
category_info . minimal ,
category_info . input ,
category_info . threshold ,
category_info . step ,
category_cover . name ,
category_cover . ext ,
category_cover . cdn ,
product_invariable . id ,
project_profile_discount . value Copy
SELECT product . id , product . event , product_active . active , product_active . active_from , product_active . active_to , product_seo . title AS seo_title , product_seo . keywords AS seo_keywords , product_seo . description AS seo_description , product_trans . name AS product_name , product_desc . preview AS product_preview , product_desc . description AS product_description , product_info . url , product_offer . id as product_offer_uid , product_offer . const as product_offer_const , product_offer . value as product_offer_value , product_offer . postfix as product_offer_postfix , category_offer . reference AS product_offer_reference , category_offer_trans . name as product_offer_name , category_offer_trans . postfix as product_offer_name_postfix , product_variation . id as product_variation_uid , product_variation . const as product_variation_const , product_variation . value as product_variation_value , product_variation . postfix as product_variation_postfix , category_variation . reference as product_variation_reference , category_variation_trans . name as product_variation_name , category_variation_trans . postfix as product_variation_name_postfix , product_modification . id as product_modification_uid , product_modification . const as product_modification_const , product_modification . value as product_modification_value , product_modification . postfix as product_modification_postfix , category_modification . reference as product_modification_reference , category_modification_trans . name as product_modification_name , category_modification_trans . postfix as product_modification_name_postfix ,
COALESCE (
product_modification . article ,
product_variation . article ,
product_offer . article ,
product_info . article
) AS product_article
,
CASE
WHEN product_modification_image . ext IS NOT NULL THEN
JSON_AGG
(DISTINCT
JSONB_BUILD_OBJECT
(
'product_img_root' , product_modification_image . root ,
'product_img' , CONCAT ( '/upload/product_modification_images' , '/' , product_modification_image . name ),
'product_img_ext' , product_modification_image . ext ,
'product_img_cdn' , product_modification_image . cdn
)
)
WHEN product_variation_image . ext IS NOT NULL THEN
JSON_AGG
(DISTINCT
JSONB_BUILD_OBJECT
(
'product_img_root' , product_variation_image . root ,
'product_img' , CONCAT ( '/upload/product_variation_images' , '/' , product_variation_image . name ),
'product_img_ext' , product_variation_image . ext ,
'product_img_cdn' , product_variation_image . cdn
)
)
WHEN product_offer_images . ext IS NOT NULL THEN
JSON_AGG
(DISTINCT
JSONB_BUILD_OBJECT
(
'product_img_root' , product_offer_images . root ,
'product_img' , CONCAT ( '/upload/product_offer_images' , '/' , product_offer_images . name ),
'product_img_ext' , product_offer_images . ext ,
'product_img_cdn' , product_offer_images . cdn
)
)
WHEN product_photo . ext IS NOT NULL THEN
JSON_AGG
(DISTINCT
JSONB_BUILD_OBJECT
(
'product_img_root' , product_photo . root ,
'product_img' , CONCAT ( '/upload/product_photo' , '/' , product_photo . name ),
'product_img_ext' , product_photo . ext ,
'product_img_cdn' , product_photo . cdn
)
)
ELSE NULL
END
AS product_images ,
COALESCE (
NULLIF (product_modification_price . price , 0 ),
NULLIF (product_variation_price . price , 0 ),
NULLIF (product_offer_price . price , 0 ),
NULLIF (product_price . price , 0 ),
0
) AS product_price
,
COALESCE (
NULLIF (product_modification_price . old , 0 ),
NULLIF (product_variation_price . old , 0 ),
NULLIF (product_offer_price . old , 0 ),
NULLIF (product_price . old , 0 ),
0
) AS product_old_price
,
CASE
WHEN product_modification_price . price IS NOT NULL AND product_modification_price . price > 0
THEN product_modification_price . currency
WHEN product_variation_price . price IS NOT NULL AND product_variation_price . price > 0
THEN product_variation_price . currency
WHEN product_offer_price . price IS NOT NULL AND product_offer_price . price > 0
THEN product_offer_price . currency
WHEN product_price . price IS NOT NULL AND product_price . price > 0
THEN product_price . currency
ELSE NULL
END AS product_currency
, JSON_AGG (
DISTINCT JSONB_BUILD_OBJECT (
'value' , product_region_delivery . value ,
'day' , product_region_delivery . day
)) FILTER (WHERE product_region_delivery . day IS NOT NULL )
AS product_region_delivery , JSON_AGG (
DISTINCT JSONB_BUILD_OBJECT (
'total' , stock . total ,
'reserve' , stock . reserve
)) FILTER (WHERE stock . total > stock . reserve )
AS product_quantity_stocks , JSON_AGG (
DISTINCT JSONB_BUILD_OBJECT (
'total' , COALESCE (
product_modification_quantity . quantity ,
product_variation_quantity . quantity ,
product_offer_quantity . quantity ,
product_price . quantity ,
0
),
'reserve' , COALESCE (
product_modification_quantity . reserve ,
product_variation_quantity . reserve ,
product_offer_quantity . reserve ,
product_price . reserve ,
0
)
) )
AS product_quantity , category . id AS category_id , category_trans . name AS category_name , category_info . url AS category_url , category_info . minimal AS category_minimal , category_info . input AS category_input , category_info . threshold AS category_threshold , category_info . step AS category_step ,
CASE
WHEN category_cover . name IS NOT NULL THEN
CONCAT ( '/upload/product_category_cover' , '/' , category_cover . name )
ELSE NULL
END AS category_cover_path
, category_cover . ext AS category_cover_ext , category_cover . cdn AS category_cover_cdn , JSON_AGG
( DISTINCT
JSONB_BUILD_OBJECT
(
'0' , category_section_field . sort ,
'field_uid' , category_section_field . id ,
'field_const' , category_section_field . const ,
'field_name' , category_section_field . name ,
'field_alternative' , category_section_field . alternative ,
'field_public' , category_section_field . public ,
'field_card' , category_section_field . card ,
'field_type' , category_section_field . type ,
'field_trans' , category_section_field_trans . name ,
'field_value' , product_property . value
)
)
AS category_section_field , product_invariable . id AS product_invariable_id , project_profile_discount . value AS project_discount FROM product INNER JOIN product_active product_active ON product_active . event = product . event LEFT JOIN product_seo product_seo ON product_seo . event = product . event AND product_seo . local = 'ru' LEFT JOIN product_trans product_trans ON product_trans . event = product . event AND product_trans . local = 'ru' LEFT JOIN product_description product_desc ON product_desc . event = product . event AND product_desc . device = 'pc' LEFT JOIN product_info product_info ON product_info . product = product . id INNER JOIN product_offer product_offer ON product_offer . event = product . event AND LOWER (product_offer . value ) = '19' AND ( LOWER (product_offer . postfix ) = '111w' OR product_offer . postfix IS NULL ) LEFT JOIN product_price product_price ON product_price . event = product . event LEFT JOIN product_photo product_photo ON product_photo . event = product . event LEFT JOIN user_profile_region product_profile_region ON product_profile_region . value = '201042a6-c35d-7bc4-9cb9-ef8bc1c8711e' INNER JOIN product_categories_product product_event_category ON product_event_category . event = product . event AND product_event_category . root = true INNER JOIN users_profile project_profile ON
project_profile . id = '019469c3-700f-76a9-9b34-ccde7b4e6f49' LEFT JOIN product_category_offers category_offer ON category_offer . id = product_offer . category_offer INNER JOIN product_variation product_variation ON product_variation . offer = product_offer . id AND LOWER (product_variation . value ) = '255' AND ( LOWER (product_variation . postfix ) = '111w' OR product_variation . postfix IS NULL ) LEFT JOIN product_offer_price product_offer_price ON product_offer_price . offer = product_offer . id LEFT JOIN product_offer_images product_offer_images ON product_offer_images . offer = product_offer . id LEFT JOIN product_variation_images product_variation_image ON product_variation_image . variation = product_variation . id LEFT JOIN product_offer_quantity product_offer_quantity ON product_offer_quantity . offer = product_offer . id LEFT JOIN product_category_offers_trans category_offer_trans ON category_offer_trans . offer = category_offer . id AND category_offer_trans . local = 'ru' LEFT JOIN product_category_variation category_variation ON category_variation . id = product_variation . category_variation INNER JOIN product_modification product_modification ON product_modification . variation = product_variation . id AND LOWER (product_modification . value ) = '55' AND ( LOWER (product_modification . postfix ) = '111w' OR product_modification . postfix IS NULL ) LEFT JOIN product_variation_price product_variation_price ON product_variation_price . variation = product_variation . id LEFT JOIN product_category_variation_trans category_variation_trans ON category_variation_trans . variation = category_variation . id AND category_variation_trans . local = 'ru' LEFT JOIN product_variation_quantity product_variation_quantity ON product_variation_quantity . variation = product_variation . id LEFT JOIN product_category_modification category_modification ON category_modification . id = product_modification . category_modification LEFT JOIN product_modification_price product_modification_price ON product_modification_price . modification = product_modification . id LEFT JOIN product_modification_images product_modification_image ON product_modification_image . modification = product_modification . id LEFT JOIN product_invariable product_invariable ON
product_invariable . product = product . id AND
(
(product_offer . const IS NOT NULL AND product_invariable . offer = product_offer . const ) OR
(product_offer . const IS NULL AND product_invariable . offer IS NULL )
)
AND
(
(product_variation . const IS NOT NULL AND product_invariable . variation = product_variation . const ) OR
(product_variation . const IS NULL AND product_invariable . variation IS NULL )
)
AND
(
(product_modification . const IS NOT NULL AND product_invariable . modification = product_modification . const ) OR
(product_modification . const IS NULL AND product_invariable . modification IS NULL )
)
LEFT JOIN product_category_modification_trans category_modification_trans ON category_modification_trans . modification = category_modification . id AND category_modification_trans . local = 'ru' LEFT JOIN product_modification_quantity product_modification_quantity ON product_modification_quantity . modification = product_modification . id INNER JOIN users_profile product_region_total ON product_region_total . event = product_profile_region . event LEFT JOIN user_profile_delivery product_region_delivery ON product_region_delivery . event = product_profile_region . event LEFT JOIN product_stock_total stock ON
stock . profile = product_region_total . id AND
stock . product = product . id
AND
CASE
WHEN product_offer . const IS NOT NULL
THEN stock . offer = product_offer . const
ELSE stock . offer IS NULL
END
AND
CASE
WHEN product_variation . const IS NOT NULL
THEN stock . variation = product_variation . const
ELSE stock . variation IS NULL
END
AND
CASE
WHEN product_modification . const IS NOT NULL
THEN stock . modification = product_modification . const
ELSE stock . modification IS NULL
END
INNER JOIN product_category category ON category . id = product_event_category . category LEFT JOIN product_category_trans category_trans ON category_trans . event = category . event AND category_trans . local = 'ru' LEFT JOIN product_category_info category_info ON category_info . event = category . event LEFT JOIN product_category_section category_section ON category_section . event = category . event LEFT JOIN product_category_cover category_cover ON category_cover . event = category . event LEFT JOIN product_category_section_field category_section_field ON category_section_field . section = category_section . id LEFT JOIN product_category_section_field_trans category_section_field_trans ON category_section_field_trans . field = category_section_field . id
AND category_section_field_trans . local = 'ru' LEFT JOIN product_property product_property ON product_property . event = product . event AND product_property . field = category_section_field . const LEFT JOIN user_profile_discount project_profile_discount ON
project_profile_discount . event = project_profile . event WHERE product . id = '018954cb-0a6e-744a-97f0-128e7f05d76d' GROUP BY product_photo . ext , product_offer_images . ext , product_variation_image . ext , product_modification_image . ext , product . id , product . event , product_active . active , product_active . active_from , product_active . active_to , product_seo . title , product_seo . keywords , product_seo . description , product_trans . name , product_desc . preview , product_desc . description , product_info . url , product_offer . id , product_offer . const , product_offer . value , product_offer . postfix , category_offer . reference , category_offer_trans . name , category_offer_trans . postfix , product_variation . id , product_variation . const , product_variation . value , product_variation . postfix , category_variation . reference , category_variation_trans . name , category_variation_trans . postfix , product_modification . id , product_modification . const , product_modification . value , category_modification . reference , category_modification_trans . name , category_modification_trans . postfix , product_modification . article , product_variation . article , product_offer . article , product_info . article , product_modification_price . price , product_variation_price . price , product_offer_price . price , product_price . price , product_modification_price . old , product_variation_price . old , product_offer_price . old , product_price . old , product_modification_price . currency , product_variation_price . currency , product_offer_price . currency , product_price . currency , category . id , category_trans . name , category_info . url , category_info . minimal , category_info . input , category_info . threshold , category_info . step , category_cover . name , category_cover . ext , category_cover . cdn , product_invariable . id , project_profile_discount . value ; Copy
3
0.85 ms
SELECT invariable . id FROM product_invariable invariable WHERE (invariable . product = ? ) AND (invariable . offer = ? ) AND (invariable . variation = ? ) AND (invariable . modification = ? )
Parameters :
[
"018954cb-0a6e-744a-97f0-128e7f05d76d "
"018954cb-0a03-7cb1-83e6-2ea563c4cf13 "
"018954cb-09f4-7d0c-a875-036719f3c88d "
"018ecd6f-dc5a-7c33-8457-eba606a7e6c6 "
]
SELECT invariable . id FROM product_invariable invariable WHERE (invariable . product = '018954cb-0a6e-744a-97f0-128e7f05d76d' ) AND (invariable . offer = '018954cb-0a03-7cb1-83e6-2ea563c4cf13' ) AND (invariable . variation = '018954cb-09f4-7d0c-a875-036719f3c88d' ) AND (invariable . modification = '018ecd6f-dc5a-7c33-8457-eba606a7e6c6' ); Copy
4
71.11 ms
SELECT product . id , product . event , product_active . active , product_active . active_from , product_active . active_to , product_seo . title AS seo_title , product_seo . keywords AS seo_keywords , product_seo . description AS seo_description , product_trans . name AS product_name , product_desc . preview AS product_preview , product_desc . description AS product_description , product_info . url , product_offer . id as product_offer_uid , product_offer . const as product_offer_const , product_offer . value as product_offer_value , product_offer . postfix as product_offer_postfix , category_offer . reference AS product_offer_reference , category_offer_trans . name as product_offer_name , category_offer_trans . postfix as product_offer_name_postfix , product_variation . id as product_variation_uid , product_variation . const as product_variation_const , product_variation . value as product_variation_value , product_variation . postfix as product_variation_postfix , category_variation . reference as product_variation_reference , category_variation_trans . name as product_variation_name , category_variation_trans . postfix as product_variation_name_postfix , product_modification . id as product_modification_uid , product_modification . const as product_modification_const , product_modification . value as product_modification_value , product_modification . postfix as product_modification_postfix , category_modification . reference as product_modification_reference , category_modification_trans . name as product_modification_name , category_modification_trans . postfix as product_modification_name_postfix ,
COALESCE (
product_modification . article ,
product_variation . article ,
product_offer . article ,
product_info . article
) AS product_article
,
CASE
WHEN product_modification_image . ext IS NOT NULL THEN
JSON_AGG
(DISTINCT
JSONB_BUILD_OBJECT
(
'product_img_root' , product_modification_image . root ,
'product_img' , CONCAT ( '/upload/product_modification_images' , '/' , product_modification_image . name ),
'product_img_ext' , product_modification_image . ext ,
'product_img_cdn' , product_modification_image . cdn
)
)
WHEN product_variation_image . ext IS NOT NULL THEN
JSON_AGG
(DISTINCT
JSONB_BUILD_OBJECT
(
'product_img_root' , product_variation_image . root ,
'product_img' , CONCAT ( '/upload/product_variation_images' , '/' , product_variation_image . name ),
'product_img_ext' , product_variation_image . ext ,
'product_img_cdn' , product_variation_image . cdn
)
)
WHEN product_offer_images . ext IS NOT NULL THEN
JSON_AGG
(DISTINCT
JSONB_BUILD_OBJECT
(
'product_img_root' , product_offer_images . root ,
'product_img' , CONCAT ( '/upload/product_offer_images' , '/' , product_offer_images . name ),
'product_img_ext' , product_offer_images . ext ,
'product_img_cdn' , product_offer_images . cdn
)
)
WHEN product_photo . ext IS NOT NULL THEN
JSON_AGG
(DISTINCT
JSONB_BUILD_OBJECT
(
'product_img_root' , product_photo . root ,
'product_img' , CONCAT ( '/upload/product_photo' , '/' , product_photo . name ),
'product_img_ext' , product_photo . ext ,
'product_img_cdn' , product_photo . cdn
)
)
ELSE NULL
END
AS product_images ,
COALESCE (
NULLIF (product_modification_price . price , 0 ),
NULLIF (product_variation_price . price , 0 ),
NULLIF (product_offer_price . price , 0 ),
NULLIF (product_price . price , 0 ),
0
) AS product_price
,
COALESCE (
NULLIF (product_modification_price . old , 0 ),
NULLIF (product_variation_price . old , 0 ),
NULLIF (product_offer_price . old , 0 ),
NULLIF (product_price . old , 0 ),
0
) AS product_old_price
,
CASE
WHEN product_modification_price . price IS NOT NULL AND product_modification_price . price > 0
THEN product_modification_price . currency
WHEN product_variation_price . price IS NOT NULL AND product_variation_price . price > 0
THEN product_variation_price . currency
WHEN product_offer_price . price IS NOT NULL AND product_offer_price . price > 0
THEN product_offer_price . currency
WHEN product_price . price IS NOT NULL AND product_price . price > 0
THEN product_price . currency
ELSE NULL
END AS product_currency
, JSON_AGG (
DISTINCT JSONB_BUILD_OBJECT (
'value' , product_region_delivery . value ,
'day' , product_region_delivery . day
)) FILTER (WHERE product_region_delivery . day IS NOT NULL )
AS product_region_delivery , JSON_AGG (
DISTINCT JSONB_BUILD_OBJECT (
'total' , stock . total ,
'reserve' , stock . reserve
)) FILTER (WHERE stock . total > stock . reserve )
AS product_quantity_stocks , JSON_AGG (
DISTINCT JSONB_BUILD_OBJECT (
'total' , COALESCE (
product_modification_quantity . quantity ,
product_variation_quantity . quantity ,
product_offer_quantity . quantity ,
product_price . quantity ,
0
),
'reserve' , COALESCE (
product_modification_quantity . reserve ,
product_variation_quantity . reserve ,
product_offer_quantity . reserve ,
product_price . reserve ,
0
)
) )
AS product_quantity , category . id AS category_id , category_trans . name AS category_name , category_info . url AS category_url , category_info . minimal AS category_minimal , category_info . input AS category_input , category_info . threshold AS category_threshold , category_info . step AS category_step ,
CASE
WHEN category_cover . name IS NOT NULL THEN
CONCAT ( '/upload/product_category_cover' , '/' , category_cover . name )
ELSE NULL
END AS category_cover_path
, category_cover . ext AS category_cover_ext , category_cover . cdn AS category_cover_cdn , JSON_AGG
( DISTINCT
JSONB_BUILD_OBJECT
(
'0' , category_section_field . sort ,
'field_uid' , category_section_field . id ,
'field_const' , category_section_field . const ,
'field_name' , category_section_field . name ,
'field_alternative' , category_section_field . alternative ,
'field_public' , category_section_field . public ,
'field_card' , category_section_field . card ,
'field_type' , category_section_field . type ,
'field_trans' , category_section_field_trans . name ,
'field_value' , product_property . value
)
)
AS category_section_field , product_invariable . id AS product_invariable_id , project_profile_discount . value AS project_discount FROM product INNER JOIN product_active product_active ON product_active . event = product . event LEFT JOIN product_seo product_seo ON product_seo . event = product . event AND product_seo . local = ? LEFT JOIN product_trans product_trans ON product_trans . event = product . event AND product_trans . local = ? LEFT JOIN product_description product_desc ON product_desc . event = product . event AND product_desc . device = ? LEFT JOIN product_info product_info ON product_info . product = product . id INNER JOIN product_offer product_offer ON product_offer . event = product . event AND LOWER (product_offer . value ) = ? AND ( LOWER (product_offer . postfix ) = ? OR product_offer . postfix IS NULL ) LEFT JOIN product_price product_price ON product_price . event = product . event LEFT JOIN product_photo product_photo ON product_photo . event = product . event LEFT JOIN user_profile_region product_profile_region ON product_profile_region . value = ? INNER JOIN product_categories_product product_event_category ON product_event_category . event = product . event AND product_event_category . root = true INNER JOIN users_profile project_profile ON
project_profile . id = ? LEFT JOIN product_category_offers category_offer ON category_offer . id = product_offer . category_offer INNER JOIN product_variation product_variation ON product_variation . offer = product_offer . id AND LOWER (product_variation . value ) = ? AND ( LOWER (product_variation . postfix ) = ? OR product_variation . postfix IS NULL ) LEFT JOIN product_offer_price product_offer_price ON product_offer_price . offer = product_offer . id LEFT JOIN product_offer_images product_offer_images ON product_offer_images . offer = product_offer . id LEFT JOIN product_variation_images product_variation_image ON product_variation_image . variation = product_variation . id LEFT JOIN product_offer_quantity product_offer_quantity ON product_offer_quantity . offer = product_offer . id LEFT JOIN product_category_offers_trans category_offer_trans ON category_offer_trans . offer = category_offer . id AND category_offer_trans . local = ? LEFT JOIN product_category_variation category_variation ON category_variation . id = product_variation . category_variation INNER JOIN product_modification product_modification ON product_modification . variation = product_variation . id AND LOWER (product_modification . value ) = ? AND ( LOWER (product_modification . postfix ) = ? OR product_modification . postfix IS NULL ) LEFT JOIN product_variation_price product_variation_price ON product_variation_price . variation = product_variation . id LEFT JOIN product_category_variation_trans category_variation_trans ON category_variation_trans . variation = category_variation . id AND category_variation_trans . local = ? LEFT JOIN product_variation_quantity product_variation_quantity ON product_variation_quantity . variation = product_variation . id LEFT JOIN product_category_modification category_modification ON category_modification . id = product_modification . category_modification LEFT JOIN product_modification_price product_modification_price ON product_modification_price . modification = product_modification . id LEFT JOIN product_modification_images product_modification_image ON product_modification_image . modification = product_modification . id LEFT JOIN product_invariable product_invariable ON
product_invariable . product = product . id AND
(
(product_offer . const IS NOT NULL AND product_invariable . offer = product_offer . const ) OR
(product_offer . const IS NULL AND product_invariable . offer IS NULL )
)
AND
(
(product_variation . const IS NOT NULL AND product_invariable . variation = product_variation . const ) OR
(product_variation . const IS NULL AND product_invariable . variation IS NULL )
)
AND
(
(product_modification . const IS NOT NULL AND product_invariable . modification = product_modification . const ) OR
(product_modification . const IS NULL AND product_invariable . modification IS NULL )
)
LEFT JOIN product_category_modification_trans category_modification_trans ON category_modification_trans . modification = category_modification . id AND category_modification_trans . local = ? LEFT JOIN product_modification_quantity product_modification_quantity ON product_modification_quantity . modification = product_modification . id INNER JOIN users_profile product_region_total ON product_region_total . event = product_profile_region . event LEFT JOIN user_profile_delivery product_region_delivery ON product_region_delivery . event = product_profile_region . event LEFT JOIN product_stock_total stock ON
stock . profile = product_region_total . id AND
stock . product = product . id
AND
CASE
WHEN product_offer . const IS NOT NULL
THEN stock . offer = product_offer . const
ELSE stock . offer IS NULL
END
AND
CASE
WHEN product_variation . const IS NOT NULL
THEN stock . variation = product_variation . const
ELSE stock . variation IS NULL
END
AND
CASE
WHEN product_modification . const IS NOT NULL
THEN stock . modification = product_modification . const
ELSE stock . modification IS NULL
END
INNER JOIN product_category category ON category . id = product_event_category . category LEFT JOIN product_category_trans category_trans ON category_trans . event = category . event AND category_trans . local = ? LEFT JOIN product_category_info category_info ON category_info . event = category . event LEFT JOIN product_category_section category_section ON category_section . event = category . event LEFT JOIN product_category_cover category_cover ON category_cover . event = category . event LEFT JOIN product_category_section_field category_section_field ON category_section_field . section = category_section . id LEFT JOIN product_category_section_field_trans category_section_field_trans ON category_section_field_trans . field = category_section_field . id
AND category_section_field_trans . local = ? LEFT JOIN product_property product_property ON product_property . event = product . event AND product_property . field = category_section_field . const LEFT JOIN user_profile_discount project_profile_discount ON
project_profile_discount . event = project_profile . event WHERE product . id = ? GROUP BY product_photo . ext , product_offer_images . ext , product_variation_image . ext , product_modification_image . ext , product . id , product . event , product_active . active , product_active . active_from , product_active . active_to , product_seo . title , product_seo . keywords , product_seo . description , product_trans . name , product_desc . preview , product_desc . description , product_info . url , product_offer . id , product_offer . const , product_offer . value , product_offer . postfix , category_offer . reference , category_offer_trans . name , category_offer_trans . postfix , product_variation . id , product_variation . const , product_variation . value , product_variation . postfix , category_variation . reference , category_variation_trans . name , category_variation_trans . postfix , product_modification . id , product_modification . const , product_modification . value , category_modification . reference , category_modification_trans . name , category_modification_trans . postfix , product_modification . article , product_variation . article , product_offer . article , product_info . article , product_modification_price . price , product_variation_price . price , product_offer_price . price , product_price . price , product_modification_price . old , product_variation_price . old , product_offer_price . old , product_price . old , product_modification_price . currency , product_variation_price . currency , product_offer_price . currency , product_price . currency , category . id , category_trans . name , category_info . url , category_info . minimal , category_info . input , category_info . threshold , category_info . step , category_cover . name , category_cover . ext , category_cover . cdn , product_invariable . id , project_profile_discount . value
Parameters :
[
"ru "
"ru "
"pc "
"19 "
"111w "
"201042a6-c35d-7bc4-9cb9-ef8bc1c8711e "
"019469c3-700f-76a9-9b34-ccde7b4e6f49 "
"255 "
"111w "
"ru "
"55 "
"111w "
"ru "
"ru "
"ru "
"ru "
"018954cb-0a6e-744a-97f0-128e7f05d76d "
]
SELECT
product . id ,
product . event ,
product_active . active ,
product_active . active_from ,
product_active . active_to ,
product_seo . title AS seo_title ,
product_seo . keywords AS seo_keywords ,
product_seo . description AS seo_description ,
product_trans . name AS product_name ,
product_desc . preview AS product_preview ,
product_desc . description AS product_description ,
product_info . url ,
product_offer . id as product_offer_uid ,
product_offer . const as product_offer_const ,
product_offer . value as product_offer_value ,
product_offer . postfix as product_offer_postfix ,
category_offer . reference AS product_offer_reference ,
category_offer_trans . name as product_offer_name ,
category_offer_trans . postfix as product_offer_name_postfix ,
product_variation . id as product_variation_uid ,
product_variation . const as product_variation_const ,
product_variation . value as product_variation_value ,
product_variation . postfix as product_variation_postfix ,
category_variation . reference as product_variation_reference ,
category_variation_trans . name as product_variation_name ,
category_variation_trans . postfix as product_variation_name_postfix ,
product_modification . id as product_modification_uid ,
product_modification . const as product_modification_const ,
product_modification . value as product_modification_value ,
product_modification . postfix as product_modification_postfix ,
category_modification . reference as product_modification_reference ,
category_modification_trans . name as product_modification_name ,
category_modification_trans . postfix as product_modification_name_postfix ,
COALESCE (
product_modification . article , product_variation . article ,
product_offer . article , product_info . article
) AS product_article ,
CASE WHEN product_modification_image . ext IS NOT NULL THEN
JSON_AGG (
DISTINCT JSONB_BUILD_OBJECT (
'product_img_root' ,
product_modification_image . root ,
'product_img' ,
CONCAT (
'/upload/product_modification_images' ,
'/' , product_modification_image . name
),
'product_img_ext' ,
product_modification_image . ext ,
'product_img_cdn' ,
product_modification_image . cdn
)
)
WHEN product_variation_image . ext IS NOT NULL THEN
JSON_AGG (
DISTINCT JSONB_BUILD_OBJECT (
'product_img_root' ,
product_variation_image . root ,
'product_img' ,
CONCAT (
'/upload/product_variation_images' ,
'/' , product_variation_image . name
),
'product_img_ext' ,
product_variation_image . ext ,
'product_img_cdn' ,
product_variation_image . cdn
)
)
WHEN product_offer_images . ext IS NOT NULL THEN
JSON_AGG (
DISTINCT JSONB_BUILD_OBJECT (
'product_img_root' ,
product_offer_images . root ,
'product_img' ,
CONCAT (
'/upload/product_offer_images' ,
'/' , product_offer_images . name
),
'product_img_ext' ,
product_offer_images . ext ,
'product_img_cdn' ,
product_offer_images . cdn
)
)
WHEN product_photo . ext IS NOT NULL THEN
JSON_AGG (
DISTINCT JSONB_BUILD_OBJECT (
'product_img_root' ,
product_photo . root ,
'product_img' ,
CONCAT (
'/upload/product_photo' , '/' , product_photo . name
),
'product_img_ext' ,
product_photo . ext ,
'product_img_cdn' ,
product_photo . cdn
)
)
ELSE
NULL
END AS product_images ,
COALESCE (
NULLIF (
product_modification_price . price ,
0
),
NULLIF (
product_variation_price . price , 0
),
NULLIF (product_offer_price . price , 0 ),
NULLIF (product_price . price , 0 ),
0
) AS product_price ,
COALESCE (
NULLIF (
product_modification_price . old ,
0
),
NULLIF (product_variation_price . old , 0 ),
NULLIF (product_offer_price . old , 0 ),
NULLIF (product_price . old , 0 ),
0
) AS product_old_price ,
CASE WHEN product_modification_price . price IS NOT NULL
AND product_modification_price . price > 0 THEN
product_modification_price . currency
WHEN product_variation_price . price IS NOT NULL
AND product_variation_price . price > 0 THEN
product_variation_price . currency
WHEN product_offer_price . price IS NOT NULL
AND product_offer_price . price > 0 THEN
product_offer_price . currency
WHEN product_price . price IS NOT NULL
AND product_price . price > 0 THEN
product_price . currency
ELSE
NULL
END AS product_currency ,
JSON_AGG (
DISTINCT JSONB_BUILD_OBJECT (
'value' , product_region_delivery . value ,
'day' , product_region_delivery . day
)
) FILTER (
WHERE
product_region_delivery . day IS NOT NULL
) AS product_region_delivery ,
JSON_AGG (
DISTINCT JSONB_BUILD_OBJECT (
'total' , stock . total , 'reserve' , stock . reserve
)
) FILTER (
WHERE
stock . total > stock . reserve
) AS product_quantity_stocks ,
JSON_AGG (
DISTINCT JSONB_BUILD_OBJECT (
'total' ,
COALESCE (
product_modification_quantity . quantity ,
product_variation_quantity . quantity ,
product_offer_quantity . quantity ,
product_price . quantity , 0
),
'reserve' ,
COALESCE (
product_modification_quantity . reserve ,
product_variation_quantity . reserve ,
product_offer_quantity . reserve ,
product_price . reserve , 0
)
)
) AS product_quantity ,
category . id AS category_id ,
category_trans . name AS category_name ,
category_info . url AS category_url ,
category_info . minimal AS category_minimal ,
category_info . input AS category_input ,
category_info . threshold AS category_threshold ,
category_info . step AS category_step ,
CASE WHEN category_cover . name IS NOT NULL THEN
CONCAT (
'/upload/product_category_cover' ,
'/' , category_cover . name
)
ELSE
NULL
END AS category_cover_path ,
category_cover . ext AS category_cover_ext ,
category_cover . cdn AS category_cover_cdn ,
JSON_AGG (
DISTINCT JSONB_BUILD_OBJECT (
'0' ,
category_section_field . sort ,
'field_uid' ,
category_section_field . id ,
'field_const' ,
category_section_field . const ,
'field_name' ,
category_section_field . name ,
'field_alternative' ,
category_section_field . alternative ,
'field_public' ,
category_section_field . public ,
'field_card' ,
category_section_field . card ,
'field_type' ,
category_section_field . type ,
'field_trans' ,
category_section_field_trans . name ,
'field_value' ,
product_property . value
)
) AS category_section_field ,
product_invariable . id AS product_invariable_id ,
project_profile_discount . value AS project_discount
FROM
product
INNER JOIN product_active product_active ON product_active . event = product . event
LEFT JOIN product_seo product_seo ON product_seo . event = product . event
AND product_seo . local = ?
LEFT JOIN product_trans product_trans ON product_trans . event = product . event
AND product_trans . local = ?
LEFT JOIN product_description product_desc ON product_desc . event = product . event
AND product_desc . device = ?
LEFT JOIN product_info product_info ON product_info . product = product . id
INNER JOIN product_offer product_offer ON product_offer . event = product . event
AND LOWER (product_offer . value ) = ?
AND (
LOWER (product_offer . postfix ) = ?
OR product_offer . postfix IS NULL
)
LEFT JOIN product_price product_price ON product_price . event = product . event
LEFT JOIN product_photo product_photo ON product_photo . event = product . event
LEFT JOIN user_profile_region product_profile_region ON product_profile_region . value = ?
INNER JOIN product_categories_product product_event_category ON product_event_category . event = product . event
AND product_event_category . root = true
INNER JOIN users_profile project_profile ON project_profile . id = ?
LEFT JOIN product_category_offers category_offer ON category_offer . id = product_offer . category_offer
INNER JOIN product_variation product_variation ON product_variation . offer = product_offer . id
AND LOWER (product_variation . value ) = ?
AND (
LOWER (product_variation . postfix ) = ?
OR product_variation . postfix IS NULL
)
LEFT JOIN product_offer_price product_offer_price ON product_offer_price . offer = product_offer . id
LEFT JOIN product_offer_images product_offer_images ON product_offer_images . offer = product_offer . id
LEFT JOIN product_variation_images product_variation_image ON product_variation_image . variation = product_variation . id
LEFT JOIN product_offer_quantity product_offer_quantity ON product_offer_quantity . offer = product_offer . id
LEFT JOIN product_category_offers_trans category_offer_trans ON category_offer_trans . offer = category_offer . id
AND category_offer_trans . local = ?
LEFT JOIN product_category_variation category_variation ON category_variation . id = product_variation . category_variation
INNER JOIN product_modification product_modification ON product_modification . variation = product_variation . id
AND LOWER (product_modification . value ) = ?
AND (
LOWER (product_modification . postfix ) = ?
OR product_modification . postfix IS NULL
)
LEFT JOIN product_variation_price product_variation_price ON product_variation_price . variation = product_variation . id
LEFT JOIN product_category_variation_trans category_variation_trans ON category_variation_trans . variation = category_variation . id
AND category_variation_trans . local = ?
LEFT JOIN product_variation_quantity product_variation_quantity ON product_variation_quantity . variation = product_variation . id
LEFT JOIN product_category_modification category_modification ON category_modification . id = product_modification . category_modification
LEFT JOIN product_modification_price product_modification_price ON product_modification_price . modification = product_modification . id
LEFT JOIN product_modification_images product_modification_image ON product_modification_image . modification = product_modification . id
LEFT JOIN product_invariable product_invariable ON product_invariable . product = product . id
AND (
(
product_offer . const IS NOT NULL
AND product_invariable . offer = product_offer . const
)
OR (
product_offer . const IS NULL
AND product_invariable . offer IS NULL
)
)
AND (
(
product_variation . const IS NOT NULL
AND product_invariable . variation = product_variation . const
)
OR (
product_variation . const IS NULL
AND product_invariable . variation IS NULL
)
)
AND (
(
product_modification . const IS NOT NULL
AND product_invariable . modification = product_modification . const
)
OR (
product_modification . const IS NULL
AND product_invariable . modification IS NULL
)
)
LEFT JOIN product_category_modification_trans category_modification_trans ON category_modification_trans . modification = category_modification . id
AND category_modification_trans . local = ?
LEFT JOIN product_modification_quantity product_modification_quantity ON product_modification_quantity . modification = product_modification . id
INNER JOIN users_profile product_region_total ON product_region_total . event = product_profile_region . event
LEFT JOIN user_profile_delivery product_region_delivery ON product_region_delivery . event = product_profile_region . event
LEFT JOIN product_stock_total stock ON stock . profile = product_region_total . id
AND stock . product = product . id
AND CASE WHEN product_offer . const IS NOT NULL THEN
stock . offer = product_offer . const
ELSE
stock . offer IS NULL
END
AND CASE WHEN product_variation . const IS NOT NULL THEN
stock . variation = product_variation . const
ELSE
stock . variation IS NULL
END
AND CASE WHEN product_modification . const IS NOT NULL THEN
stock . modification = product_modification . const
ELSE
stock . modification IS NULL
END
INNER JOIN product_category category ON category . id = product_event_category . category
LEFT JOIN product_category_trans category_trans ON category_trans . event = category . event
AND category_trans . local = ?
LEFT JOIN product_category_info category_info ON category_info . event = category . event
LEFT JOIN product_category_section category_section ON category_section . event = category . event
LEFT JOIN product_category_cover category_cover ON category_cover . event = category . event
LEFT JOIN product_category_section_field category_section_field ON category_section_field . section = category_section . id
LEFT JOIN product_category_section_field_trans category_section_field_trans ON category_section_field_trans . field = category_section_field . id
AND category_section_field_trans . local = ?
LEFT JOIN product_property product_property ON product_property . event = product . event
AND product_property . field = category_section_field . const
LEFT JOIN user_profile_discount project_profile_discount ON project_profile_discount . event = project_profile . event
WHERE
product . id = ?
GROUP BY
product_photo . ext ,
product_offer_images . ext ,
product_variation_image . ext ,
product_modification_image . ext ,
product . id ,
product . event ,
product_active . active ,
product_active . active_from ,
product_active . active_to ,
product_seo . title ,
product_seo . keywords ,
product_seo . description ,
product_trans . name ,
product_desc . preview ,
product_desc . description ,
product_info . url ,
product_offer . id ,
product_offer . const ,
product_offer . value ,
product_offer . postfix ,
category_offer . reference ,
category_offer_trans . name ,
category_offer_trans . postfix ,
product_variation . id ,
product_variation . const ,
product_variation . value ,
product_variation . postfix ,
category_variation . reference ,
category_variation_trans . name ,
category_variation_trans . postfix ,
product_modification . id ,
product_modification . const ,
product_modification . value ,
category_modification . reference ,
category_modification_trans . name ,
category_modification_trans . postfix ,
product_modification . article ,
product_variation . article ,
product_offer . article ,
product_info . article ,
product_modification_price . price ,
product_variation_price . price ,
product_offer_price . price ,
product_price . price ,
product_modification_price . old ,
product_variation_price . old ,
product_offer_price . old ,
product_price . old ,
product_modification_price . currency ,
product_variation_price . currency ,
product_offer_price . currency ,
product_price . currency ,
category . id ,
category_trans . name ,
category_info . url ,
category_info . minimal ,
category_info . input ,
category_info . threshold ,
category_info . step ,
category_cover . name ,
category_cover . ext ,
category_cover . cdn ,
product_invariable . id ,
project_profile_discount . value Copy
SELECT product . id , product . event , product_active . active , product_active . active_from , product_active . active_to , product_seo . title AS seo_title , product_seo . keywords AS seo_keywords , product_seo . description AS seo_description , product_trans . name AS product_name , product_desc . preview AS product_preview , product_desc . description AS product_description , product_info . url , product_offer . id as product_offer_uid , product_offer . const as product_offer_const , product_offer . value as product_offer_value , product_offer . postfix as product_offer_postfix , category_offer . reference AS product_offer_reference , category_offer_trans . name as product_offer_name , category_offer_trans . postfix as product_offer_name_postfix , product_variation . id as product_variation_uid , product_variation . const as product_variation_const , product_variation . value as product_variation_value , product_variation . postfix as product_variation_postfix , category_variation . reference as product_variation_reference , category_variation_trans . name as product_variation_name , category_variation_trans . postfix as product_variation_name_postfix , product_modification . id as product_modification_uid , product_modification . const as product_modification_const , product_modification . value as product_modification_value , product_modification . postfix as product_modification_postfix , category_modification . reference as product_modification_reference , category_modification_trans . name as product_modification_name , category_modification_trans . postfix as product_modification_name_postfix ,
COALESCE (
product_modification . article ,
product_variation . article ,
product_offer . article ,
product_info . article
) AS product_article
,
CASE
WHEN product_modification_image . ext IS NOT NULL THEN
JSON_AGG
(DISTINCT
JSONB_BUILD_OBJECT
(
'product_img_root' , product_modification_image . root ,
'product_img' , CONCAT ( '/upload/product_modification_images' , '/' , product_modification_image . name ),
'product_img_ext' , product_modification_image . ext ,
'product_img_cdn' , product_modification_image . cdn
)
)
WHEN product_variation_image . ext IS NOT NULL THEN
JSON_AGG
(DISTINCT
JSONB_BUILD_OBJECT
(
'product_img_root' , product_variation_image . root ,
'product_img' , CONCAT ( '/upload/product_variation_images' , '/' , product_variation_image . name ),
'product_img_ext' , product_variation_image . ext ,
'product_img_cdn' , product_variation_image . cdn
)
)
WHEN product_offer_images . ext IS NOT NULL THEN
JSON_AGG
(DISTINCT
JSONB_BUILD_OBJECT
(
'product_img_root' , product_offer_images . root ,
'product_img' , CONCAT ( '/upload/product_offer_images' , '/' , product_offer_images . name ),
'product_img_ext' , product_offer_images . ext ,
'product_img_cdn' , product_offer_images . cdn
)
)
WHEN product_photo . ext IS NOT NULL THEN
JSON_AGG
(DISTINCT
JSONB_BUILD_OBJECT
(
'product_img_root' , product_photo . root ,
'product_img' , CONCAT ( '/upload/product_photo' , '/' , product_photo . name ),
'product_img_ext' , product_photo . ext ,
'product_img_cdn' , product_photo . cdn
)
)
ELSE NULL
END
AS product_images ,
COALESCE (
NULLIF (product_modification_price . price , 0 ),
NULLIF (product_variation_price . price , 0 ),
NULLIF (product_offer_price . price , 0 ),
NULLIF (product_price . price , 0 ),
0
) AS product_price
,
COALESCE (
NULLIF (product_modification_price . old , 0 ),
NULLIF (product_variation_price . old , 0 ),
NULLIF (product_offer_price . old , 0 ),
NULLIF (product_price . old , 0 ),
0
) AS product_old_price
,
CASE
WHEN product_modification_price . price IS NOT NULL AND product_modification_price . price > 0
THEN product_modification_price . currency
WHEN product_variation_price . price IS NOT NULL AND product_variation_price . price > 0
THEN product_variation_price . currency
WHEN product_offer_price . price IS NOT NULL AND product_offer_price . price > 0
THEN product_offer_price . currency
WHEN product_price . price IS NOT NULL AND product_price . price > 0
THEN product_price . currency
ELSE NULL
END AS product_currency
, JSON_AGG (
DISTINCT JSONB_BUILD_OBJECT (
'value' , product_region_delivery . value ,
'day' , product_region_delivery . day
)) FILTER (WHERE product_region_delivery . day IS NOT NULL )
AS product_region_delivery , JSON_AGG (
DISTINCT JSONB_BUILD_OBJECT (
'total' , stock . total ,
'reserve' , stock . reserve
)) FILTER (WHERE stock . total > stock . reserve )
AS product_quantity_stocks , JSON_AGG (
DISTINCT JSONB_BUILD_OBJECT (
'total' , COALESCE (
product_modification_quantity . quantity ,
product_variation_quantity . quantity ,
product_offer_quantity . quantity ,
product_price . quantity ,
0
),
'reserve' , COALESCE (
product_modification_quantity . reserve ,
product_variation_quantity . reserve ,
product_offer_quantity . reserve ,
product_price . reserve ,
0
)
) )
AS product_quantity , category . id AS category_id , category_trans . name AS category_name , category_info . url AS category_url , category_info . minimal AS category_minimal , category_info . input AS category_input , category_info . threshold AS category_threshold , category_info . step AS category_step ,
CASE
WHEN category_cover . name IS NOT NULL THEN
CONCAT ( '/upload/product_category_cover' , '/' , category_cover . name )
ELSE NULL
END AS category_cover_path
, category_cover . ext AS category_cover_ext , category_cover . cdn AS category_cover_cdn , JSON_AGG
( DISTINCT
JSONB_BUILD_OBJECT
(
'0' , category_section_field . sort ,
'field_uid' , category_section_field . id ,
'field_const' , category_section_field . const ,
'field_name' , category_section_field . name ,
'field_alternative' , category_section_field . alternative ,
'field_public' , category_section_field . public ,
'field_card' , category_section_field . card ,
'field_type' , category_section_field . type ,
'field_trans' , category_section_field_trans . name ,
'field_value' , product_property . value
)
)
AS category_section_field , product_invariable . id AS product_invariable_id , project_profile_discount . value AS project_discount FROM product INNER JOIN product_active product_active ON product_active . event = product . event LEFT JOIN product_seo product_seo ON product_seo . event = product . event AND product_seo . local = 'ru' LEFT JOIN product_trans product_trans ON product_trans . event = product . event AND product_trans . local = 'ru' LEFT JOIN product_description product_desc ON product_desc . event = product . event AND product_desc . device = 'pc' LEFT JOIN product_info product_info ON product_info . product = product . id INNER JOIN product_offer product_offer ON product_offer . event = product . event AND LOWER (product_offer . value ) = '19' AND ( LOWER (product_offer . postfix ) = '111w' OR product_offer . postfix IS NULL ) LEFT JOIN product_price product_price ON product_price . event = product . event LEFT JOIN product_photo product_photo ON product_photo . event = product . event LEFT JOIN user_profile_region product_profile_region ON product_profile_region . value = '201042a6-c35d-7bc4-9cb9-ef8bc1c8711e' INNER JOIN product_categories_product product_event_category ON product_event_category . event = product . event AND product_event_category . root = true INNER JOIN users_profile project_profile ON
project_profile . id = '019469c3-700f-76a9-9b34-ccde7b4e6f49' LEFT JOIN product_category_offers category_offer ON category_offer . id = product_offer . category_offer INNER JOIN product_variation product_variation ON product_variation . offer = product_offer . id AND LOWER (product_variation . value ) = '255' AND ( LOWER (product_variation . postfix ) = '111w' OR product_variation . postfix IS NULL ) LEFT JOIN product_offer_price product_offer_price ON product_offer_price . offer = product_offer . id LEFT JOIN product_offer_images product_offer_images ON product_offer_images . offer = product_offer . id LEFT JOIN product_variation_images product_variation_image ON product_variation_image . variation = product_variation . id LEFT JOIN product_offer_quantity product_offer_quantity ON product_offer_quantity . offer = product_offer . id LEFT JOIN product_category_offers_trans category_offer_trans ON category_offer_trans . offer = category_offer . id AND category_offer_trans . local = 'ru' LEFT JOIN product_category_variation category_variation ON category_variation . id = product_variation . category_variation INNER JOIN product_modification product_modification ON product_modification . variation = product_variation . id AND LOWER (product_modification . value ) = '55' AND ( LOWER (product_modification . postfix ) = '111w' OR product_modification . postfix IS NULL ) LEFT JOIN product_variation_price product_variation_price ON product_variation_price . variation = product_variation . id LEFT JOIN product_category_variation_trans category_variation_trans ON category_variation_trans . variation = category_variation . id AND category_variation_trans . local = 'ru' LEFT JOIN product_variation_quantity product_variation_quantity ON product_variation_quantity . variation = product_variation . id LEFT JOIN product_category_modification category_modification ON category_modification . id = product_modification . category_modification LEFT JOIN product_modification_price product_modification_price ON product_modification_price . modification = product_modification . id LEFT JOIN product_modification_images product_modification_image ON product_modification_image . modification = product_modification . id LEFT JOIN product_invariable product_invariable ON
product_invariable . product = product . id AND
(
(product_offer . const IS NOT NULL AND product_invariable . offer = product_offer . const ) OR
(product_offer . const IS NULL AND product_invariable . offer IS NULL )
)
AND
(
(product_variation . const IS NOT NULL AND product_invariable . variation = product_variation . const ) OR
(product_variation . const IS NULL AND product_invariable . variation IS NULL )
)
AND
(
(product_modification . const IS NOT NULL AND product_invariable . modification = product_modification . const ) OR
(product_modification . const IS NULL AND product_invariable . modification IS NULL )
)
LEFT JOIN product_category_modification_trans category_modification_trans ON category_modification_trans . modification = category_modification . id AND category_modification_trans . local = 'ru' LEFT JOIN product_modification_quantity product_modification_quantity ON product_modification_quantity . modification = product_modification . id INNER JOIN users_profile product_region_total ON product_region_total . event = product_profile_region . event LEFT JOIN user_profile_delivery product_region_delivery ON product_region_delivery . event = product_profile_region . event LEFT JOIN product_stock_total stock ON
stock . profile = product_region_total . id AND
stock . product = product . id
AND
CASE
WHEN product_offer . const IS NOT NULL
THEN stock . offer = product_offer . const
ELSE stock . offer IS NULL
END
AND
CASE
WHEN product_variation . const IS NOT NULL
THEN stock . variation = product_variation . const
ELSE stock . variation IS NULL
END
AND
CASE
WHEN product_modification . const IS NOT NULL
THEN stock . modification = product_modification . const
ELSE stock . modification IS NULL
END
INNER JOIN product_category category ON category . id = product_event_category . category LEFT JOIN product_category_trans category_trans ON category_trans . event = category . event AND category_trans . local = 'ru' LEFT JOIN product_category_info category_info ON category_info . event = category . event LEFT JOIN product_category_section category_section ON category_section . event = category . event LEFT JOIN product_category_cover category_cover ON category_cover . event = category . event LEFT JOIN product_category_section_field category_section_field ON category_section_field . section = category_section . id LEFT JOIN product_category_section_field_trans category_section_field_trans ON category_section_field_trans . field = category_section_field . id
AND category_section_field_trans . local = 'ru' LEFT JOIN product_property product_property ON product_property . event = product . event AND product_property . field = category_section_field . const LEFT JOIN user_profile_discount project_profile_discount ON
project_profile_discount . event = project_profile . event WHERE product . id = '018954cb-0a6e-744a-97f0-128e7f05d76d' GROUP BY product_photo . ext , product_offer_images . ext , product_variation_image . ext , product_modification_image . ext , product . id , product . event , product_active . active , product_active . active_from , product_active . active_to , product_seo . title , product_seo . keywords , product_seo . description , product_trans . name , product_desc . preview , product_desc . description , product_info . url , product_offer . id , product_offer . const , product_offer . value , product_offer . postfix , category_offer . reference , category_offer_trans . name , category_offer_trans . postfix , product_variation . id , product_variation . const , product_variation . value , product_variation . postfix , category_variation . reference , category_variation_trans . name , category_variation_trans . postfix , product_modification . id , product_modification . const , product_modification . value , category_modification . reference , category_modification_trans . name , category_modification_trans . postfix , product_modification . article , product_variation . article , product_offer . article , product_info . article , product_modification_price . price , product_variation_price . price , product_offer_price . price , product_price . price , product_modification_price . old , product_variation_price . old , product_offer_price . old , product_price . old , product_modification_price . currency , product_variation_price . currency , product_offer_price . currency , product_price . currency , category . id , category_trans . name , category_info . url , category_info . minimal , category_info . input , category_info . threshold , category_info . step , category_cover . name , category_cover . ext , category_cover . cdn , product_invariable . id , project_profile_discount . value ; Copy
5
399.77 ms
SELECT product_offer . value as product_offer_value , product_offer . postfix as product_offer_postfix , product_offer . id as product_offer_uid , product . id , product . event , product_variation . value as product_variation_value , product_variation . postfix as product_variation_postfix , product_variation . id as product_variation_uid , product_modification . value as product_modification_value , product_modification . postfix as product_modification_postfix , product_modification . id as product_modification_uid , product_active . active_from , product_trans . name AS product_name , product_info . url AS product_url ,
COALESCE (
product_modification . article ,
product_variation . article ,
product_offer . article ,
product_info . article
) AS article
, category_offer . reference AS product_offer_reference , category_offer_trans . name as product_offer_name , category_offer_variation . reference as product_variation_reference , category_offer_variation_trans . name as product_variation_name , category_offer_modification . reference as product_modification_reference , category_offer_modification_trans . name as product_modification_name ,
CASE
WHEN product_modification_price . price IS NOT NULL AND product_modification_price . price > 0
THEN product_modification_price . price
WHEN product_variation_price . price IS NOT NULL AND product_variation_price . price > 0
THEN product_variation_price . price
WHEN product_offer_price . price IS NOT NULL AND product_offer_price . price > 0
THEN product_offer_price . price
WHEN product_price . price IS NOT NULL AND product_price . price > 0
THEN product_price . price
ELSE NULL
END AS product_price
,
COALESCE (
NULLIF (product_modification_price . old , 0 ),
NULLIF (product_variation_price . old , 0 ),
NULLIF (product_offer_price . old , 0 ),
NULLIF (product_price . old , 0 ),
0
) AS product_old_price
,
CASE
WHEN product_modification_price . price IS NOT NULL AND product_modification_price . price > 0
THEN product_modification_price . currency
WHEN product_variation_price . price IS NOT NULL AND product_variation_price . price > 0
THEN product_variation_price . currency
WHEN product_offer_price . price IS NOT NULL AND product_offer_price . price > 0
THEN product_offer_price . currency
WHEN product_price . price IS NOT NULL AND product_price . price > 0
THEN product_price . currency
ELSE NULL
END AS product_currency
,
CASE
WHEN product_modification_quantity . quantity > 0 AND product_modification_quantity . quantity > product_modification_quantity . reserve
THEN (product_modification_quantity . quantity - product_modification_quantity . reserve )
WHEN product_variation_quantity . quantity > 0 AND product_variation_quantity . quantity > product_variation_quantity . reserve
THEN (product_variation_quantity . quantity - product_variation_quantity . reserve )
WHEN product_offer_quantity . quantity > 0 AND product_offer_quantity . quantity > product_offer_quantity . reserve
THEN (product_offer_quantity . quantity - product_offer_quantity . reserve )
WHEN product_price . quantity > 0 AND product_price . quantity > product_price . reserve
THEN (product_price . quantity - product_price . reserve )
ELSE 0
END AS quantity
, category_trans . name AS category_name , category_info . url AS category_url , category_info . threshold AS category_threshold , JSON_AGG
( DISTINCT
JSONB_BUILD_OBJECT
(
'0' , category_section_field . sort ,
'field_type' , category_section_field . type ,
'field_trans' , category_section_field_trans . name ,
'field_value' , category_product_property . value
)
)
AS category_section_field , JSON_AGG
(DISTINCT
CASE
WHEN product_offer_images . ext IS NOT NULL
THEN JSONB_BUILD_OBJECT
(
'img_root' , product_offer_images . root ,
'img' , CONCAT ( '/upload/product_offer_images' , '/' , product_offer_images . name ),
'img_ext' , product_offer_images . ext ,
'img_cdn' , product_offer_images . cdn
)
WHEN product_variation_image . ext IS NOT NULL
THEN JSONB_BUILD_OBJECT
(
'img_root' , product_variation_image . root ,
'img' , CONCAT ( '/upload/product_variation_images' , '/' , product_variation_image . name ),
'img_ext' , product_variation_image . ext ,
'img_cdn' , product_variation_image . cdn
)
WHEN product_offer_modification_image . ext IS NOT NULL
THEN JSONB_BUILD_OBJECT
(
'img_root' , product_offer_modification_image . root ,
'img' , CONCAT ( '/upload/product_modification_images' , '/' , product_offer_modification_image . name ),
'img_ext' , product_offer_modification_image . ext ,
'img_cdn' , product_offer_modification_image . cdn
)
WHEN product_photo . ext IS NOT NULL
THEN JSONB_BUILD_OBJECT
(
'img_root' , product_photo . root ,
'img' , CONCAT ( '/upload/product_photo' , '/' , product_photo . name ),
'img_ext' , product_photo . ext ,
'img_cdn' , product_photo . cdn
)
END ) AS product_images , product_invariable . id AS product_invariable_id , project_profile_discount . value AS project_discount FROM product_offer INNER JOIN product product ON product . event = product_offer . event INNER JOIN product_variation product_variation ON product_variation . offer = product_offer . id AND product_variation . value = ? LEFT JOIN product_category_offers category_offer ON category_offer . id = product_offer . category_offer LEFT JOIN product_offer_price product_offer_price ON product_offer_price . offer = product_offer . id LEFT JOIN product_offer_quantity product_offer_quantity ON product_offer_quantity . offer = product_offer . id INNER JOIN product_property product_property_cbab47f9be78ee8c1420d7939dac3ad5 ON product_property_cbab47f9be78ee8c1420d7939dac3ad5 . event = product_offer . event AND product_property_cbab47f9be78ee8c1420d7939dac3ad5 . value = ? LEFT JOIN product_variation_images product_variation_image ON product_variation_image . variation = product_variation . id LEFT JOIN product_offer_images product_offer_images ON product_offer_images . offer = product_offer . id INNER JOIN product_active product_active ON product_active . event = product . event AND
product_active . active = true AND
product_active . active_from < NOW ()
AND (
CASE
WHEN product_active . active_to IS NOT NULL
THEN product_active . active_to > NOW ()
ELSE TRUE
END
)
LEFT JOIN product_trans product_trans ON product_trans . event = product . event AND product_trans . local = ? LEFT JOIN product_info product_info ON product_info . product = product . id LEFT JOIN product_price product_price ON product_price . event = product . event LEFT JOIN product_categories_product product_event_category ON product_event_category . event = product . event AND product_event_category . root = true LEFT JOIN product_photo product_photo ON product_photo . event = product . event INNER JOIN users_profile project_profile ON
project_profile . id = ? LEFT JOIN product_category category ON category . id = product_event_category . category LEFT JOIN product_category_trans category_trans ON category_trans . event = category . event AND category_trans . local = ? INNER JOIN product_category_info category_info ON category_info . event = category . event AND category_info . active IS TRUE LEFT JOIN product_category_section category_section ON category_section . event = category . event LEFT JOIN product_category_section_field category_section_field ON category_section_field . section = category_section . id AND category_section_field . card = TRUE LEFT JOIN product_category_section_field_trans category_section_field_trans ON category_section_field_trans . field = category_section_field . id AND category_section_field_trans . local = ? LEFT JOIN product_property category_product_property ON category_product_property . event = product . event AND category_product_property . field = category_section_field . const LEFT JOIN user_profile_discount project_profile_discount ON
project_profile_discount . event = project_profile . event INNER JOIN product_modification product_modification ON product_modification . variation = product_variation . id AND product_modification . value = ? LEFT JOIN product_category_variation category_offer_variation ON category_offer_variation . id = product_variation . category_variation LEFT JOIN product_variation_price product_variation_price ON product_variation_price . variation = product_variation . id LEFT JOIN product_category_modification category_offer_modification ON category_offer_modification . id = product_modification . category_modification LEFT JOIN product_modification_price product_modification_price ON product_modification_price . modification = product_modification . id LEFT JOIN product_modification_images product_offer_modification_image ON product_offer_modification_image . modification = product_modification . id LEFT JOIN product_invariable product_invariable ON
product_invariable . product = product . id AND
(
(product_offer . const IS NOT NULL AND product_invariable . offer = product_offer . const ) OR
(product_offer . const IS NULL AND product_invariable . offer IS NULL )
)
AND
(
(product_variation . const IS NOT NULL AND product_invariable . variation = product_variation . const ) OR
(product_variation . const IS NULL AND product_invariable . variation IS NULL )
)
AND
(
(product_modification . const IS NOT NULL AND product_invariable . modification = product_modification . const ) OR
(product_modification . const IS NULL AND product_invariable . modification IS NULL )
)
LEFT JOIN product_category_modification_trans category_offer_modification_trans ON category_offer_modification_trans . modification = category_offer_modification . id AND category_offer_modification_trans . local = ? LEFT JOIN product_modification_quantity product_modification_quantity ON product_modification_quantity . modification = product_modification . id LEFT JOIN product_category_variation_trans category_offer_variation_trans ON category_offer_variation_trans . variation = category_offer_variation . id AND category_offer_variation_trans . local = ? LEFT JOIN product_variation_quantity product_variation_quantity ON product_variation_quantity . variation = product_variation . id LEFT JOIN product_category_offers_trans category_offer_trans ON category_offer_trans . offer = category_offer . id AND category_offer_trans . local = ? WHERE product_offer . value = ? GROUP BY product_offer . value , product_offer . postfix , product_offer . id , product . id , product . event , product_variation . value , product_variation . postfix , product_variation . id , product_modification . value , product_modification . postfix , product_modification . id , product_active . active_from , product_trans . name , product_info . url , product_modification . article , product_variation . article , product_offer . article , product_info . article , category_offer . reference , category_offer_trans . name , category_offer_variation . reference , category_offer_variation_trans . name , category_offer_modification . reference , category_offer_modification_trans . name , product_modification_price . price , product_variation_price . price , product_offer_price . price , product_price . price , product_modification_price . old , product_variation_price . old , product_offer_price . old , product_price . old , product_modification_price . currency , product_variation_price . currency , product_offer_price . currency , product_price . currency , product_modification_quantity . quantity , product_modification_quantity . reserve , product_variation_quantity . quantity , product_variation_quantity . reserve , product_offer_quantity . quantity , product_offer_quantity . reserve , product_price . quantity , product_price . reserve , category_trans . name , category_info . url , category_info . threshold , product_invariable . id , project_profile_discount . value , product_info . sort ORDER BY product_info . sort DESC , SUM (product_modification_quantity . reserve ) DESC , SUM (product_variation_quantity . reserve ) DESC , SUM (product_offer_quantity . reserve ) DESC , SUM (product_price . reserve ) DESC , SUM (product_modification_quantity . quantity ) DESC , SUM (product_variation_quantity . quantity ) DESC , SUM (product_offer_quantity . quantity ) DESC , SUM (product_price . quantity ) DESC LIMIT 100
Parameters :
[
"255 "
"summer "
"ru "
"019469c3-700f-76a9-9b34-ccde7b4e6f49 "
"ru "
"ru "
"55 "
"ru "
"ru "
"ru "
"19 "
]
SELECT
product_offer . value as product_offer_value ,
product_offer . postfix as product_offer_postfix ,
product_offer . id as product_offer_uid ,
product . id ,
product . event ,
product_variation . value as product_variation_value ,
product_variation . postfix as product_variation_postfix ,
product_variation . id as product_variation_uid ,
product_modification . value as product_modification_value ,
product_modification . postfix as product_modification_postfix ,
product_modification . id as product_modification_uid ,
product_active . active_from ,
product_trans . name AS product_name ,
product_info . url AS product_url ,
COALESCE (
product_modification . article , product_variation . article ,
product_offer . article , product_info . article
) AS article ,
category_offer . reference AS product_offer_reference ,
category_offer_trans . name as product_offer_name ,
category_offer_variation . reference as product_variation_reference ,
category_offer_variation_trans . name as product_variation_name ,
category_offer_modification . reference as product_modification_reference ,
category_offer_modification_trans . name as product_modification_name ,
CASE WHEN product_modification_price . price IS NOT NULL
AND product_modification_price . price > 0 THEN
product_modification_price . price
WHEN product_variation_price . price IS NOT NULL
AND product_variation_price . price > 0 THEN
product_variation_price . price
WHEN product_offer_price . price IS NOT NULL
AND product_offer_price . price > 0 THEN
product_offer_price . price
WHEN product_price . price IS NOT NULL
AND product_price . price > 0 THEN
product_price . price
ELSE
NULL
END AS product_price ,
COALESCE (
NULLIF (
product_modification_price . old ,
0
),
NULLIF (product_variation_price . old , 0 ),
NULLIF (product_offer_price . old , 0 ),
NULLIF (product_price . old , 0 ),
0
) AS product_old_price ,
CASE WHEN product_modification_price . price IS NOT NULL
AND product_modification_price . price > 0 THEN
product_modification_price . currency
WHEN product_variation_price . price IS NOT NULL
AND product_variation_price . price > 0 THEN
product_variation_price . currency
WHEN product_offer_price . price IS NOT NULL
AND product_offer_price . price > 0 THEN
product_offer_price . currency
WHEN product_price . price IS NOT NULL
AND product_price . price > 0 THEN
product_price . currency
ELSE
NULL
END AS product_currency ,
CASE WHEN product_modification_quantity . quantity > 0
AND product_modification_quantity . quantity > product_modification_quantity . reserve THEN
(
product_modification_quantity . quantity - product_modification_quantity . reserve
)
WHEN product_variation_quantity . quantity > 0
AND product_variation_quantity . quantity > product_variation_quantity . reserve THEN
(
product_variation_quantity . quantity - product_variation_quantity . reserve
)
WHEN product_offer_quantity . quantity > 0
AND product_offer_quantity . quantity > product_offer_quantity . reserve THEN
(
product_offer_quantity . quantity - product_offer_quantity . reserve
)
WHEN product_price . quantity > 0
AND product_price . quantity > product_price . reserve THEN
(
product_price . quantity - product_price . reserve
)
ELSE
0
END AS quantity ,
category_trans . name AS category_name ,
category_info . url AS category_url ,
category_info . threshold AS category_threshold ,
JSON_AGG (
DISTINCT JSONB_BUILD_OBJECT (
'0' , category_section_field . sort ,
'field_type' , category_section_field . type ,
'field_trans' , category_section_field_trans . name ,
'field_value' , category_product_property . value
)
) AS category_section_field ,
JSON_AGG (
DISTINCT CASE WHEN product_offer_images . ext IS NOT NULL THEN
JSONB_BUILD_OBJECT (
'img_root' ,
product_offer_images . root ,
'img' ,
CONCAT (
'/upload/product_offer_images' ,
'/' , product_offer_images . name
),
'img_ext' ,
product_offer_images . ext ,
'img_cdn' ,
product_offer_images . cdn
)
WHEN product_variation_image . ext IS NOT NULL THEN
JSONB_BUILD_OBJECT (
'img_root' ,
product_variation_image . root ,
'img' ,
CONCAT (
'/upload/product_variation_images' ,
'/' , product_variation_image . name
),
'img_ext' ,
product_variation_image . ext ,
'img_cdn' ,
product_variation_image . cdn
)
WHEN product_offer_modification_image . ext IS NOT NULL THEN
JSONB_BUILD_OBJECT (
'img_root' ,
product_offer_modification_image . root ,
'img' ,
CONCAT (
'/upload/product_modification_images' ,
'/' , product_offer_modification_image . name
),
'img_ext' ,
product_offer_modification_image . ext ,
'img_cdn' ,
product_offer_modification_image . cdn
)
WHEN product_photo . ext IS NOT NULL THEN
JSONB_BUILD_OBJECT (
'img_root' ,
product_photo . root ,
'img' ,
CONCAT (
'/upload/product_photo' , '/' , product_photo . name
),
'img_ext' ,
product_photo . ext ,
'img_cdn' ,
product_photo . cdn
)
END
) AS product_images ,
product_invariable . id AS product_invariable_id ,
project_profile_discount . value AS project_discount
FROM
product_offer
INNER JOIN product product ON product . event = product_offer . event
INNER JOIN product_variation product_variation ON product_variation . offer = product_offer . id
AND product_variation . value = ?
LEFT JOIN product_category_offers category_offer ON category_offer . id = product_offer . category_offer
LEFT JOIN product_offer_price product_offer_price ON product_offer_price . offer = product_offer . id
LEFT JOIN product_offer_quantity product_offer_quantity ON product_offer_quantity . offer = product_offer . id
INNER JOIN product_property product_property_cbab47f9be78ee8c1420d7939dac3ad5 ON product_property_cbab47f9be78ee8c1420d7939dac3ad5 . event = product_offer . event
AND product_property_cbab47f9be78ee8c1420d7939dac3ad5 . value = ?
LEFT JOIN product_variation_images product_variation_image ON product_variation_image . variation = product_variation . id
LEFT JOIN product_offer_images product_offer_images ON product_offer_images . offer = product_offer . id
INNER JOIN product_active product_active ON product_active . event = product . event
AND product_active . active = true
AND product_active . active_from < NOW ()
AND (
CASE WHEN product_active . active_to IS NOT NULL THEN
product_active . active_to > NOW ()
ELSE
TRUE
END
)
LEFT JOIN product_trans product_trans ON product_trans . event = product . event
AND product_trans . local = ?
LEFT JOIN product_info product_info ON product_info . product = product . id
LEFT JOIN product_price product_price ON product_price . event = product . event
LEFT JOIN product_categories_product product_event_category ON product_event_category . event = product . event
AND product_event_category . root = true
LEFT JOIN product_photo product_photo ON product_photo . event = product . event
INNER JOIN users_profile project_profile ON project_profile . id = ?
LEFT JOIN product_category category ON category . id = product_event_category . category
LEFT JOIN product_category_trans category_trans ON category_trans . event = category . event
AND category_trans . local = ?
INNER JOIN product_category_info category_info ON category_info . event = category . event
AND category_info . active IS TRUE
LEFT JOIN product_category_section category_section ON category_section . event = category . event
LEFT JOIN product_category_section_field category_section_field ON category_section_field . section = category_section . id
AND category_section_field . card = TRUE
LEFT JOIN product_category_section_field_trans category_section_field_trans ON category_section_field_trans . field = category_section_field . id
AND category_section_field_trans . local = ?
LEFT JOIN product_property category_product_property ON category_product_property . event = product . event
AND category_product_property . field = category_section_field . const
LEFT JOIN user_profile_discount project_profile_discount ON project_profile_discount . event = project_profile . event
INNER JOIN product_modification product_modification ON product_modification . variation = product_variation . id
AND product_modification . value = ?
LEFT JOIN product_category_variation category_offer_variation ON category_offer_variation . id = product_variation . category_variation
LEFT JOIN product_variation_price product_variation_price ON product_variation_price . variation = product_variation . id
LEFT JOIN product_category_modification category_offer_modification ON category_offer_modification . id = product_modification . category_modification
LEFT JOIN product_modification_price product_modification_price ON product_modification_price . modification = product_modification . id
LEFT JOIN product_modification_images product_offer_modification_image ON product_offer_modification_image . modification = product_modification . id
LEFT JOIN product_invariable product_invariable ON product_invariable . product = product . id
AND (
(
product_offer . const IS NOT NULL
AND product_invariable . offer = product_offer . const
)
OR (
product_offer . const IS NULL
AND product_invariable . offer IS NULL
)
)
AND (
(
product_variation . const IS NOT NULL
AND product_invariable . variation = product_variation . const
)
OR (
product_variation . const IS NULL
AND product_invariable . variation IS NULL
)
)
AND (
(
product_modification . const IS NOT NULL
AND product_invariable . modification = product_modification . const
)
OR (
product_modification . const IS NULL
AND product_invariable . modification IS NULL
)
)
LEFT JOIN product_category_modification_trans category_offer_modification_trans ON category_offer_modification_trans . modification = category_offer_modification . id
AND category_offer_modification_trans . local = ?
LEFT JOIN product_modification_quantity product_modification_quantity ON product_modification_quantity . modification = product_modification . id
LEFT JOIN product_category_variation_trans category_offer_variation_trans ON category_offer_variation_trans . variation = category_offer_variation . id
AND category_offer_variation_trans . local = ?
LEFT JOIN product_variation_quantity product_variation_quantity ON product_variation_quantity . variation = product_variation . id
LEFT JOIN product_category_offers_trans category_offer_trans ON category_offer_trans . offer = category_offer . id
AND category_offer_trans . local = ?
WHERE
product_offer . value = ?
GROUP BY
product_offer . value ,
product_offer . postfix ,
product_offer . id ,
product . id ,
product . event ,
product_variation . value ,
product_variation . postfix ,
product_variation . id ,
product_modification . value ,
product_modification . postfix ,
product_modification . id ,
product_active . active_from ,
product_trans . name ,
product_info . url ,
product_modification . article ,
product_variation . article ,
product_offer . article ,
product_info . article ,
category_offer . reference ,
category_offer_trans . name ,
category_offer_variation . reference ,
category_offer_variation_trans . name ,
category_offer_modification . reference ,
category_offer_modification_trans . name ,
product_modification_price . price ,
product_variation_price . price ,
product_offer_price . price ,
product_price . price ,
product_modification_price . old ,
product_variation_price . old ,
product_offer_price . old ,
product_price . old ,
product_modification_price . currency ,
product_variation_price . currency ,
product_offer_price . currency ,
product_price . currency ,
product_modification_quantity . quantity ,
product_modification_quantity . reserve ,
product_variation_quantity . quantity ,
product_variation_quantity . reserve ,
product_offer_quantity . quantity ,
product_offer_quantity . reserve ,
product_price . quantity ,
product_price . reserve ,
category_trans . name ,
category_info . url ,
category_info . threshold ,
product_invariable . id ,
project_profile_discount . value ,
product_info . sort
ORDER BY
product_info . sort DESC ,
SUM (
product_modification_quantity . reserve
) DESC ,
SUM (
product_variation_quantity . reserve
) DESC ,
SUM (product_offer_quantity . reserve ) DESC ,
SUM (product_price . reserve ) DESC ,
SUM (
product_modification_quantity . quantity
) DESC ,
SUM (
product_variation_quantity . quantity
) DESC ,
SUM (
product_offer_quantity . quantity
) DESC ,
SUM (product_price . quantity ) DESC
LIMIT
100 Copy
SELECT product_offer . value as product_offer_value , product_offer . postfix as product_offer_postfix , product_offer . id as product_offer_uid , product . id , product . event , product_variation . value as product_variation_value , product_variation . postfix as product_variation_postfix , product_variation . id as product_variation_uid , product_modification . value as product_modification_value , product_modification . postfix as product_modification_postfix , product_modification . id as product_modification_uid , product_active . active_from , product_trans . name AS product_name , product_info . url AS product_url ,
COALESCE (
product_modification . article ,
product_variation . article ,
product_offer . article ,
product_info . article
) AS article
, category_offer . reference AS product_offer_reference , category_offer_trans . name as product_offer_name , category_offer_variation . reference as product_variation_reference , category_offer_variation_trans . name as product_variation_name , category_offer_modification . reference as product_modification_reference , category_offer_modification_trans . name as product_modification_name ,
CASE
WHEN product_modification_price . price IS NOT NULL AND product_modification_price . price > 0
THEN product_modification_price . price
WHEN product_variation_price . price IS NOT NULL AND product_variation_price . price > 0
THEN product_variation_price . price
WHEN product_offer_price . price IS NOT NULL AND product_offer_price . price > 0
THEN product_offer_price . price
WHEN product_price . price IS NOT NULL AND product_price . price > 0
THEN product_price . price
ELSE NULL
END AS product_price
,
COALESCE (
NULLIF (product_modification_price . old , 0 ),
NULLIF (product_variation_price . old , 0 ),
NULLIF (product_offer_price . old , 0 ),
NULLIF (product_price . old , 0 ),
0
) AS product_old_price
,
CASE
WHEN product_modification_price . price IS NOT NULL AND product_modification_price . price > 0
THEN product_modification_price . currency
WHEN product_variation_price . price IS NOT NULL AND product_variation_price . price > 0
THEN product_variation_price . currency
WHEN product_offer_price . price IS NOT NULL AND product_offer_price . price > 0
THEN product_offer_price . currency
WHEN product_price . price IS NOT NULL AND product_price . price > 0
THEN product_price . currency
ELSE NULL
END AS product_currency
,
CASE
WHEN product_modification_quantity . quantity > 0 AND product_modification_quantity . quantity > product_modification_quantity . reserve
THEN (product_modification_quantity . quantity - product_modification_quantity . reserve )
WHEN product_variation_quantity . quantity > 0 AND product_variation_quantity . quantity > product_variation_quantity . reserve
THEN (product_variation_quantity . quantity - product_variation_quantity . reserve )
WHEN product_offer_quantity . quantity > 0 AND product_offer_quantity . quantity > product_offer_quantity . reserve
THEN (product_offer_quantity . quantity - product_offer_quantity . reserve )
WHEN product_price . quantity > 0 AND product_price . quantity > product_price . reserve
THEN (product_price . quantity - product_price . reserve )
ELSE 0
END AS quantity
, category_trans . name AS category_name , category_info . url AS category_url , category_info . threshold AS category_threshold , JSON_AGG
( DISTINCT
JSONB_BUILD_OBJECT
(
'0' , category_section_field . sort ,
'field_type' , category_section_field . type ,
'field_trans' , category_section_field_trans . name ,
'field_value' , category_product_property . value
)
)
AS category_section_field , JSON_AGG
(DISTINCT
CASE
WHEN product_offer_images . ext IS NOT NULL
THEN JSONB_BUILD_OBJECT
(
'img_root' , product_offer_images . root ,
'img' , CONCAT ( '/upload/product_offer_images' , '/' , product_offer_images . name ),
'img_ext' , product_offer_images . ext ,
'img_cdn' , product_offer_images . cdn
)
WHEN product_variation_image . ext IS NOT NULL
THEN JSONB_BUILD_OBJECT
(
'img_root' , product_variation_image . root ,
'img' , CONCAT ( '/upload/product_variation_images' , '/' , product_variation_image . name ),
'img_ext' , product_variation_image . ext ,
'img_cdn' , product_variation_image . cdn
)
WHEN product_offer_modification_image . ext IS NOT NULL
THEN JSONB_BUILD_OBJECT
(
'img_root' , product_offer_modification_image . root ,
'img' , CONCAT ( '/upload/product_modification_images' , '/' , product_offer_modification_image . name ),
'img_ext' , product_offer_modification_image . ext ,
'img_cdn' , product_offer_modification_image . cdn
)
WHEN product_photo . ext IS NOT NULL
THEN JSONB_BUILD_OBJECT
(
'img_root' , product_photo . root ,
'img' , CONCAT ( '/upload/product_photo' , '/' , product_photo . name ),
'img_ext' , product_photo . ext ,
'img_cdn' , product_photo . cdn
)
END ) AS product_images , product_invariable . id AS product_invariable_id , project_profile_discount . value AS project_discount FROM product_offer INNER JOIN product product ON product . event = product_offer . event INNER JOIN product_variation product_variation ON product_variation . offer = product_offer . id AND product_variation . value = '255' LEFT JOIN product_category_offers category_offer ON category_offer . id = product_offer . category_offer LEFT JOIN product_offer_price product_offer_price ON product_offer_price . offer = product_offer . id LEFT JOIN product_offer_quantity product_offer_quantity ON product_offer_quantity . offer = product_offer . id INNER JOIN product_property product_property_cbab47f9be78ee8c1420d7939dac3ad5 ON product_property_cbab47f9be78ee8c1420d7939dac3ad5 . event = product_offer . event AND product_property_cbab47f9be78ee8c1420d7939dac3ad5 . value = 'summer' LEFT JOIN product_variation_images product_variation_image ON product_variation_image . variation = product_variation . id LEFT JOIN product_offer_images product_offer_images ON product_offer_images . offer = product_offer . id INNER JOIN product_active product_active ON product_active . event = product . event AND
product_active . active = true AND
product_active . active_from < NOW ()
AND (
CASE
WHEN product_active . active_to IS NOT NULL
THEN product_active . active_to > NOW ()
ELSE TRUE
END
)
LEFT JOIN product_trans product_trans ON product_trans . event = product . event AND product_trans . local = 'ru' LEFT JOIN product_info product_info ON product_info . product = product . id LEFT JOIN product_price product_price ON product_price . event = product . event LEFT JOIN product_categories_product product_event_category ON product_event_category . event = product . event AND product_event_category . root = true LEFT JOIN product_photo product_photo ON product_photo . event = product . event INNER JOIN users_profile project_profile ON
project_profile . id = '019469c3-700f-76a9-9b34-ccde7b4e6f49' LEFT JOIN product_category category ON category . id = product_event_category . category LEFT JOIN product_category_trans category_trans ON category_trans . event = category . event AND category_trans . local = 'ru' INNER JOIN product_category_info category_info ON category_info . event = category . event AND category_info . active IS TRUE LEFT JOIN product_category_section category_section ON category_section . event = category . event LEFT JOIN product_category_section_field category_section_field ON category_section_field . section = category_section . id AND category_section_field . card = TRUE LEFT JOIN product_category_section_field_trans category_section_field_trans ON category_section_field_trans . field = category_section_field . id AND category_section_field_trans . local = 'ru' LEFT JOIN product_property category_product_property ON category_product_property . event = product . event AND category_product_property . field = category_section_field . const LEFT JOIN user_profile_discount project_profile_discount ON
project_profile_discount . event = project_profile . event INNER JOIN product_modification product_modification ON product_modification . variation = product_variation . id AND product_modification . value = '55' LEFT JOIN product_category_variation category_offer_variation ON category_offer_variation . id = product_variation . category_variation LEFT JOIN product_variation_price product_variation_price ON product_variation_price . variation = product_variation . id LEFT JOIN product_category_modification category_offer_modification ON category_offer_modification . id = product_modification . category_modification LEFT JOIN product_modification_price product_modification_price ON product_modification_price . modification = product_modification . id LEFT JOIN product_modification_images product_offer_modification_image ON product_offer_modification_image . modification = product_modification . id LEFT JOIN product_invariable product_invariable ON
product_invariable . product = product . id AND
(
(product_offer . const IS NOT NULL AND product_invariable . offer = product_offer . const ) OR
(product_offer . const IS NULL AND product_invariable . offer IS NULL )
)
AND
(
(product_variation . const IS NOT NULL AND product_invariable . variation = product_variation . const ) OR
(product_variation . const IS NULL AND product_invariable . variation IS NULL )
)
AND
(
(product_modification . const IS NOT NULL AND product_invariable . modification = product_modification . const ) OR
(product_modification . const IS NULL AND product_invariable . modification IS NULL )
)
LEFT JOIN product_category_modification_trans category_offer_modification_trans ON category_offer_modification_trans . modification = category_offer_modification . id AND category_offer_modification_trans . local = 'ru' LEFT JOIN product_modification_quantity product_modification_quantity ON product_modification_quantity . modification = product_modification . id LEFT JOIN product_category_variation_trans category_offer_variation_trans ON category_offer_variation_trans . variation = category_offer_variation . id AND category_offer_variation_trans . local = 'ru' LEFT JOIN product_variation_quantity product_variation_quantity ON product_variation_quantity . variation = product_variation . id LEFT JOIN product_category_offers_trans category_offer_trans ON category_offer_trans . offer = category_offer . id AND category_offer_trans . local = 'ru' WHERE product_offer . value = '19' GROUP BY product_offer . value , product_offer . postfix , product_offer . id , product . id , product . event , product_variation . value , product_variation . postfix , product_variation . id , product_modification . value , product_modification . postfix , product_modification . id , product_active . active_from , product_trans . name , product_info . url , product_modification . article , product_variation . article , product_offer . article , product_info . article , category_offer . reference , category_offer_trans . name , category_offer_variation . reference , category_offer_variation_trans . name , category_offer_modification . reference , category_offer_modification_trans . name , product_modification_price . price , product_variation_price . price , product_offer_price . price , product_price . price , product_modification_price . old , product_variation_price . old , product_offer_price . old , product_price . old , product_modification_price . currency , product_variation_price . currency , product_offer_price . currency , product_price . currency , product_modification_quantity . quantity , product_modification_quantity . reserve , product_variation_quantity . quantity , product_variation_quantity . reserve , product_offer_quantity . quantity , product_offer_quantity . reserve , product_price . quantity , product_price . reserve , category_trans . name , category_info . url , category_info . threshold , product_invariable . id , project_profile_discount . value , product_info . sort ORDER BY product_info . sort DESC , SUM (product_modification_quantity . reserve ) DESC , SUM (product_variation_quantity . reserve ) DESC , SUM (product_offer_quantity . reserve ) DESC , SUM (product_price . reserve ) DESC , SUM (product_modification_quantity . quantity ) DESC , SUM (product_variation_quantity . quantity ) DESC , SUM (product_offer_quantity . quantity ) DESC , SUM (product_price . quantity ) DESC LIMIT 100 ; Copy
6
100.26 ms
SELECT product_trans . name AS product_name , product_info . url , product_info . sort , product_offer . id as product_offer_uid , product_offer . value as product_offer_value , product_offer . postfix as product_offer_postfix , category_offer . reference as product_offer_reference , product_variation . id as product_variation_uid , product_variation . value as product_variation_value , product_variation . postfix as product_variation_postfix , category_offer_variation . reference as product_variation_reference , product_modification . id as product_modification_uid , product_modification . value as product_modification_value , product_modification . postfix as product_modification_postfix , category_offer_modification . reference as product_modification_reference , category . id as category_id , category_info . url AS category_url ,
COALESCE (
NULLIF (product_modification_price . price , 0 ),
NULLIF (product_variation_price . price , 0 ),
NULLIF (product_offer_price . price , 0 ),
NULLIF (product_price . price , 0 ),
0
) AS product_price
,
COALESCE (
NULLIF (product_modification_price . old , 0 ),
NULLIF (product_variation_price . old , 0 ),
NULLIF (product_offer_price . old , 0 ),
NULLIF (product_price . old , 0 ),
0
) AS product_old_price
,
CASE
WHEN COALESCE (product_modification_price . price , 0 ) ! = 0
THEN product_modification_price . currency
WHEN COALESCE (product_variation_price . price , 0 ) ! = 0
THEN product_variation_price . currency
WHEN COALESCE (product_offer_price . price , 0 ) ! = 0
THEN product_offer_price . currency
WHEN COALESCE (product_price . price , 0 ) ! = 0
THEN product_price . currency
ELSE NULL
END AS product_currency , JSON_AGG
(DISTINCT
CASE
WHEN product_offer_images . ext IS NOT NULL
THEN JSONB_BUILD_OBJECT
(
'img_root' , product_offer_images . root ,
'img' , CONCAT ( '/upload/product_offer_images' , '/' , product_offer_images . name ),
'img_ext' , product_offer_images . ext ,
'img_cdn' , product_offer_images . cdn
)
WHEN product_variation_image . ext IS NOT NULL
THEN JSONB_BUILD_OBJECT
(
'img_root' , product_variation_image . root ,
'img' , CONCAT ( '/upload/product_variation_images' , '/' , product_variation_image . name ),
'img_ext' , product_variation_image . ext ,
'img_cdn' , product_variation_image . cdn
)
WHEN product_modification_image . ext IS NOT NULL
THEN JSONB_BUILD_OBJECT
(
'img_root' , product_modification_image . root ,
'img' , CONCAT ( '/upload/product_modification_images' , '/' , product_modification_image . name ),
'img_ext' , product_modification_image . ext ,
'img_cdn' , product_modification_image . cdn
)
WHEN product_photo . ext IS NOT NULL
THEN JSONB_BUILD_OBJECT
(
'img_root' , product_photo . root ,
'img' , CONCAT ( '/upload/product_photo' , '/' , product_photo . name ),
'img_ext' , product_photo . ext ,
'img_cdn' , product_photo . cdn
)
END ) AS product_images , product_invariable . id AS product_invariable_id , project_profile_discount . value AS project_discount FROM product LEFT JOIN product_trans product_trans ON product_trans . event = product . event AND product_trans . local = ? LEFT JOIN product_price product_price ON product_price . event = product . event LEFT JOIN product_info product_info ON product_info . product = product . id LEFT JOIN product_offer product_offer ON product_offer . event = product . event LEFT JOIN product_categories_product product_event_category ON
product_event_category . event = product . event AND
product_event_category . root = true LEFT JOIN product_photo product_photo ON product_photo . event = product . event INNER JOIN users_profile project_profile ON
project_profile . id = ? LEFT JOIN product_offer_quantity product_offer_quantity ON product_offer_quantity . offer = product_offer . id LEFT JOIN product_offer_price product_offer_price ON product_offer_price . offer = product_offer . id LEFT JOIN product_category_offers category_offer ON category_offer . id = product_offer . category_offer LEFT JOIN product_variation product_variation ON product_variation . id = (SELECT tmp_product_variation . id FROM product_variation tmp_product_variation WHERE tmp_product_variation . offer = product_offer . id ORDER BY tmp_product_variation . id DESC LIMIT 1 ) LEFT JOIN product_variation_images product_variation_image ON product_variation_image . variation = product_variation . id LEFT JOIN product_offer_images product_offer_images ON product_offer_images . offer = product_offer . id LEFT JOIN product_variation_quantity product_variation_quantity ON product_variation_quantity . variation = product_variation . id LEFT JOIN product_variation_price product_variation_price ON product_variation_price . variation = product_variation . id LEFT JOIN product_category_variation category_offer_variation ON category_offer_variation . id = product_variation . category_variation LEFT JOIN product_modification product_modification ON product_modification . variation = product_variation . id LEFT JOIN product_modification_quantity product_modification_quantity ON product_modification_quantity . modification = product_modification . id LEFT JOIN product_modification_price product_modification_price ON product_modification_price . modification = product_modification . id LEFT JOIN product_category_modification category_offer_modification ON category_offer_modification . id = product_modification . category_modification LEFT JOIN product_modification_images product_modification_image ON product_modification_image . modification = product_modification . id LEFT JOIN product_invariable product_invariable ON
product_invariable . product = product . id AND
(
(product_offer . const IS NOT NULL AND product_invariable . offer = product_offer . const ) OR
(product_offer . const IS NULL AND product_invariable . offer IS NULL )
)
AND
(
(product_variation . const IS NOT NULL AND product_invariable . variation = product_variation . const ) OR
(product_variation . const IS NULL AND product_invariable . variation IS NULL )
)
AND
(
(product_modification . const IS NOT NULL AND product_invariable . modification = product_modification . const ) OR
(product_modification . const IS NULL AND product_invariable . modification IS NULL )
)
INNER JOIN product_category category ON
category . id = product_event_category . category AND
category . id = ? LEFT JOIN product_category_info category_info ON category_info . event = category . event LEFT JOIN user_profile_discount project_profile_discount ON
project_profile_discount . event = project_profile . event WHERE (
CASE
WHEN product_modification_price . price IS NOT NULL THEN product_modification_price . price
WHEN product_variation_price . price IS NOT NULL THEN product_variation_price . price
WHEN product_offer_price . price IS NOT NULL THEN product_offer_price . price
WHEN product_price . price IS NOT NULL THEN product_price . price
ELSE 0
END > 0 ) AND (
CASE
WHEN product_modification_quantity . quantity IS NOT NULL THEN (product_modification_quantity . quantity - product_modification_quantity . reserve )
WHEN product_variation_quantity . quantity IS NOT NULL THEN (product_variation_quantity . quantity - product_variation_quantity . reserve )
WHEN product_offer_quantity . quantity IS NOT NULL THEN (product_offer_quantity . quantity - product_offer_quantity . reserve )
WHEN product_price . quantity IS NOT NULL THEN (product_price . quantity - product_price . reserve )
ELSE 0
END > 0 ) AND (product_invariable . id ! = ? ) GROUP BY product_price . price , product_price . currency , product_price . reserve , product_price . quantity , product_offer_quantity . quantity , product_offer_quantity . reserve , product_offer_price . price , product_offer_price . currency , product_variation_quantity . quantity , product_variation_quantity . reserve , product_variation_price . price , product_variation_price . currency , product_modification_quantity . quantity , product_modification_quantity . reserve , product_modification_price . price , product_modification_price . currency , product_trans . name , product_info . url , product_info . sort , product_offer . id , product_offer . value , product_offer . postfix , category_offer . reference , product_variation . id , product_variation . value , product_variation . postfix , category_offer_variation . reference , product_modification . id , product_modification . value , product_modification . postfix , category_offer_modification . reference , category . id , category_info . url , product_modification_price . price , product_variation_price . price , product_offer_price . price , product_price . price , product_modification_price . old , product_variation_price . old , product_offer_price . old , product_price . old , product_modification_price . currency , product_variation_price . currency , product_offer_price . currency , product_price . currency , product_invariable . id , project_profile_discount . value ORDER BY product_modification_quantity . reserve DESC , product_variation_quantity . reserve DESC , product_offer_quantity . reserve DESC , product_price . reserve DESC LIMIT 2
Parameters :
[
"ru "
"019469c3-700f-76a9-9b34-ccde7b4e6f49 "
"01876af0-ddfc-70c3-ab25-5f85f55a9907 "
"0194002f-732d-7e40-b45d-c3ebcaa8a95d "
]
SELECT
product_trans . name AS product_name ,
product_info . url ,
product_info . sort ,
product_offer . id as product_offer_uid ,
product_offer . value as product_offer_value ,
product_offer . postfix as product_offer_postfix ,
category_offer . reference as product_offer_reference ,
product_variation . id as product_variation_uid ,
product_variation . value as product_variation_value ,
product_variation . postfix as product_variation_postfix ,
category_offer_variation . reference as product_variation_reference ,
product_modification . id as product_modification_uid ,
product_modification . value as product_modification_value ,
product_modification . postfix as product_modification_postfix ,
category_offer_modification . reference as product_modification_reference ,
category . id as category_id ,
category_info . url AS category_url ,
COALESCE (
NULLIF (
product_modification_price . price ,
0
),
NULLIF (
product_variation_price . price , 0
),
NULLIF (product_offer_price . price , 0 ),
NULLIF (product_price . price , 0 ),
0
) AS product_price ,
COALESCE (
NULLIF (
product_modification_price . old ,
0
),
NULLIF (product_variation_price . old , 0 ),
NULLIF (product_offer_price . old , 0 ),
NULLIF (product_price . old , 0 ),
0
) AS product_old_price ,
CASE WHEN COALESCE (
product_modification_price . price ,
0
) ! = 0 THEN
product_modification_price . currency
WHEN COALESCE (
product_variation_price . price , 0
) ! = 0 THEN
product_variation_price . currency
WHEN COALESCE (product_offer_price . price , 0 ) ! = 0 THEN
product_offer_price . currency
WHEN COALESCE (product_price . price , 0 ) ! = 0 THEN
product_price . currency
ELSE
NULL
END AS product_currency ,
JSON_AGG (
DISTINCT CASE WHEN product_offer_images . ext IS NOT NULL THEN
JSONB_BUILD_OBJECT (
'img_root' ,
product_offer_images . root ,
'img' ,
CONCAT (
'/upload/product_offer_images' ,
'/' , product_offer_images . name
),
'img_ext' ,
product_offer_images . ext ,
'img_cdn' ,
product_offer_images . cdn
)
WHEN product_variation_image . ext IS NOT NULL THEN
JSONB_BUILD_OBJECT (
'img_root' ,
product_variation_image . root ,
'img' ,
CONCAT (
'/upload/product_variation_images' ,
'/' , product_variation_image . name
),
'img_ext' ,
product_variation_image . ext ,
'img_cdn' ,
product_variation_image . cdn
)
WHEN product_modification_image . ext IS NOT NULL THEN
JSONB_BUILD_OBJECT (
'img_root' ,
product_modification_image . root ,
'img' ,
CONCAT (
'/upload/product_modification_images' ,
'/' , product_modification_image . name
),
'img_ext' ,
product_modification_image . ext ,
'img_cdn' ,
product_modification_image . cdn
)
WHEN product_photo . ext IS NOT NULL THEN
JSONB_BUILD_OBJECT (
'img_root' ,
product_photo . root ,
'img' ,
CONCAT (
'/upload/product_photo' , '/' , product_photo . name
),
'img_ext' ,
product_photo . ext ,
'img_cdn' ,
product_photo . cdn
)
END
) AS product_images ,
product_invariable . id AS product_invariable_id ,
project_profile_discount . value AS project_discount
FROM
product
LEFT JOIN product_trans product_trans ON product_trans . event = product . event
AND product_trans . local = ?
LEFT JOIN product_price product_price ON product_price . event = product . event
LEFT JOIN product_info product_info ON product_info . product = product . id
LEFT JOIN product_offer product_offer ON product_offer . event = product . event
LEFT JOIN product_categories_product product_event_category ON product_event_category . event = product . event
AND product_event_category . root = true
LEFT JOIN product_photo product_photo ON product_photo . event = product . event
INNER JOIN users_profile project_profile ON project_profile . id = ?
LEFT JOIN product_offer_quantity product_offer_quantity ON product_offer_quantity . offer = product_offer . id
LEFT JOIN product_offer_price product_offer_price ON product_offer_price . offer = product_offer . id
LEFT JOIN product_category_offers category_offer ON category_offer . id = product_offer . category_offer
LEFT JOIN product_variation product_variation ON product_variation . id = (
SELECT
tmp_product_variation . id
FROM
product_variation tmp_product_variation
WHERE
tmp_product_variation . offer = product_offer . id
ORDER BY
tmp_product_variation . id DESC
LIMIT
1
) LEFT JOIN product_variation_images product_variation_image ON product_variation_image . variation = product_variation . id
LEFT JOIN product_offer_images product_offer_images ON product_offer_images . offer = product_offer . id
LEFT JOIN product_variation_quantity product_variation_quantity ON product_variation_quantity . variation = product_variation . id
LEFT JOIN product_variation_price product_variation_price ON product_variation_price . variation = product_variation . id
LEFT JOIN product_category_variation category_offer_variation ON category_offer_variation . id = product_variation . category_variation
LEFT JOIN product_modification product_modification ON product_modification . variation = product_variation . id
LEFT JOIN product_modification_quantity product_modification_quantity ON product_modification_quantity . modification = product_modification . id
LEFT JOIN product_modification_price product_modification_price ON product_modification_price . modification = product_modification . id
LEFT JOIN product_category_modification category_offer_modification ON category_offer_modification . id = product_modification . category_modification
LEFT JOIN product_modification_images product_modification_image ON product_modification_image . modification = product_modification . id
LEFT JOIN product_invariable product_invariable ON product_invariable . product = product . id
AND (
(
product_offer . const IS NOT NULL
AND product_invariable . offer = product_offer . const
)
OR (
product_offer . const IS NULL
AND product_invariable . offer IS NULL
)
)
AND (
(
product_variation . const IS NOT NULL
AND product_invariable . variation = product_variation . const
)
OR (
product_variation . const IS NULL
AND product_invariable . variation IS NULL
)
)
AND (
(
product_modification . const IS NOT NULL
AND product_invariable . modification = product_modification . const
)
OR (
product_modification . const IS NULL
AND product_invariable . modification IS NULL
)
)
INNER JOIN product_category category ON category . id = product_event_category . category
AND category . id = ?
LEFT JOIN product_category_info category_info ON category_info . event = category . event
LEFT JOIN user_profile_discount project_profile_discount ON project_profile_discount . event = project_profile . event
WHERE
(
CASE WHEN product_modification_price . price IS NOT NULL THEN
product_modification_price . price
WHEN product_variation_price . price IS NOT NULL THEN
product_variation_price . price
WHEN product_offer_price . price IS NOT NULL THEN
product_offer_price . price
WHEN product_price . price IS NOT NULL THEN
product_price . price
ELSE
0
END > 0
)
AND (
CASE WHEN product_modification_quantity . quantity IS NOT NULL THEN
(
product_modification_quantity . quantity - product_modification_quantity . reserve
)
WHEN product_variation_quantity . quantity IS NOT NULL THEN
(
product_variation_quantity . quantity - product_variation_quantity . reserve
)
WHEN product_offer_quantity . quantity IS NOT NULL THEN
(
product_offer_quantity . quantity - product_offer_quantity . reserve
)
WHEN product_price . quantity IS NOT NULL THEN
(
product_price . quantity - product_price . reserve
)
ELSE
0
END > 0
)
AND (product_invariable . id ! = ? )
GROUP BY
product_price . price ,
product_price . currency ,
product_price . reserve ,
product_price . quantity ,
product_offer_quantity . quantity ,
product_offer_quantity . reserve ,
product_offer_price . price ,
product_offer_price . currency ,
product_variation_quantity . quantity ,
product_variation_quantity . reserve ,
product_variation_price . price ,
product_variation_price . currency ,
product_modification_quantity . quantity ,
product_modification_quantity . reserve ,
product_modification_price . price ,
product_modification_price . currency ,
product_trans . name ,
product_info . url ,
product_info . sort ,
product_offer . id ,
product_offer . value ,
product_offer . postfix ,
category_offer . reference ,
product_variation . id ,
product_variation . value ,
product_variation . postfix ,
category_offer_variation . reference ,
product_modification . id ,
product_modification . value ,
product_modification . postfix ,
category_offer_modification . reference ,
category . id ,
category_info . url ,
product_modification_price . price ,
product_variation_price . price ,
product_offer_price . price ,
product_price . price ,
product_modification_price . old ,
product_variation_price . old ,
product_offer_price . old ,
product_price . old ,
product_modification_price . currency ,
product_variation_price . currency ,
product_offer_price . currency ,
product_price . currency ,
product_invariable . id ,
project_profile_discount . value
ORDER BY
product_modification_quantity . reserve DESC ,
product_variation_quantity . reserve DESC ,
product_offer_quantity . reserve DESC ,
product_price . reserve DESC
LIMIT
2 Copy
SELECT product_trans . name AS product_name , product_info . url , product_info . sort , product_offer . id as product_offer_uid , product_offer . value as product_offer_value , product_offer . postfix as product_offer_postfix , category_offer . reference as product_offer_reference , product_variation . id as product_variation_uid , product_variation . value as product_variation_value , product_variation . postfix as product_variation_postfix , category_offer_variation . reference as product_variation_reference , product_modification . id as product_modification_uid , product_modification . value as product_modification_value , product_modification . postfix as product_modification_postfix , category_offer_modification . reference as product_modification_reference , category . id as category_id , category_info . url AS category_url ,
COALESCE (
NULLIF (product_modification_price . price , 0 ),
NULLIF (product_variation_price . price , 0 ),
NULLIF (product_offer_price . price , 0 ),
NULLIF (product_price . price , 0 ),
0
) AS product_price
,
COALESCE (
NULLIF (product_modification_price . old , 0 ),
NULLIF (product_variation_price . old , 0 ),
NULLIF (product_offer_price . old , 0 ),
NULLIF (product_price . old , 0 ),
0
) AS product_old_price
,
CASE
WHEN COALESCE (product_modification_price . price , 0 ) ! = 0
THEN product_modification_price . currency
WHEN COALESCE (product_variation_price . price , 0 ) ! = 0
THEN product_variation_price . currency
WHEN COALESCE (product_offer_price . price , 0 ) ! = 0
THEN product_offer_price . currency
WHEN COALESCE (product_price . price , 0 ) ! = 0
THEN product_price . currency
ELSE NULL
END AS product_currency , JSON_AGG
(DISTINCT
CASE
WHEN product_offer_images . ext IS NOT NULL
THEN JSONB_BUILD_OBJECT
(
'img_root' , product_offer_images . root ,
'img' , CONCAT ( '/upload/product_offer_images' , '/' , product_offer_images . name ),
'img_ext' , product_offer_images . ext ,
'img_cdn' , product_offer_images . cdn
)
WHEN product_variation_image . ext IS NOT NULL
THEN JSONB_BUILD_OBJECT
(
'img_root' , product_variation_image . root ,
'img' , CONCAT ( '/upload/product_variation_images' , '/' , product_variation_image . name ),
'img_ext' , product_variation_image . ext ,
'img_cdn' , product_variation_image . cdn
)
WHEN product_modification_image . ext IS NOT NULL
THEN JSONB_BUILD_OBJECT
(
'img_root' , product_modification_image . root ,
'img' , CONCAT ( '/upload/product_modification_images' , '/' , product_modification_image . name ),
'img_ext' , product_modification_image . ext ,
'img_cdn' , product_modification_image . cdn
)
WHEN product_photo . ext IS NOT NULL
THEN JSONB_BUILD_OBJECT
(
'img_root' , product_photo . root ,
'img' , CONCAT ( '/upload/product_photo' , '/' , product_photo . name ),
'img_ext' , product_photo . ext ,
'img_cdn' , product_photo . cdn
)
END ) AS product_images , product_invariable . id AS product_invariable_id , project_profile_discount . value AS project_discount FROM product LEFT JOIN product_trans product_trans ON product_trans . event = product . event AND product_trans . local = 'ru' LEFT JOIN product_price product_price ON product_price . event = product . event LEFT JOIN product_info product_info ON product_info . product = product . id LEFT JOIN product_offer product_offer ON product_offer . event = product . event LEFT JOIN product_categories_product product_event_category ON
product_event_category . event = product . event AND
product_event_category . root = true LEFT JOIN product_photo product_photo ON product_photo . event = product . event INNER JOIN users_profile project_profile ON
project_profile . id = '019469c3-700f-76a9-9b34-ccde7b4e6f49' LEFT JOIN product_offer_quantity product_offer_quantity ON product_offer_quantity . offer = product_offer . id LEFT JOIN product_offer_price product_offer_price ON product_offer_price . offer = product_offer . id LEFT JOIN product_category_offers category_offer ON category_offer . id = product_offer . category_offer LEFT JOIN product_variation product_variation ON product_variation . id = (SELECT tmp_product_variation . id FROM product_variation tmp_product_variation WHERE tmp_product_variation . offer = product_offer . id ORDER BY tmp_product_variation . id DESC LIMIT 1 ) LEFT JOIN product_variation_images product_variation_image ON product_variation_image . variation = product_variation . id LEFT JOIN product_offer_images product_offer_images ON product_offer_images . offer = product_offer . id LEFT JOIN product_variation_quantity product_variation_quantity ON product_variation_quantity . variation = product_variation . id LEFT JOIN product_variation_price product_variation_price ON product_variation_price . variation = product_variation . id LEFT JOIN product_category_variation category_offer_variation ON category_offer_variation . id = product_variation . category_variation LEFT JOIN product_modification product_modification ON product_modification . variation = product_variation . id LEFT JOIN product_modification_quantity product_modification_quantity ON product_modification_quantity . modification = product_modification . id LEFT JOIN product_modification_price product_modification_price ON product_modification_price . modification = product_modification . id LEFT JOIN product_category_modification category_offer_modification ON category_offer_modification . id = product_modification . category_modification LEFT JOIN product_modification_images product_modification_image ON product_modification_image . modification = product_modification . id LEFT JOIN product_invariable product_invariable ON
product_invariable . product = product . id AND
(
(product_offer . const IS NOT NULL AND product_invariable . offer = product_offer . const ) OR
(product_offer . const IS NULL AND product_invariable . offer IS NULL )
)
AND
(
(product_variation . const IS NOT NULL AND product_invariable . variation = product_variation . const ) OR
(product_variation . const IS NULL AND product_invariable . variation IS NULL )
)
AND
(
(product_modification . const IS NOT NULL AND product_invariable . modification = product_modification . const ) OR
(product_modification . const IS NULL AND product_invariable . modification IS NULL )
)
INNER JOIN product_category category ON
category . id = product_event_category . category AND
category . id = '01876af0-ddfc-70c3-ab25-5f85f55a9907' LEFT JOIN product_category_info category_info ON category_info . event = category . event LEFT JOIN user_profile_discount project_profile_discount ON
project_profile_discount . event = project_profile . event WHERE (
CASE
WHEN product_modification_price . price IS NOT NULL THEN product_modification_price . price
WHEN product_variation_price . price IS NOT NULL THEN product_variation_price . price
WHEN product_offer_price . price IS NOT NULL THEN product_offer_price . price
WHEN product_price . price IS NOT NULL THEN product_price . price
ELSE 0
END > 0 ) AND (
CASE
WHEN product_modification_quantity . quantity IS NOT NULL THEN (product_modification_quantity . quantity - product_modification_quantity . reserve )
WHEN product_variation_quantity . quantity IS NOT NULL THEN (product_variation_quantity . quantity - product_variation_quantity . reserve )
WHEN product_offer_quantity . quantity IS NOT NULL THEN (product_offer_quantity . quantity - product_offer_quantity . reserve )
WHEN product_price . quantity IS NOT NULL THEN (product_price . quantity - product_price . reserve )
ELSE 0
END > 0 ) AND (product_invariable . id ! = '0194002f-732d-7e40-b45d-c3ebcaa8a95d' ) GROUP BY product_price . price , product_price . currency , product_price . reserve , product_price . quantity , product_offer_quantity . quantity , product_offer_quantity . reserve , product_offer_price . price , product_offer_price . currency , product_variation_quantity . quantity , product_variation_quantity . reserve , product_variation_price . price , product_variation_price . currency , product_modification_quantity . quantity , product_modification_quantity . reserve , product_modification_price . price , product_modification_price . currency , product_trans . name , product_info . url , product_info . sort , product_offer . id , product_offer . value , product_offer . postfix , category_offer . reference , product_variation . id , product_variation . value , product_variation . postfix , category_offer_variation . reference , product_modification . id , product_modification . value , product_modification . postfix , category_offer_modification . reference , category . id , category_info . url , product_modification_price . price , product_variation_price . price , product_offer_price . price , product_price . price , product_modification_price . old , product_variation_price . old , product_offer_price . old , product_price . old , product_modification_price . currency , product_variation_price . currency , product_offer_price . currency , product_price . currency , product_invariable . id , project_profile_discount . value ORDER BY product_modification_quantity . reserve DESC , product_variation_quantity . reserve DESC , product_offer_quantity . reserve DESC , product_price . reserve DESC LIMIT 2 ; Copy
7
318.89 ms
SELECT product . id AS product_id , product_trans . name AS product_name , product_info . url AS product_url , category_info . url AS category_url , product_offer . value AS offer_value , product_offer . postfix AS offer_postfix , category_offer . reference AS offer_reference , product_variation . value AS variation_value , product_variation . postfix AS variation_postfix , category_variation . reference as variation_reference , product_modification . value AS modification_value , product_modification . postfix AS modification_postfix , category_modification . reference as modification_reference ,
COALESCE (
product_modification . article ,
product_variation . article ,
product_offer . article ,
product_info . article
) AS product_article
,
CASE
WHEN product_modification_quantity . quantity IS NOT NULL AND product_modification_quantity . quantity > 0 AND product_modification_quantity . quantity > product_modification_quantity . reserve
THEN (product_modification_quantity . quantity - product_modification_quantity . reserve )
WHEN product_variation_quantity . quantity IS NOT NULL AND product_variation_quantity . quantity > 0 AND product_variation_quantity . quantity > product_variation_quantity . reserve
THEN (product_variation_quantity . quantity - product_variation_quantity . reserve )
WHEN product_offer_quantity . quantity IS NOT NULL AND product_offer_quantity . quantity > 0 AND product_offer_quantity . quantity > product_offer_quantity . reserve
THEN (product_offer_quantity . quantity - product_offer_quantity . reserve )
WHEN product_price . quantity IS NOT NULL AND product_price . quantity > 0 AND product_price . quantity > product_price . reserve
THEN (product_price . quantity - product_price . reserve )
ELSE 0
END AS product_quantity
,
CASE
WHEN product_modification_image . ext IS NOT NULL THEN
JSON_AGG
(DISTINCT
JSONB_BUILD_OBJECT
(
'product_img_root' , product_modification_image . root ,
'product_img' , CONCAT ( '/upload/product_modification_images' , '/' , product_modification_image . name ),
'product_img_ext' , product_modification_image . ext ,
'product_img_cdn' , product_modification_image . cdn
)
)
WHEN product_variation_image . ext IS NOT NULL THEN
JSON_AGG
(DISTINCT
JSONB_BUILD_OBJECT
(
'product_img_root' , product_variation_image . root ,
'product_img' , CONCAT ( '/upload/product_variation_images' , '/' , product_variation_image . name ),
'product_img_ext' , product_variation_image . ext ,
'product_img_cdn' , product_variation_image . cdn
)
)
WHEN product_offer_images . ext IS NOT NULL THEN
JSON_AGG
(DISTINCT
JSONB_BUILD_OBJECT
(
'product_img_root' , product_offer_images . root ,
'product_img' , CONCAT ( '/upload/product_offer_images' , '/' , product_offer_images . name ),
'product_img_ext' , product_offer_images . ext ,
'product_img_cdn' , product_offer_images . cdn
)
)
WHEN product_photo . ext IS NOT NULL THEN
JSON_AGG
(DISTINCT
JSONB_BUILD_OBJECT
(
'product_img_root' , product_photo . root ,
'product_img' , CONCAT ( '/upload/product_photo' , '/' , product_photo . name ),
'product_img_ext' , product_photo . ext ,
'product_img_cdn' , product_photo . cdn
)
)
ELSE NULL
END
AS product_root_image ,
COALESCE (
NULLIF (modification_price . price , 0 ),
NULLIF (variation_price . price , 0 ),
NULLIF (offer_price . price , 0 ),
NULLIF (product_price . price , 0 ),
0
) AS price
,
COALESCE (
NULLIF (modification_price . old , 0 ),
NULLIF (variation_price . old , 0 ),
NULLIF (offer_price . old , 0 ),
NULLIF (product_price . old , 0 ),
0
) AS old_price
,
COALESCE (
modification_price . currency ,
variation_price . currency ,
offer_price . currency ,
product_price . currency
) AS currency
, product_invariable . id AS invariable , project_profile_discount . value AS project_discount , invariable . id as invariable_id FROM product_invariable invariable INNER JOIN product product ON product . id = invariable . product LEFT JOIN product_offer product_offer ON
product_offer . event = product . event AND
product_offer . const = invariable . offer
LEFT JOIN product_event product_event ON product_event . id = product . event INNER JOIN product_trans product_trans ON product_trans . event = product . event AND product_trans . local = ? INNER JOIN product_info product_info ON product_info . event = product . event INNER JOIN product_categories_product product_event_category ON
product_event_category . event = product . event AND
product_event_category . root = true
LEFT JOIN product_price product_price ON product_price . event = product . event INNER JOIN users_profile project_profile ON
project_profile . id = ? LEFT JOIN product_photo product_photo ON product_photo . event = product_event . id AND product_photo . root = true INNER JOIN product_category category ON category . id = product_event_category . category LEFT JOIN product_category_info category_info ON category_info . event = category . event LEFT JOIN user_profile_discount project_profile_discount ON
project_profile_discount . event = project_profile . event LEFT JOIN product_category_offers category_offer ON category_offer . id = product_offer . category_offer LEFT JOIN product_variation product_variation ON
product_variation . offer = product_offer . id AND
product_variation . const = invariable . variation
LEFT JOIN product_offer_price offer_price ON offer_price . offer = product_offer . id LEFT JOIN product_offer_quantity product_offer_quantity ON product_offer_quantity . offer = product_offer . id LEFT JOIN product_offer_images product_offer_images ON product_offer_images . offer = product_offer . id AND product_offer_images . root = true LEFT JOIN product_variation_images product_variation_image ON product_variation_image . variation = product_variation . id AND product_variation_image . root = true LEFT JOIN product_category_variation category_variation ON category_variation . id = product_variation . category_variation LEFT JOIN product_modification product_modification ON
product_modification . variation = product_variation . id AND
product_modification . const = invariable . modification
LEFT JOIN product_variation_price variation_price ON variation_price . variation = product_variation . id LEFT JOIN product_variation_quantity product_variation_quantity ON product_variation_quantity . variation = product_variation . id LEFT JOIN product_category_modification category_modification ON category_modification . id = product_modification . category_modification LEFT JOIN product_modification_price modification_price ON modification_price . modification = product_modification . id LEFT JOIN product_modification_quantity product_modification_quantity ON product_modification_quantity . modification = product_modification . id LEFT JOIN product_modification_images product_modification_image ON product_modification_image . modification = product_modification . id AND product_modification_image . root = true LEFT JOIN product_invariable product_invariable ON
product_invariable . product = product . id
AND (CASE
WHEN product_offer . const IS NOT NULL
THEN product_invariable . offer = product_offer . const
ELSE product_invariable . offer IS NULL
END )
AND (CASE
WHEN product_variation . const IS NOT NULL
THEN product_invariable . variation = product_variation . const
ELSE product_invariable . variation IS NULL
END )
AND (CASE
WHEN product_modification . const IS NOT NULL
THEN product_invariable . modification = product_modification . const
ELSE product_invariable . modification IS NULL
END )
WHERE invariable . id IN (? , ? , ? , ? , ? , ? ) GROUP BY product_photo . ext , product_offer_images . ext , product_variation_image . ext , product_modification_image . ext , product . id , product_trans . name , product_info . url , category_info . url , product_offer . value , product_offer . postfix , category_offer . reference , product_variation . value , product_variation . postfix , category_variation . reference , product_modification . value , product_modification . postfix , category_modification . reference , product_modification . article , product_variation . article , product_offer . article , product_info . article , product_modification_quantity . quantity , product_modification_quantity . reserve , product_variation_quantity . quantity , product_variation_quantity . reserve , product_offer_quantity . quantity , product_offer_quantity . reserve , product_price . quantity , product_price . reserve , modification_price . price , variation_price . price , offer_price . price , product_price . price , modification_price . old , variation_price . old , offer_price . old , product_price . old , modification_price . currency , variation_price . currency , offer_price . currency , product_price . currency , product_invariable . id , project_profile_discount . value , invariable . id ORDER BY CASE invariable . id WHEN '0194002f-732d-7e40-b45d-c3ebcaa8a95d' THEN 0 WHEN '0194002f-7326-7832-91f4-5fb5633ded8d' THEN 1 WHEN '0194002f-6c38-7fc0-988c-a51d8a20bc36' THEN 2 WHEN '0194002f-7293-7f6d-81cf-cc5aa1e51939' THEN 3 WHEN '0194002f-7310-7e57-bd89-23f448fec055' THEN 4 WHEN '0194002f-6aed-7c3c-8e6b-727b7ccacc7e' THEN 5 END LIMIT 5
Parameters :
[
"ru "
"019469c3-700f-76a9-9b34-ccde7b4e6f49 "
"0194002f-732d-7e40-b45d-c3ebcaa8a95d "
"0194002f-7326-7832-91f4-5fb5633ded8d "
"0194002f-6c38-7fc0-988c-a51d8a20bc36 "
"0194002f-7293-7f6d-81cf-cc5aa1e51939 "
"0194002f-7310-7e57-bd89-23f448fec055 "
"0194002f-6aed-7c3c-8e6b-727b7ccacc7e "
]
SELECT
product . id AS product_id ,
product_trans . name AS product_name ,
product_info . url AS product_url ,
category_info . url AS category_url ,
product_offer . value AS offer_value ,
product_offer . postfix AS offer_postfix ,
category_offer . reference AS offer_reference ,
product_variation . value AS variation_value ,
product_variation . postfix AS variation_postfix ,
category_variation . reference as variation_reference ,
product_modification . value AS modification_value ,
product_modification . postfix AS modification_postfix ,
category_modification . reference as modification_reference ,
COALESCE (
product_modification . article , product_variation . article ,
product_offer . article , product_info . article
) AS product_article ,
CASE WHEN product_modification_quantity . quantity IS NOT NULL
AND product_modification_quantity . quantity > 0
AND product_modification_quantity . quantity > product_modification_quantity . reserve THEN
(
product_modification_quantity . quantity - product_modification_quantity . reserve
)
WHEN product_variation_quantity . quantity IS NOT NULL
AND product_variation_quantity . quantity > 0
AND product_variation_quantity . quantity > product_variation_quantity . reserve THEN
(
product_variation_quantity . quantity - product_variation_quantity . reserve
)
WHEN product_offer_quantity . quantity IS NOT NULL
AND product_offer_quantity . quantity > 0
AND product_offer_quantity . quantity > product_offer_quantity . reserve THEN
(
product_offer_quantity . quantity - product_offer_quantity . reserve
)
WHEN product_price . quantity IS NOT NULL
AND product_price . quantity > 0
AND product_price . quantity > product_price . reserve THEN
(
product_price . quantity - product_price . reserve
)
ELSE
0
END AS product_quantity ,
CASE WHEN product_modification_image . ext IS NOT NULL THEN
JSON_AGG (
DISTINCT JSONB_BUILD_OBJECT (
'product_img_root' ,
product_modification_image . root ,
'product_img' ,
CONCAT (
'/upload/product_modification_images' ,
'/' , product_modification_image . name
),
'product_img_ext' ,
product_modification_image . ext ,
'product_img_cdn' ,
product_modification_image . cdn
)
)
WHEN product_variation_image . ext IS NOT NULL THEN
JSON_AGG (
DISTINCT JSONB_BUILD_OBJECT (
'product_img_root' ,
product_variation_image . root ,
'product_img' ,
CONCAT (
'/upload/product_variation_images' ,
'/' , product_variation_image . name
),
'product_img_ext' ,
product_variation_image . ext ,
'product_img_cdn' ,
product_variation_image . cdn
)
)
WHEN product_offer_images . ext IS NOT NULL THEN
JSON_AGG (
DISTINCT JSONB_BUILD_OBJECT (
'product_img_root' ,
product_offer_images . root ,
'product_img' ,
CONCAT (
'/upload/product_offer_images' ,
'/' , product_offer_images . name
),
'product_img_ext' ,
product_offer_images . ext ,
'product_img_cdn' ,
product_offer_images . cdn
)
)
WHEN product_photo . ext IS NOT NULL THEN
JSON_AGG (
DISTINCT JSONB_BUILD_OBJECT (
'product_img_root' ,
product_photo . root ,
'product_img' ,
CONCAT (
'/upload/product_photo' , '/' , product_photo . name
),
'product_img_ext' ,
product_photo . ext ,
'product_img_cdn' ,
product_photo . cdn
)
)
ELSE
NULL
END AS product_root_image ,
COALESCE (
NULLIF (modification_price . price , 0 ),
NULLIF (variation_price . price , 0 ),
NULLIF (offer_price . price , 0 ),
NULLIF (product_price . price , 0 ),
0
) AS price ,
COALESCE (
NULLIF (modification_price . old , 0 ),
NULLIF (variation_price . old , 0 ),
NULLIF (offer_price . old , 0 ),
NULLIF (product_price . old , 0 ),
0
) AS old_price ,
COALESCE (
modification_price . currency , variation_price . currency ,
offer_price . currency , product_price . currency
) AS currency ,
product_invariable . id AS invariable ,
project_profile_discount . value AS project_discount ,
invariable . id as invariable_id
FROM
product_invariable invariable
INNER JOIN product product ON product . id = invariable . product
LEFT JOIN product_offer product_offer ON product_offer . event = product . event
AND product_offer . const = invariable . offer
LEFT JOIN product_event product_event ON product_event . id = product . event
INNER JOIN product_trans product_trans ON product_trans . event = product . event
AND product_trans . local = ?
INNER JOIN product_info product_info ON product_info . event = product . event
INNER JOIN product_categories_product product_event_category ON product_event_category . event = product . event
AND product_event_category . root = true
LEFT JOIN product_price product_price ON product_price . event = product . event
INNER JOIN users_profile project_profile ON project_profile . id = ?
LEFT JOIN product_photo product_photo ON product_photo . event = product_event . id
AND product_photo . root = true
INNER JOIN product_category category ON category . id = product_event_category . category
LEFT JOIN product_category_info category_info ON category_info . event = category . event
LEFT JOIN user_profile_discount project_profile_discount ON project_profile_discount . event = project_profile . event
LEFT JOIN product_category_offers category_offer ON category_offer . id = product_offer . category_offer
LEFT JOIN product_variation product_variation ON product_variation . offer = product_offer . id
AND product_variation . const = invariable . variation
LEFT JOIN product_offer_price offer_price ON offer_price . offer = product_offer . id
LEFT JOIN product_offer_quantity product_offer_quantity ON product_offer_quantity . offer = product_offer . id
LEFT JOIN product_offer_images product_offer_images ON product_offer_images . offer = product_offer . id
AND product_offer_images . root = true
LEFT JOIN product_variation_images product_variation_image ON product_variation_image . variation = product_variation . id
AND product_variation_image . root = true
LEFT JOIN product_category_variation category_variation ON category_variation . id = product_variation . category_variation
LEFT JOIN product_modification product_modification ON product_modification . variation = product_variation . id
AND product_modification . const = invariable . modification
LEFT JOIN product_variation_price variation_price ON variation_price . variation = product_variation . id
LEFT JOIN product_variation_quantity product_variation_quantity ON product_variation_quantity . variation = product_variation . id
LEFT JOIN product_category_modification category_modification ON category_modification . id = product_modification . category_modification
LEFT JOIN product_modification_price modification_price ON modification_price . modification = product_modification . id
LEFT JOIN product_modification_quantity product_modification_quantity ON product_modification_quantity . modification = product_modification . id
LEFT JOIN product_modification_images product_modification_image ON product_modification_image . modification = product_modification . id
AND product_modification_image . root = true
LEFT JOIN product_invariable product_invariable ON product_invariable . product = product . id
AND (
CASE WHEN product_offer . const IS NOT NULL THEN
product_invariable . offer = product_offer . const
ELSE
product_invariable . offer IS NULL
END
)
AND (
CASE WHEN product_variation . const IS NOT NULL THEN
product_invariable . variation = product_variation . const
ELSE
product_invariable . variation IS NULL
END
)
AND (
CASE WHEN product_modification . const IS NOT NULL THEN
product_invariable . modification = product_modification . const
ELSE
product_invariable . modification IS NULL
END
)
WHERE
invariable . id IN (? , ? , ? , ? , ? , ? )
GROUP BY
product_photo . ext ,
product_offer_images . ext ,
product_variation_image . ext ,
product_modification_image . ext ,
product . id ,
product_trans . name ,
product_info . url ,
category_info . url ,
product_offer . value ,
product_offer . postfix ,
category_offer . reference ,
product_variation . value ,
product_variation . postfix ,
category_variation . reference ,
product_modification . value ,
product_modification . postfix ,
category_modification . reference ,
product_modification . article ,
product_variation . article ,
product_offer . article ,
product_info . article ,
product_modification_quantity . quantity ,
product_modification_quantity . reserve ,
product_variation_quantity . quantity ,
product_variation_quantity . reserve ,
product_offer_quantity . quantity ,
product_offer_quantity . reserve ,
product_price . quantity ,
product_price . reserve ,
modification_price . price ,
variation_price . price ,
offer_price . price ,
product_price . price ,
modification_price . old ,
variation_price . old ,
offer_price . old ,
product_price . old ,
modification_price . currency ,
variation_price . currency ,
offer_price . currency ,
product_price . currency ,
product_invariable . id ,
project_profile_discount . value ,
invariable . id
ORDER BY
CASE invariable . id
WHEN '0194002f-732d-7e40-b45d-c3ebcaa8a95d' THEN
0
WHEN '0194002f-7326-7832-91f4-5fb5633ded8d' THEN
1
WHEN '0194002f-6c38-7fc0-988c-a51d8a20bc36' THEN
2
WHEN '0194002f-7293-7f6d-81cf-cc5aa1e51939' THEN
3
WHEN '0194002f-7310-7e57-bd89-23f448fec055' THEN
4
WHEN '0194002f-6aed-7c3c-8e6b-727b7ccacc7e' THEN
5
END
LIMIT
5 Copy
SELECT product . id AS product_id , product_trans . name AS product_name , product_info . url AS product_url , category_info . url AS category_url , product_offer . value AS offer_value , product_offer . postfix AS offer_postfix , category_offer . reference AS offer_reference , product_variation . value AS variation_value , product_variation . postfix AS variation_postfix , category_variation . reference as variation_reference , product_modification . value AS modification_value , product_modification . postfix AS modification_postfix , category_modification . reference as modification_reference ,
COALESCE (
product_modification . article ,
product_variation . article ,
product_offer . article ,
product_info . article
) AS product_article
,
CASE
WHEN product_modification_quantity . quantity IS NOT NULL AND product_modification_quantity . quantity > 0 AND product_modification_quantity . quantity > product_modification_quantity . reserve
THEN (product_modification_quantity . quantity - product_modification_quantity . reserve )
WHEN product_variation_quantity . quantity IS NOT NULL AND product_variation_quantity . quantity > 0 AND product_variation_quantity . quantity > product_variation_quantity . reserve
THEN (product_variation_quantity . quantity - product_variation_quantity . reserve )
WHEN product_offer_quantity . quantity IS NOT NULL AND product_offer_quantity . quantity > 0 AND product_offer_quantity . quantity > product_offer_quantity . reserve
THEN (product_offer_quantity . quantity - product_offer_quantity . reserve )
WHEN product_price . quantity IS NOT NULL AND product_price . quantity > 0 AND product_price . quantity > product_price . reserve
THEN (product_price . quantity - product_price . reserve )
ELSE 0
END AS product_quantity
,
CASE
WHEN product_modification_image . ext IS NOT NULL THEN
JSON_AGG
(DISTINCT
JSONB_BUILD_OBJECT
(
'product_img_root' , product_modification_image . root ,
'product_img' , CONCAT ( '/upload/product_modification_images' , '/' , product_modification_image . name ),
'product_img_ext' , product_modification_image . ext ,
'product_img_cdn' , product_modification_image . cdn
)
)
WHEN product_variation_image . ext IS NOT NULL THEN
JSON_AGG
(DISTINCT
JSONB_BUILD_OBJECT
(
'product_img_root' , product_variation_image . root ,
'product_img' , CONCAT ( '/upload/product_variation_images' , '/' , product_variation_image . name ),
'product_img_ext' , product_variation_image . ext ,
'product_img_cdn' , product_variation_image . cdn
)
)
WHEN product_offer_images . ext IS NOT NULL THEN
JSON_AGG
(DISTINCT
JSONB_BUILD_OBJECT
(
'product_img_root' , product_offer_images . root ,
'product_img' , CONCAT ( '/upload/product_offer_images' , '/' , product_offer_images . name ),
'product_img_ext' , product_offer_images . ext ,
'product_img_cdn' , product_offer_images . cdn
)
)
WHEN product_photo . ext IS NOT NULL THEN
JSON_AGG
(DISTINCT
JSONB_BUILD_OBJECT
(
'product_img_root' , product_photo . root ,
'product_img' , CONCAT ( '/upload/product_photo' , '/' , product_photo . name ),
'product_img_ext' , product_photo . ext ,
'product_img_cdn' , product_photo . cdn
)
)
ELSE NULL
END
AS product_root_image ,
COALESCE (
NULLIF (modification_price . price , 0 ),
NULLIF (variation_price . price , 0 ),
NULLIF (offer_price . price , 0 ),
NULLIF (product_price . price , 0 ),
0
) AS price
,
COALESCE (
NULLIF (modification_price . old , 0 ),
NULLIF (variation_price . old , 0 ),
NULLIF (offer_price . old , 0 ),
NULLIF (product_price . old , 0 ),
0
) AS old_price
,
COALESCE (
modification_price . currency ,
variation_price . currency ,
offer_price . currency ,
product_price . currency
) AS currency
, product_invariable . id AS invariable , project_profile_discount . value AS project_discount , invariable . id as invariable_id FROM product_invariable invariable INNER JOIN product product ON product . id = invariable . product LEFT JOIN product_offer product_offer ON
product_offer . event = product . event AND
product_offer . const = invariable . offer
LEFT JOIN product_event product_event ON product_event . id = product . event INNER JOIN product_trans product_trans ON product_trans . event = product . event AND product_trans . local = 'ru' INNER JOIN product_info product_info ON product_info . event = product . event INNER JOIN product_categories_product product_event_category ON
product_event_category . event = product . event AND
product_event_category . root = true
LEFT JOIN product_price product_price ON product_price . event = product . event INNER JOIN users_profile project_profile ON
project_profile . id = '019469c3-700f-76a9-9b34-ccde7b4e6f49' LEFT JOIN product_photo product_photo ON product_photo . event = product_event . id AND product_photo . root = true INNER JOIN product_category category ON category . id = product_event_category . category LEFT JOIN product_category_info category_info ON category_info . event = category . event LEFT JOIN user_profile_discount project_profile_discount ON
project_profile_discount . event = project_profile . event LEFT JOIN product_category_offers category_offer ON category_offer . id = product_offer . category_offer LEFT JOIN product_variation product_variation ON
product_variation . offer = product_offer . id AND
product_variation . const = invariable . variation
LEFT JOIN product_offer_price offer_price ON offer_price . offer = product_offer . id LEFT JOIN product_offer_quantity product_offer_quantity ON product_offer_quantity . offer = product_offer . id LEFT JOIN product_offer_images product_offer_images ON product_offer_images . offer = product_offer . id AND product_offer_images . root = true LEFT JOIN product_variation_images product_variation_image ON product_variation_image . variation = product_variation . id AND product_variation_image . root = true LEFT JOIN product_category_variation category_variation ON category_variation . id = product_variation . category_variation LEFT JOIN product_modification product_modification ON
product_modification . variation = product_variation . id AND
product_modification . const = invariable . modification
LEFT JOIN product_variation_price variation_price ON variation_price . variation = product_variation . id LEFT JOIN product_variation_quantity product_variation_quantity ON product_variation_quantity . variation = product_variation . id LEFT JOIN product_category_modification category_modification ON category_modification . id = product_modification . category_modification LEFT JOIN product_modification_price modification_price ON modification_price . modification = product_modification . id LEFT JOIN product_modification_quantity product_modification_quantity ON product_modification_quantity . modification = product_modification . id LEFT JOIN product_modification_images product_modification_image ON product_modification_image . modification = product_modification . id AND product_modification_image . root = true LEFT JOIN product_invariable product_invariable ON
product_invariable . product = product . id
AND (CASE
WHEN product_offer . const IS NOT NULL
THEN product_invariable . offer = product_offer . const
ELSE product_invariable . offer IS NULL
END )
AND (CASE
WHEN product_variation . const IS NOT NULL
THEN product_invariable . variation = product_variation . const
ELSE product_invariable . variation IS NULL
END )
AND (CASE
WHEN product_modification . const IS NOT NULL
THEN product_invariable . modification = product_modification . const
ELSE product_invariable . modification IS NULL
END )
WHERE invariable . id IN ('0194002f-732d-7e40-b45d-c3ebcaa8a95d' , '0194002f-7326-7832-91f4-5fb5633ded8d' , '0194002f-6c38-7fc0-988c-a51d8a20bc36' , '0194002f-7293-7f6d-81cf-cc5aa1e51939' , '0194002f-7310-7e57-bd89-23f448fec055' , '0194002f-6aed-7c3c-8e6b-727b7ccacc7e' ) GROUP BY product_photo . ext , product_offer_images . ext , product_variation_image . ext , product_modification_image . ext , product . id , product_trans . name , product_info . url , category_info . url , product_offer . value , product_offer . postfix , category_offer . reference , product_variation . value , product_variation . postfix , category_variation . reference , product_modification . value , product_modification . postfix , category_modification . reference , product_modification . article , product_variation . article , product_offer . article , product_info . article , product_modification_quantity . quantity , product_modification_quantity . reserve , product_variation_quantity . quantity , product_variation_quantity . reserve , product_offer_quantity . quantity , product_offer_quantity . reserve , product_price . quantity , product_price . reserve , modification_price . price , variation_price . price , offer_price . price , product_price . price , modification_price . old , variation_price . old , offer_price . old , product_price . old , modification_price . currency , variation_price . currency , offer_price . currency , product_price . currency , product_invariable . id , project_profile_discount . value , invariable . id ORDER BY CASE invariable . id WHEN '0194002f-732d-7e40-b45d-c3ebcaa8a95d' THEN 0 WHEN '0194002f-7326-7832-91f4-5fb5633ded8d' THEN 1 WHEN '0194002f-6c38-7fc0-988c-a51d8a20bc36' THEN 2 WHEN '0194002f-7293-7f6d-81cf-cc5aa1e51939' THEN 3 WHEN '0194002f-7310-7e57-bd89-23f448fec055' THEN 4 WHEN '0194002f-6aed-7c3c-8e6b-727b7ccacc7e' THEN 5 END LIMIT 5 ; Copy