255.21 ms
(64.83%)
|
1 |
SELECT product.id AS product_id, product.event AS product_event, product_trans.name AS product_name, product_info.url AS product_url, product_info.sort AS product_sort, product_active.active_from AS product_active_from, category_offer.card AS category_offer_card , category_offer.reference AS product_offer_reference,
CASE
WHEN category_offer.card IS NOT NULL AND category_offer.card IS TRUE
THEN product_offer.value
ELSE NULL
END AS product_offer_value
,
CASE
WHEN category_offer.card IS NOT NULL AND category_offer.card IS TRUE
THEN product_offer.postfix
ELSE NULL
END AS product_offer_postfix
,
JSON_AGG( DISTINCT
CASE
WHEN product_offer.value IS NOT NULL THEN
JSONB_BUILD_OBJECT (
'offer_id', product_offer.id,
'offer_value', product_offer.value,
'offer_postfix', product_offer.postfix
)
ELSE NULL
END
) AS offer_agg, category_variation.card AS category_variation_card, category_variation.reference as product_variation_reference,
CASE
WHEN category_variation.card IS NOT NULL AND category_variation.card IS TRUE
THEN product_variation.value
ELSE NULL
END AS product_variation_value,
CASE
WHEN category_variation.card IS NOT NULL AND category_variation.card IS TRUE
THEN product_variation.postfix
ELSE NULL
END
AS product_variation_postfix
,
JSON_AGG( DISTINCT
CASE
WHEN product_variation.value IS NOT NULL THEN
JSONB_BUILD_OBJECT (
'variation_id', product_variation.id,
'variation_value', product_variation.value,
'variation_postfix', product_variation.postfix
)
ELSE NULL
END
) AS variation_agg, category_modification.card AS category_modification_card , category_modification.reference as product_modification_reference,
CASE
WHEN category_modification.card IS NOT NULL AND category_modification.card IS TRUE
THEN product_modification.value
ELSE NULL
END
AS product_modification_value
,
CASE
WHEN category_modification.card IS NOT NULL AND category_modification.card IS TRUE
THEN product_modification.postfix
ELSE NULL
END
AS product_modification_postfix
,
JSON_AGG( DISTINCT
CASE
WHEN product_modification.value IS NOT NULL THEN
JSONB_BUILD_OBJECT (
'modification_id', product_modification.id,
'modification_value', product_modification.value,
'modification_postfix', product_modification.postfix
)
ELSE NULL
END
) AS modification_agg, JSON_AGG( DISTINCT product_invariable.id) AS invariable,
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
)
ELSE NULL
END
)
AS product_root_images, category_info.url AS category_url, category_trans.name AS category_name, JSON_AGG
( DISTINCT
JSONB_BUILD_OBJECT
(
'field_name', category_section_field.name,
'field_card', category_section_field.card,
'field_photo', category_section_field.photo,
'field_type', category_section_field.type,
'field_trans', category_section_field_trans.name,
'field_value', product_property.value
)
)
AS category_section_field,
COALESCE(
NULLIF(MIN(product_modification_price.price), 0),
NULLIF(MIN(product_variation_price.price), 0),
NULLIF(MIN(product_offer_price.price), 0),
NULLIF(MIN(product_price.price), 0),
0
) AS product_price
,
COALESCE(
NULLIF(MIN(product_modification_price.old), 0),
NULLIF(MIN(product_variation_price.old), 0),
NULLIF(MIN(product_offer_price.old), 0),
NULLIF(MIN(product_price.old), 0),
0
) AS product_old_price
,
CASE
WHEN MIN(product_modification_price.price) IS NOT NULL
THEN product_modification_price.currency
WHEN MIN(product_variation_price.price) IS NOT NULL
THEN product_variation_price.currency
WHEN MIN(product_offer_price.price) IS NOT NULL
THEN product_offer_price.currency
WHEN product_price.price IS NOT NULL
THEN product_price.currency
ELSE NULL
END AS product_currency
,
CASE
WHEN SUM(product_modification_quantity.quantity) - SUM(product_modification_quantity.reserve) > 0
THEN SUM(product_modification_quantity.quantity) - SUM(product_modification_quantity.reserve)
WHEN SUM(product_variation_quantity.quantity) - SUM(product_variation_quantity.reserve) > 0
THEN SUM(product_variation_quantity.quantity) - SUM(product_variation_quantity.reserve)
WHEN SUM(product_offer_quantity.quantity) - SUM(product_offer_quantity.reserve) > 0
THEN SUM(product_offer_quantity.quantity) - SUM(product_offer_quantity.reserve)
WHEN SUM(product_price.quantity) - SUM(product_price.reserve) > 0
THEN SUM(product_price.quantity) - SUM(product_price.reserve)
ELSE 0
END AS product_quantity
, project_profile_discount.value AS project_discount FROM product INNER JOIN product_categories_product product_event_category ON product_event_category.event = product.event AND product_event_category.category IN (?) 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 = ? AND product_desc.local = ? LEFT JOIN product_info product_info ON product_info.product = product.id INNER JOIN product_active product_active ON
product_active.event = product.event AND
product_active.active IS TRUE AND
(product_active.active_to IS NULL OR product_active.active_to > NOW())
LEFT JOIN product_price product_price ON product_price.event = product.event AND product_price.price > 0 LEFT JOIN product_offer product_offer ON product_offer.event = product.event LEFT JOIN product_photo product_photo ON product_photo.event = product.event AND product_photo.root = true 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_info category_info ON category_info.event = category.event AND category_info.active IS TRUE LEFT JOIN product_category_trans category_trans ON category_trans.event = category.event AND category_trans.local = ? 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 OR
category_section_field.photo = TRUE OR
category_section_field.name = 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 product_property ON product_property.event = product.event AND product_property.field = category_section_field.const 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 AND product_offer_price.price > 0 LEFT JOIN product_offer_quantity product_offer_quantity ON product_offer_quantity.offer = product_offer.id LEFT JOIN product_variation product_variation ON product_variation.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 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
LEFT JOIN product_variation_price product_variation_price ON product_variation_price.variation = product_variation.id AND product_variation_price.price > 0 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 AND product_modification_price.price > 0 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 user_profile_discount project_profile_discount ON
project_profile_discount.event = project_profile.event WHERE
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
) <> 0 GROUP BY product_price.price, product_price.currency,
CASE
WHEN category_offer.card IS NOT NULL AND category_offer.card IS TRUE
THEN product_offer.value
END
,
CASE
WHEN category_offer.card IS NOT NULL AND category_offer.card IS TRUE
THEN product_offer.postfix
END
, product_offer_price.currency,
CASE
WHEN category_variation.card IS NOT NULL AND category_variation.card IS TRUE
THEN product_variation.value
END,
CASE
WHEN category_variation.card IS NOT NULL AND category_variation.card IS TRUE
THEN product_variation.postfix
END, product_variation_price.currency,
CASE
WHEN category_modification.card IS NOT NULL AND category_modification.card IS TRUE
THEN product_modification.value
END,
CASE
WHEN category_modification.card IS NOT NULL AND category_modification.card IS TRUE
THEN product_modification.postfix
END, product_modification_price.currency, product_modification_price.currency, product.id, product.event, product_trans.name, product_info.url, product_info.sort, product_active.active_from, category_offer.card, category_offer.reference, category_variation.card, category_variation.reference, category_modification.card, category_modification.reference, category_info.url, category_trans.name, project_profile_discount.value 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 24
SELECT
product.id AS product_id,
product.event AS product_event,
product_trans.name AS product_name,
product_info.url AS product_url,
product_info.sort AS product_sort,
product_active.active_from AS product_active_from,
category_offer.card AS category_offer_card,
category_offer.reference AS product_offer_reference,
CASE WHEN category_offer.card IS NOT NULL
AND category_offer.card IS TRUE THEN
product_offer.value
ELSE
NULL
END AS product_offer_value,
CASE WHEN category_offer.card IS NOT NULL
AND category_offer.card IS TRUE THEN
product_offer.postfix
ELSE
NULL
END AS product_offer_postfix,
JSON_AGG(
DISTINCT CASE WHEN product_offer.value IS NOT NULL THEN
JSONB_BUILD_OBJECT (
'offer_id', product_offer.id, 'offer_value',
product_offer.value, 'offer_postfix',
product_offer.postfix
)
ELSE
NULL
END
) AS offer_agg,
category_variation.card AS category_variation_card,
category_variation.reference as product_variation_reference,
CASE WHEN category_variation.card IS NOT NULL
AND category_variation.card IS TRUE THEN
product_variation.value
ELSE
NULL
END AS product_variation_value,
CASE WHEN category_variation.card IS NOT NULL
AND category_variation.card IS TRUE THEN
product_variation.postfix
ELSE
NULL
END AS product_variation_postfix,
JSON_AGG(
DISTINCT CASE WHEN product_variation.value IS NOT NULL THEN
JSONB_BUILD_OBJECT (
'variation_id', product_variation.id,
'variation_value', product_variation.value,
'variation_postfix', product_variation.postfix
)
ELSE
NULL
END
) AS variation_agg,
category_modification.card AS category_modification_card,
category_modification.reference as product_modification_reference,
CASE WHEN category_modification.card IS NOT NULL
AND category_modification.card IS TRUE THEN
product_modification.value
ELSE
NULL
END AS product_modification_value,
CASE WHEN category_modification.card IS NOT NULL
AND category_modification.card IS TRUE THEN
product_modification.postfix
ELSE
NULL
END AS product_modification_postfix,
JSON_AGG(
DISTINCT CASE WHEN product_modification.value IS NOT NULL THEN
JSONB_BUILD_OBJECT (
'modification_id', product_modification.id,
'modification_value', product_modification.value,
'modification_postfix', product_modification.postfix
)
ELSE
NULL
END
) AS modification_agg,
JSON_AGG(DISTINCT product_invariable.id) AS invariable,
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
)
ELSE
NULL
END
) AS product_root_images,
category_info.url AS category_url,
category_trans.name AS category_name,
JSON_AGG (
DISTINCT JSONB_BUILD_OBJECT (
'field_name', category_section_field.name,
'field_card', category_section_field.card,
'field_photo', category_section_field.photo,
'field_type', category_section_field.type,
'field_trans', category_section_field_trans.name,
'field_value', product_property.value
)
) AS category_section_field,
COALESCE(
NULLIF(
MIN(
product_modification_price.price
),
0
),
NULLIF(
MIN(product_variation_price.price),
0
),
NULLIF(
MIN(product_offer_price.price),
0
),
NULLIF(
MIN(product_price.price),
0
),
0
) AS product_price,
COALESCE(
NULLIF(
MIN(product_modification_price.old),
0
),
NULLIF(
MIN(product_variation_price.old),
0
),
NULLIF(
MIN(product_offer_price.old),
0
),
NULLIF(
MIN(product_price.old),
0
),
0
) AS product_old_price,
CASE WHEN MIN(
product_modification_price.price
) IS NOT NULL THEN
product_modification_price.currency
WHEN MIN(product_variation_price.price) IS NOT NULL THEN
product_variation_price.currency
WHEN MIN(product_offer_price.price) IS NOT NULL THEN
product_offer_price.currency
WHEN product_price.price IS NOT NULL THEN
product_price.currency
ELSE
NULL
END AS product_currency,
CASE WHEN SUM(
product_modification_quantity.quantity
) - SUM(
product_modification_quantity.reserve
) > 0 THEN
SUM(
product_modification_quantity.quantity
) - SUM(
product_modification_quantity.reserve
)
WHEN SUM(
product_variation_quantity.quantity
) - SUM(
product_variation_quantity.reserve
) > 0 THEN
SUM(
product_variation_quantity.quantity
) - SUM(
product_variation_quantity.reserve
)
WHEN SUM(
product_offer_quantity.quantity
) - SUM(product_offer_quantity.reserve) > 0 THEN
SUM(
product_offer_quantity.quantity
) - SUM(product_offer_quantity.reserve)
WHEN SUM(product_price.quantity) - SUM(product_price.reserve) > 0 THEN
SUM(product_price.quantity) - SUM(product_price.reserve)
ELSE
0
END AS product_quantity,
project_profile_discount.value AS project_discount
FROM
product
INNER JOIN product_categories_product product_event_category ON product_event_category.event = product.event
AND product_event_category.category IN (?)
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 = ?
AND product_desc.local = ?
LEFT JOIN product_info product_info ON product_info.product = product.id
INNER JOIN product_active product_active ON product_active.event = product.event
AND product_active.active IS TRUE
AND (
product_active.active_to IS NULL
OR product_active.active_to > NOW()
)
LEFT JOIN product_price product_price ON product_price.event = product.event
AND product_price.price > 0
LEFT JOIN product_offer product_offer ON product_offer.event = product.event
LEFT JOIN product_photo product_photo ON product_photo.event = product.event
AND product_photo.root = true
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_info category_info ON category_info.event = category.event
AND category_info.active IS TRUE
LEFT JOIN product_category_trans category_trans ON category_trans.event = category.event
AND category_trans.local = ?
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
OR category_section_field.photo = TRUE
OR category_section_field.name = 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 product_property ON product_property.event = product.event
AND product_property.field = category_section_field.const
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
AND product_offer_price.price > 0
LEFT JOIN product_offer_quantity product_offer_quantity ON product_offer_quantity.offer = product_offer.id
LEFT JOIN product_variation product_variation ON product_variation.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
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
LEFT JOIN product_variation_price product_variation_price ON product_variation_price.variation = product_variation.id
AND product_variation_price.price > 0
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
AND product_modification_price.price > 0
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 user_profile_discount project_profile_discount ON project_profile_discount.event = project_profile.event
WHERE
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
) <> 0
GROUP BY
product_price.price,
product_price.currency,
CASE WHEN category_offer.card IS NOT NULL
AND category_offer.card IS TRUE THEN
product_offer.value
END,
CASE WHEN category_offer.card IS NOT NULL
AND category_offer.card IS TRUE THEN
product_offer.postfix
END,
product_offer_price.currency,
CASE WHEN category_variation.card IS NOT NULL
AND category_variation.card IS TRUE THEN
product_variation.value
END,
CASE WHEN category_variation.card IS NOT NULL
AND category_variation.card IS TRUE THEN
product_variation.postfix
END,
product_variation_price.currency,
CASE WHEN category_modification.card IS NOT NULL
AND category_modification.card IS TRUE THEN
product_modification.value
END,
CASE WHEN category_modification.card IS NOT NULL
AND category_modification.card IS TRUE THEN
product_modification.postfix
END,
product_modification_price.currency,
product_modification_price.currency,
product.id,
product.event,
product_trans.name,
product_info.url,
product_info.sort,
product_active.active_from,
category_offer.card,
category_offer.reference,
category_variation.card,
category_variation.reference,
category_modification.card,
category_modification.reference,
category_info.url,
category_trans.name,
project_profile_discount.value
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
24
SELECT product.id AS product_id, product.event AS product_event, product_trans.name AS product_name, product_info.url AS product_url, product_info.sort AS product_sort, product_active.active_from AS product_active_from, category_offer.card AS category_offer_card , category_offer.reference AS product_offer_reference,
CASE
WHEN category_offer.card IS NOT NULL AND category_offer.card IS TRUE
THEN product_offer.value
ELSE NULL
END AS product_offer_value
,
CASE
WHEN category_offer.card IS NOT NULL AND category_offer.card IS TRUE
THEN product_offer.postfix
ELSE NULL
END AS product_offer_postfix
,
JSON_AGG( DISTINCT
CASE
WHEN product_offer.value IS NOT NULL THEN
JSONB_BUILD_OBJECT (
'offer_id', product_offer.id,
'offer_value', product_offer.value,
'offer_postfix', product_offer.postfix
)
ELSE NULL
END
) AS offer_agg, category_variation.card AS category_variation_card, category_variation.reference as product_variation_reference,
CASE
WHEN category_variation.card IS NOT NULL AND category_variation.card IS TRUE
THEN product_variation.value
ELSE NULL
END AS product_variation_value,
CASE
WHEN category_variation.card IS NOT NULL AND category_variation.card IS TRUE
THEN product_variation.postfix
ELSE NULL
END
AS product_variation_postfix
,
JSON_AGG( DISTINCT
CASE
WHEN product_variation.value IS NOT NULL THEN
JSONB_BUILD_OBJECT (
'variation_id', product_variation.id,
'variation_value', product_variation.value,
'variation_postfix', product_variation.postfix
)
ELSE NULL
END
) AS variation_agg, category_modification.card AS category_modification_card , category_modification.reference as product_modification_reference,
CASE
WHEN category_modification.card IS NOT NULL AND category_modification.card IS TRUE
THEN product_modification.value
ELSE NULL
END
AS product_modification_value
,
CASE
WHEN category_modification.card IS NOT NULL AND category_modification.card IS TRUE
THEN product_modification.postfix
ELSE NULL
END
AS product_modification_postfix
,
JSON_AGG( DISTINCT
CASE
WHEN product_modification.value IS NOT NULL THEN
JSONB_BUILD_OBJECT (
'modification_id', product_modification.id,
'modification_value', product_modification.value,
'modification_postfix', product_modification.postfix
)
ELSE NULL
END
) AS modification_agg, JSON_AGG( DISTINCT product_invariable.id) AS invariable,
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
)
ELSE NULL
END
)
AS product_root_images, category_info.url AS category_url, category_trans.name AS category_name, JSON_AGG
( DISTINCT
JSONB_BUILD_OBJECT
(
'field_name', category_section_field.name,
'field_card', category_section_field.card,
'field_photo', category_section_field.photo,
'field_type', category_section_field.type,
'field_trans', category_section_field_trans.name,
'field_value', product_property.value
)
)
AS category_section_field,
COALESCE(
NULLIF(MIN(product_modification_price.price), 0),
NULLIF(MIN(product_variation_price.price), 0),
NULLIF(MIN(product_offer_price.price), 0),
NULLIF(MIN(product_price.price), 0),
0
) AS product_price
,
COALESCE(
NULLIF(MIN(product_modification_price.old), 0),
NULLIF(MIN(product_variation_price.old), 0),
NULLIF(MIN(product_offer_price.old), 0),
NULLIF(MIN(product_price.old), 0),
0
) AS product_old_price
,
CASE
WHEN MIN(product_modification_price.price) IS NOT NULL
THEN product_modification_price.currency
WHEN MIN(product_variation_price.price) IS NOT NULL
THEN product_variation_price.currency
WHEN MIN(product_offer_price.price) IS NOT NULL
THEN product_offer_price.currency
WHEN product_price.price IS NOT NULL
THEN product_price.currency
ELSE NULL
END AS product_currency
,
CASE
WHEN SUM(product_modification_quantity.quantity) - SUM(product_modification_quantity.reserve) > 0
THEN SUM(product_modification_quantity.quantity) - SUM(product_modification_quantity.reserve)
WHEN SUM(product_variation_quantity.quantity) - SUM(product_variation_quantity.reserve) > 0
THEN SUM(product_variation_quantity.quantity) - SUM(product_variation_quantity.reserve)
WHEN SUM(product_offer_quantity.quantity) - SUM(product_offer_quantity.reserve) > 0
THEN SUM(product_offer_quantity.quantity) - SUM(product_offer_quantity.reserve)
WHEN SUM(product_price.quantity) - SUM(product_price.reserve) > 0
THEN SUM(product_price.quantity) - SUM(product_price.reserve)
ELSE 0
END AS product_quantity
, project_profile_discount.value AS project_discount FROM product INNER JOIN product_categories_product product_event_category ON product_event_category.event = product.event AND product_event_category.category IN ('01924385-2099-71c1-a793-c114a808e485') 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' AND product_desc.local = 'ru' LEFT JOIN product_info product_info ON product_info.product = product.id INNER JOIN product_active product_active ON
product_active.event = product.event AND
product_active.active IS TRUE AND
(product_active.active_to IS NULL OR product_active.active_to > NOW())
LEFT JOIN product_price product_price ON product_price.event = product.event AND product_price.price > 0 LEFT JOIN product_offer product_offer ON product_offer.event = product.event LEFT JOIN product_photo product_photo ON product_photo.event = product.event AND product_photo.root = true 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_info category_info ON category_info.event = category.event AND category_info.active IS TRUE LEFT JOIN product_category_trans category_trans ON category_trans.event = category.event AND category_trans.local = 'ru' 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 OR
category_section_field.photo = TRUE OR
category_section_field.name = 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 product_property ON product_property.event = product.event AND product_property.field = category_section_field.const 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 AND product_offer_price.price > 0 LEFT JOIN product_offer_quantity product_offer_quantity ON product_offer_quantity.offer = product_offer.id LEFT JOIN product_variation product_variation ON product_variation.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 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
LEFT JOIN product_variation_price product_variation_price ON product_variation_price.variation = product_variation.id AND product_variation_price.price > 0 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 AND product_modification_price.price > 0 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 user_profile_discount project_profile_discount ON
project_profile_discount.event = project_profile.event WHERE
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
) <> 0 GROUP BY product_price.price, product_price.currency,
CASE
WHEN category_offer.card IS NOT NULL AND category_offer.card IS TRUE
THEN product_offer.value
END
,
CASE
WHEN category_offer.card IS NOT NULL AND category_offer.card IS TRUE
THEN product_offer.postfix
END
, product_offer_price.currency,
CASE
WHEN category_variation.card IS NOT NULL AND category_variation.card IS TRUE
THEN product_variation.value
END,
CASE
WHEN category_variation.card IS NOT NULL AND category_variation.card IS TRUE
THEN product_variation.postfix
END, product_variation_price.currency,
CASE
WHEN category_modification.card IS NOT NULL AND category_modification.card IS TRUE
THEN product_modification.value
END,
CASE
WHEN category_modification.card IS NOT NULL AND category_modification.card IS TRUE
THEN product_modification.postfix
END, product_modification_price.currency, product_modification_price.currency, product.id, product.event, product_trans.name, product_info.url, product_info.sort, product_active.active_from, category_offer.card, category_offer.reference, category_variation.card, category_variation.reference, category_modification.card, category_modification.reference, category_info.url, category_trans.name, project_profile_discount.value 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 24;
|
113.96 ms
(28.95%)
|
1 |
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) 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"
"01924385-2099-71c1-a793-c114a808e485"
]
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
)
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
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 = '01924385-2099-71c1-a793-c114a808e485' 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) 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;
|
19.04 ms
(4.84%)
|
1 |
SELECT info.event AS category_event, info.url AS category_url, info.counter AS category_counter, product_category.id AS category_id, product_category_event.parent AS category_parent, product_category_trans.name AS category_name, product_category_landing.header AS category_header, product_category_landing.bottom AS category_bottom, parent_product_category_trans.name AS parent_category_name, parent_product_category_info.url AS parent_category_url, parent_product_category_info.counter AS parent_category_counter, JSON_AGG
( DISTINCT
JSONB_BUILD_OBJECT
(
'0', parent_category_event.sort,
'parent_category_url', parent_category_info.url,
'parent_category_counter', parent_category_info.counter,
'parent_category_cover_name',
CASE
WHEN parent_category_cover.name IS NOT NULL
THEN CONCAT ( '/upload/product_category_cover' , '/', parent_category_cover.name)
ELSE NULL
END,
'parent_category_cover_ext', parent_category_cover.ext,
'parent_category_cover_cdn', parent_category_cover.cdn,
'parent_category_event', parent_category_event.id,
'parent_category_name', parent_category_trans.name
)
)
AS parent_category,
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 FROM product_category_info info INNER JOIN product_category product_category ON product_category.event = info.event LEFT JOIN product_category_event product_category_event ON product_category_event.id = product_category.event LEFT JOIN product_category_trans product_category_trans ON product_category_trans.event = product_category_event.id AND product_category_trans.local = ? LEFT JOIN product_category_landing product_category_landing ON product_category_landing.event = product_category_event.id AND product_category_landing.local = ? LEFT JOIN product_category_event parent_category_event ON parent_category_event.parent = product_category.id LEFT JOIN product_category_cover category_cover ON category_cover.event = product_category.event LEFT JOIN product_category parent_product_category ON parent_product_category.id = product_category_event.parent LEFT JOIN product_category_trans parent_product_category_trans ON parent_product_category_trans.event = parent_product_category.event AND parent_product_category_trans.local = ? LEFT JOIN product_category_info parent_product_category_info ON parent_product_category_info.event = parent_product_category.event LEFT JOIN product_category_info parent_category_info ON parent_category_info.event = parent_category_event.id LEFT JOIN product_category_cover parent_category_cover ON parent_category_cover.event = parent_category_event.id LEFT JOIN product_category_trans parent_category_trans ON parent_category_trans.event = parent_category_event.id AND parent_category_trans.local = ? WHERE (info.url = ?) AND (info.active = true) GROUP BY info.event, info.url, info.counter, product_category.id, product_category_event.parent, product_category_trans.name, product_category_landing.header, product_category_landing.bottom, parent_product_category_trans.name, parent_product_category_info.url, parent_product_category_info.counter, category_cover.name, category_cover.ext, category_cover.cdn
Parameters: [
"ru"
"ru"
"ru"
"ru"
"kapsen"
]
SELECT
info.event AS category_event,
info.url AS category_url,
info.counter AS category_counter,
product_category.id AS category_id,
product_category_event.parent AS category_parent,
product_category_trans.name AS category_name,
product_category_landing.header AS category_header,
product_category_landing.bottom AS category_bottom,
parent_product_category_trans.name AS parent_category_name,
parent_product_category_info.url AS parent_category_url,
parent_product_category_info.counter AS parent_category_counter,
JSON_AGG (
DISTINCT JSONB_BUILD_OBJECT (
'0',
parent_category_event.sort,
'parent_category_url',
parent_category_info.url,
'parent_category_counter',
parent_category_info.counter,
'parent_category_cover_name',
CASE WHEN parent_category_cover.name IS NOT NULL THEN
CONCAT (
'/upload/product_category_cover',
'/', parent_category_cover.name
)
ELSE
NULL
END,
'parent_category_cover_ext',
parent_category_cover.ext,
'parent_category_cover_cdn',
parent_category_cover.cdn,
'parent_category_event',
parent_category_event.id,
'parent_category_name',
parent_category_trans.name
)
) AS parent_category,
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
FROM
product_category_info info
INNER JOIN product_category product_category ON product_category.event = info.event
LEFT JOIN product_category_event product_category_event ON product_category_event.id = product_category.event
LEFT JOIN product_category_trans product_category_trans ON product_category_trans.event = product_category_event.id
AND product_category_trans.local = ?
LEFT JOIN product_category_landing product_category_landing ON product_category_landing.event = product_category_event.id
AND product_category_landing.local = ?
LEFT JOIN product_category_event parent_category_event ON parent_category_event.parent = product_category.id
LEFT JOIN product_category_cover category_cover ON category_cover.event = product_category.event
LEFT JOIN product_category parent_product_category ON parent_product_category.id = product_category_event.parent
LEFT JOIN product_category_trans parent_product_category_trans ON parent_product_category_trans.event = parent_product_category.event
AND parent_product_category_trans.local = ?
LEFT JOIN product_category_info parent_product_category_info ON parent_product_category_info.event = parent_product_category.event
LEFT JOIN product_category_info parent_category_info ON parent_category_info.event = parent_category_event.id
LEFT JOIN product_category_cover parent_category_cover ON parent_category_cover.event = parent_category_event.id
LEFT JOIN product_category_trans parent_category_trans ON parent_category_trans.event = parent_category_event.id
AND parent_category_trans.local = ?
WHERE
(info.url = ?)
AND (info.active = true)
GROUP BY
info.event,
info.url,
info.counter,
product_category.id,
product_category_event.parent,
product_category_trans.name,
product_category_landing.header,
product_category_landing.bottom,
parent_product_category_trans.name,
parent_product_category_info.url,
parent_product_category_info.counter,
category_cover.name,
category_cover.ext,
category_cover.cdn
SELECT info.event AS category_event, info.url AS category_url, info.counter AS category_counter, product_category.id AS category_id, product_category_event.parent AS category_parent, product_category_trans.name AS category_name, product_category_landing.header AS category_header, product_category_landing.bottom AS category_bottom, parent_product_category_trans.name AS parent_category_name, parent_product_category_info.url AS parent_category_url, parent_product_category_info.counter AS parent_category_counter, JSON_AGG
( DISTINCT
JSONB_BUILD_OBJECT
(
'0', parent_category_event.sort,
'parent_category_url', parent_category_info.url,
'parent_category_counter', parent_category_info.counter,
'parent_category_cover_name',
CASE
WHEN parent_category_cover.name IS NOT NULL
THEN CONCAT ( '/upload/product_category_cover' , '/', parent_category_cover.name)
ELSE NULL
END,
'parent_category_cover_ext', parent_category_cover.ext,
'parent_category_cover_cdn', parent_category_cover.cdn,
'parent_category_event', parent_category_event.id,
'parent_category_name', parent_category_trans.name
)
)
AS parent_category,
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 FROM product_category_info info INNER JOIN product_category product_category ON product_category.event = info.event LEFT JOIN product_category_event product_category_event ON product_category_event.id = product_category.event LEFT JOIN product_category_trans product_category_trans ON product_category_trans.event = product_category_event.id AND product_category_trans.local = 'ru' LEFT JOIN product_category_landing product_category_landing ON product_category_landing.event = product_category_event.id AND product_category_landing.local = 'ru' LEFT JOIN product_category_event parent_category_event ON parent_category_event.parent = product_category.id LEFT JOIN product_category_cover category_cover ON category_cover.event = product_category.event LEFT JOIN product_category parent_product_category ON parent_product_category.id = product_category_event.parent LEFT JOIN product_category_trans parent_product_category_trans ON parent_product_category_trans.event = parent_product_category.event AND parent_product_category_trans.local = 'ru' LEFT JOIN product_category_info parent_product_category_info ON parent_product_category_info.event = parent_product_category.event LEFT JOIN product_category_info parent_category_info ON parent_category_info.event = parent_category_event.id LEFT JOIN product_category_cover parent_category_cover ON parent_category_cover.event = parent_category_event.id LEFT JOIN product_category_trans parent_category_trans ON parent_category_trans.event = parent_category_event.id AND parent_category_trans.local = 'ru' WHERE (info.url = 'kapsen') AND (info.active = true) GROUP BY info.event, info.url, info.counter, product_category.id, product_category_event.parent, product_category_trans.name, product_category_landing.header, product_category_landing.bottom, parent_product_category_trans.name, parent_product_category_info.url, parent_product_category_info.counter, category_cover.name, category_cover.ext, category_cover.cdn;
|