| 1 |
12.05 ms |
WITH RECURSIVE recursive_table AS (SELECT category.id, category.event, category_event.sort, category_event.parent, category_info.url AS category_url, category_trans.name AS category_name, CONCAT ('/upload/product_category_cover' , '/', category_cover.name) AS category_cover_image, category_cover.cdn AS category_cover_cdn, category_cover.ext AS category_cover_ext, category_event.id::varchar AS groups, 1 AS level FROM product_category category INNER JOIN product_category_event category_event ON category_event.id = category.event LEFT JOIN product_category_trans category_trans ON category_trans.event = category.event AND category_trans.local = ? LEFT JOIN product_category_cover category_cover ON category_cover.event = category.event INNER JOIN product_category_info category_info ON
category_info.event = category.event AND
category_info.active IS TRUE WHERE category_event.parent IS NULL UNION SELECT category.id, category.event, category_event.sort, category_event.parent, category_info.url AS category_url, category_trans.name AS category_name, CONCAT ('/upload/product_category_cover' , '/', category_cover.name) AS category_cover_image, category_cover.cdn AS category_cover_cdn, category_cover.ext AS category_cover_ext, CONCAT(groups, ':', category_event.id::varchar) AS groups , level+1 AS level FROM product_category category INNER JOIN product_category_event category_event ON category_event.id = category.event LEFT JOIN product_category_trans category_trans ON category_trans.event = category.event AND category_trans.local = ? LEFT JOIN product_category_cover category_cover ON category_cover.event = category.event INNER JOIN product_category_info category_info ON
category_info.event = category.event AND
category_info.active IS TRUE INNER JOIN recursive_table ON recursive_table.id = category_event.parent) SELECT * FROM recursive_table ORDER BY
recursive_table.groups,
recursive_table.sort
Parameters: [
"ru"
"ru"
]
WITH RECURSIVE recursive_table AS (SELECT category.id, category.event, category_event.sort, category_event.parent, category_info.url AS category_url, category_trans.name AS category_name, CONCAT ('/upload/product_category_cover' , '/', category_cover.name) AS category_cover_image, category_cover.cdn AS category_cover_cdn, category_cover.ext AS category_cover_ext, category_event.id::varchar AS groups, 1 AS level FROM product_category category INNER JOIN product_category_event category_event ON category_event.id = category.event LEFT JOIN product_category_trans category_trans ON category_trans.event = category.event AND category_trans.local = 'ru' LEFT JOIN product_category_cover category_cover ON category_cover.event = category.event INNER JOIN product_category_info category_info ON
category_info.event = category.event AND
category_info.active IS TRUE WHERE category_event.parent IS NULL UNION SELECT category.id, category.event, category_event.sort, category_event.parent, category_info.url AS category_url, category_trans.name AS category_name, CONCAT ('/upload/product_category_cover' , '/', category_cover.name) AS category_cover_image, category_cover.cdn AS category_cover_cdn, category_cover.ext AS category_cover_ext, CONCAT(groups, ':', category_event.id::varchar) AS groups , level+1 AS level FROM product_category category INNER JOIN product_category_event category_event ON category_event.id = category.event LEFT JOIN product_category_trans category_trans ON category_trans.event = category.event AND category_trans.local = 'ru' LEFT JOIN product_category_cover category_cover ON category_cover.event = category.event INNER JOIN product_category_info category_info ON
category_info.event = category.event AND
category_info.active IS TRUE INNER JOIN recursive_table ON recursive_table.id = category_event.parent) SELECT * FROM recursive_table ORDER BY
recursive_table.groups,
recursive_table.sort
;
|
| 2 |
387.23 ms |
SELECT product.id AS product_id, product.event AS product_event, product_info.url AS product_url, product_trans.name AS product_name, product_active.active_from, 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_offer_variation.id as product_variation_uid, product_offer_variation.value as product_variation_value, product_offer_variation.postfix as product_variation_postfix, category_offer_variation.reference as product_variation_reference, product_offer_modification.id as product_modification_uid, product_offer_modification.value as product_modification_value, product_offer_modification.postfix as product_modification_postfix, category_offer_modification.reference as product_modification_reference,
CASE
WHEN product_offer_modification.article IS NOT NULL
THEN product_offer_modification.article
WHEN product_offer_variation.article IS NOT NULL
THEN product_offer_variation.article
WHEN product_offer.article IS NOT NULL
THEN product_offer.article
WHEN product_info.article IS NOT NULL
THEN product_info.article
ELSE NULL
END AS product_article
, 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_offer_variation_image.ext IS NOT NULL
THEN JSONB_BUILD_OBJECT
(
'img_root', product_offer_variation_image.root,
'img', CONCAT ( '/upload/product_variation_images' , '/', product_offer_variation_image.name),
'img_ext', product_offer_variation_image.ext,
'img_cdn', product_offer_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,
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, category_info.url AS category_url, category_trans.name AS category_name, JSON_AGG
( DISTINCT
JSONB_BUILD_OBJECT
(
'field_sort', category_section_field.sort,
'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, product_invariable.id AS product_invariable_id, project_profile_discount.value AS project_discount FROM product INNER JOIN product_event product_event ON product_event.id = product.event INNER JOIN product_categories_product product_event_category ON
product_event_category.event = product.event AND
product_event_category.category = ? AND
product_event_category.root = true INNER JOIN product_active product_active ON product_active.event = product.event LEFT JOIN product_offer product_offer ON product_offer.event = product.event INNER JOIN users_profile project_profile ON
project_profile.id = ? LEFT JOIN product_info product_info ON product_info.product = product.id LEFT JOIN product_trans product_trans ON product_trans.event = product_event.id AND product_trans.local = ? LEFT JOIN product_price product_price ON product_price.event = product_event.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 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_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 LEFT JOIN product_category_offers category_offer ON category_offer.id = product_offer.category_offer LEFT JOIN product_variation product_offer_variation ON product_offer_variation.offer = product_offer.id LEFT JOIN product_variation_images product_offer_variation_image ON product_offer_variation_image.variation = product_offer_variation.id AND product_offer_variation_image.root = true LEFT JOIN product_offer_images product_offer_images ON product_offer_variation_image.name IS NULL AND product_offer_images.offer = product_offer.id AND product_offer_images.root = true LEFT JOIN product_photo product_photo ON product_offer_images.name IS NULL AND product_photo.event = product_event.id AND product_photo.root = true LEFT JOIN product_category_variation category_offer_variation ON category_offer_variation.id = product_offer_variation.category_variation LEFT JOIN product_variation_price product_variation_price ON product_variation_price.variation = product_offer_variation.id LEFT JOIN product_variation_quantity product_variation_quantity ON product_variation_quantity.variation = product_offer_variation.id LEFT JOIN product_modification product_offer_modification ON product_offer_modification.variation = product_offer_variation.id LEFT JOIN product_modification_price product_modification_price ON product_modification_price.modification = product_offer_modification.id LEFT JOIN product_modification_quantity product_modification_quantity ON product_modification_quantity.modification = product_offer_modification.id LEFT JOIN product_category_modification category_offer_modification ON category_offer_modification.id = product_offer_modification.category_modification LEFT JOIN product_modification_images product_offer_modification_image ON product_offer_modification_image.modification = product_offer_modification.id AND product_offer_modification_image.root = true 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_offer_variation.const IS NOT NULL AND product_invariable.variation = product_offer_variation.const) OR
(product_offer_variation.const IS NULL AND product_invariable.variation IS NULL)
)
AND
(
(product_offer_modification.const IS NOT NULL AND product_invariable.modification = product_offer_modification.const) OR
(product_offer_modification.const IS NULL AND product_invariable.modification IS NULL)
)
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_info.article, product_info.sort, product_price.price, product_price.currency, product_price.quantity, product_price.reserve, product_offer_price.price, product_offer_price.currency, product_offer_quantity.quantity, product_offer_quantity.reserve, product_variation_price.price, product_variation_price.currency, product_variation_quantity.quantity, product_variation_quantity.reserve, product_modification_price.price, product_modification_price.currency, product_modification_quantity.quantity, product_modification_quantity.reserve, product.id, product.event, product_info.url, product_trans.name, product_active.active_from, product_offer.id, product_offer.value, product_offer.postfix, category_offer.reference, product_offer_variation.id, product_offer_variation.value, product_offer_variation.postfix, category_offer_variation.reference, product_offer_modification.id, product_offer_modification.value, product_offer_modification.postfix, category_offer_modification.reference, product_offer_modification.article, product_offer_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_info.url, category_trans.name, product_invariable.id, project_profile_discount.value ORDER BY product_info.sort DESC, product_modification_quantity.reserve DESC, product_variation_quantity.reserve DESC, product_offer_quantity.reserve DESC, product_price.reserve DESC LIMIT 4
Parameters: [
"01876af0-ddfc-70c3-ab25-5f85f55a9907"
"019469c3-700f-76a9-9b34-ccde7b4e6f49"
"ru"
"ru"
"ru"
]
SELECT
product.id AS product_id,
product.event AS product_event,
product_info.url AS product_url,
product_trans.name AS product_name,
product_active.active_from,
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_offer_variation.id as product_variation_uid,
product_offer_variation.value as product_variation_value,
product_offer_variation.postfix as product_variation_postfix,
category_offer_variation.reference as product_variation_reference,
product_offer_modification.id as product_modification_uid,
product_offer_modification.value as product_modification_value,
product_offer_modification.postfix as product_modification_postfix,
category_offer_modification.reference as product_modification_reference,
CASE WHEN product_offer_modification.article IS NOT NULL THEN
product_offer_modification.article
WHEN product_offer_variation.article IS NOT NULL THEN
product_offer_variation.article
WHEN product_offer.article IS NOT NULL THEN
product_offer.article
WHEN product_info.article IS NOT NULL THEN
product_info.article
ELSE
NULL
END AS product_article,
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_offer_variation_image.ext IS NOT NULL THEN
JSONB_BUILD_OBJECT (
'img_root',
product_offer_variation_image.root,
'img',
CONCAT (
'/upload/product_variation_images',
'/', product_offer_variation_image.name
),
'img_ext',
product_offer_variation_image.ext,
'img_cdn',
product_offer_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,
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,
category_info.url AS category_url,
category_trans.name AS category_name,
JSON_AGG (
DISTINCT JSONB_BUILD_OBJECT (
'field_sort', category_section_field.sort,
'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,
product_invariable.id AS product_invariable_id,
project_profile_discount.value AS project_discount
FROM
product
INNER JOIN product_event product_event ON product_event.id = product.event
INNER JOIN product_categories_product product_event_category ON product_event_category.event = product.event
AND product_event_category.category = ?
AND product_event_category.root = true
INNER JOIN product_active product_active ON product_active.event = product.event
LEFT JOIN product_offer product_offer ON product_offer.event = product.event
INNER JOIN users_profile project_profile ON project_profile.id = ?
LEFT JOIN product_info product_info ON product_info.product = product.id
LEFT JOIN product_trans product_trans ON product_trans.event = product_event.id
AND product_trans.local = ?
LEFT JOIN product_price product_price ON product_price.event = product_event.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
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_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
LEFT JOIN product_category_offers category_offer ON category_offer.id = product_offer.category_offer
LEFT JOIN product_variation product_offer_variation ON product_offer_variation.offer = product_offer.id
LEFT JOIN product_variation_images product_offer_variation_image ON product_offer_variation_image.variation = product_offer_variation.id
AND product_offer_variation_image.root = true
LEFT JOIN product_offer_images product_offer_images ON product_offer_variation_image.name IS NULL
AND product_offer_images.offer = product_offer.id
AND product_offer_images.root = true
LEFT JOIN product_photo product_photo ON product_offer_images.name IS NULL
AND product_photo.event = product_event.id
AND product_photo.root = true
LEFT JOIN product_category_variation category_offer_variation ON category_offer_variation.id = product_offer_variation.category_variation
LEFT JOIN product_variation_price product_variation_price ON product_variation_price.variation = product_offer_variation.id
LEFT JOIN product_variation_quantity product_variation_quantity ON product_variation_quantity.variation = product_offer_variation.id
LEFT JOIN product_modification product_offer_modification ON product_offer_modification.variation = product_offer_variation.id
LEFT JOIN product_modification_price product_modification_price ON product_modification_price.modification = product_offer_modification.id
LEFT JOIN product_modification_quantity product_modification_quantity ON product_modification_quantity.modification = product_offer_modification.id
LEFT JOIN product_category_modification category_offer_modification ON category_offer_modification.id = product_offer_modification.category_modification
LEFT JOIN product_modification_images product_offer_modification_image ON product_offer_modification_image.modification = product_offer_modification.id
AND product_offer_modification_image.root = true
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_offer_variation.const IS NOT NULL
AND product_invariable.variation = product_offer_variation.const
)
OR (
product_offer_variation.const IS NULL
AND product_invariable.variation IS NULL
)
)
AND (
(
product_offer_modification.const IS NOT NULL
AND product_invariable.modification = product_offer_modification.const
)
OR (
product_offer_modification.const IS NULL
AND product_invariable.modification IS NULL
)
)
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_info.article,
product_info.sort,
product_price.price,
product_price.currency,
product_price.quantity,
product_price.reserve,
product_offer_price.price,
product_offer_price.currency,
product_offer_quantity.quantity,
product_offer_quantity.reserve,
product_variation_price.price,
product_variation_price.currency,
product_variation_quantity.quantity,
product_variation_quantity.reserve,
product_modification_price.price,
product_modification_price.currency,
product_modification_quantity.quantity,
product_modification_quantity.reserve,
product.id,
product.event,
product_info.url,
product_trans.name,
product_active.active_from,
product_offer.id,
product_offer.value,
product_offer.postfix,
category_offer.reference,
product_offer_variation.id,
product_offer_variation.value,
product_offer_variation.postfix,
category_offer_variation.reference,
product_offer_modification.id,
product_offer_modification.value,
product_offer_modification.postfix,
category_offer_modification.reference,
product_offer_modification.article,
product_offer_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_info.url,
category_trans.name,
product_invariable.id,
project_profile_discount.value
ORDER BY
product_info.sort DESC,
product_modification_quantity.reserve DESC,
product_variation_quantity.reserve DESC,
product_offer_quantity.reserve DESC,
product_price.reserve DESC
LIMIT
4
SELECT product.id AS product_id, product.event AS product_event, product_info.url AS product_url, product_trans.name AS product_name, product_active.active_from, 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_offer_variation.id as product_variation_uid, product_offer_variation.value as product_variation_value, product_offer_variation.postfix as product_variation_postfix, category_offer_variation.reference as product_variation_reference, product_offer_modification.id as product_modification_uid, product_offer_modification.value as product_modification_value, product_offer_modification.postfix as product_modification_postfix, category_offer_modification.reference as product_modification_reference,
CASE
WHEN product_offer_modification.article IS NOT NULL
THEN product_offer_modification.article
WHEN product_offer_variation.article IS NOT NULL
THEN product_offer_variation.article
WHEN product_offer.article IS NOT NULL
THEN product_offer.article
WHEN product_info.article IS NOT NULL
THEN product_info.article
ELSE NULL
END AS product_article
, 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_offer_variation_image.ext IS NOT NULL
THEN JSONB_BUILD_OBJECT
(
'img_root', product_offer_variation_image.root,
'img', CONCAT ( '/upload/product_variation_images' , '/', product_offer_variation_image.name),
'img_ext', product_offer_variation_image.ext,
'img_cdn', product_offer_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,
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, category_info.url AS category_url, category_trans.name AS category_name, JSON_AGG
( DISTINCT
JSONB_BUILD_OBJECT
(
'field_sort', category_section_field.sort,
'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, product_invariable.id AS product_invariable_id, project_profile_discount.value AS project_discount FROM product INNER JOIN product_event product_event ON product_event.id = product.event INNER JOIN product_categories_product product_event_category ON
product_event_category.event = product.event AND
product_event_category.category = '01876af0-ddfc-70c3-ab25-5f85f55a9907' AND
product_event_category.root = true INNER JOIN product_active product_active ON product_active.event = product.event LEFT JOIN product_offer product_offer ON product_offer.event = product.event INNER JOIN users_profile project_profile ON
project_profile.id = '019469c3-700f-76a9-9b34-ccde7b4e6f49' LEFT JOIN product_info product_info ON product_info.product = product.id LEFT JOIN product_trans product_trans ON product_trans.event = product_event.id AND product_trans.local = 'ru' LEFT JOIN product_price product_price ON product_price.event = product_event.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 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_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 LEFT JOIN product_category_offers category_offer ON category_offer.id = product_offer.category_offer LEFT JOIN product_variation product_offer_variation ON product_offer_variation.offer = product_offer.id LEFT JOIN product_variation_images product_offer_variation_image ON product_offer_variation_image.variation = product_offer_variation.id AND product_offer_variation_image.root = true LEFT JOIN product_offer_images product_offer_images ON product_offer_variation_image.name IS NULL AND product_offer_images.offer = product_offer.id AND product_offer_images.root = true LEFT JOIN product_photo product_photo ON product_offer_images.name IS NULL AND product_photo.event = product_event.id AND product_photo.root = true LEFT JOIN product_category_variation category_offer_variation ON category_offer_variation.id = product_offer_variation.category_variation LEFT JOIN product_variation_price product_variation_price ON product_variation_price.variation = product_offer_variation.id LEFT JOIN product_variation_quantity product_variation_quantity ON product_variation_quantity.variation = product_offer_variation.id LEFT JOIN product_modification product_offer_modification ON product_offer_modification.variation = product_offer_variation.id LEFT JOIN product_modification_price product_modification_price ON product_modification_price.modification = product_offer_modification.id LEFT JOIN product_modification_quantity product_modification_quantity ON product_modification_quantity.modification = product_offer_modification.id LEFT JOIN product_category_modification category_offer_modification ON category_offer_modification.id = product_offer_modification.category_modification LEFT JOIN product_modification_images product_offer_modification_image ON product_offer_modification_image.modification = product_offer_modification.id AND product_offer_modification_image.root = true 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_offer_variation.const IS NOT NULL AND product_invariable.variation = product_offer_variation.const) OR
(product_offer_variation.const IS NULL AND product_invariable.variation IS NULL)
)
AND
(
(product_offer_modification.const IS NOT NULL AND product_invariable.modification = product_offer_modification.const) OR
(product_offer_modification.const IS NULL AND product_invariable.modification IS NULL)
)
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_info.article, product_info.sort, product_price.price, product_price.currency, product_price.quantity, product_price.reserve, product_offer_price.price, product_offer_price.currency, product_offer_quantity.quantity, product_offer_quantity.reserve, product_variation_price.price, product_variation_price.currency, product_variation_quantity.quantity, product_variation_quantity.reserve, product_modification_price.price, product_modification_price.currency, product_modification_quantity.quantity, product_modification_quantity.reserve, product.id, product.event, product_info.url, product_trans.name, product_active.active_from, product_offer.id, product_offer.value, product_offer.postfix, category_offer.reference, product_offer_variation.id, product_offer_variation.value, product_offer_variation.postfix, category_offer_variation.reference, product_offer_modification.id, product_offer_modification.value, product_offer_modification.postfix, category_offer_modification.reference, product_offer_modification.article, product_offer_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_info.url, category_trans.name, product_invariable.id, project_profile_discount.value ORDER BY product_info.sort DESC, product_modification_quantity.reserve DESC, product_variation_quantity.reserve DESC, product_offer_quantity.reserve DESC, product_price.reserve DESC LIMIT 4;
|
| 3 |
48.24 ms |
SELECT product_trans.name AS product_name, product_info.url, product_info.sort, category_info.url AS category_url 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 INNER JOIN product_active product_active ON
product_active.event = product.event AND
product_active.active IS TRUE
LEFT JOIN product_offer product_offer ON product_offer.event = product.event INNER JOIN product_categories_product product_event_category ON
product_event_category.event = product.event AND
product_event_category.category = ? AND
product_event_category.root = true 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_variation product_offer_variation ON product_offer_variation.id = (SELECT tmp_product_offer_variation.id FROM product_variation tmp_product_offer_variation WHERE tmp_product_offer_variation.offer = product_offer.id ORDER BY tmp_product_offer_variation.id DESC LIMIT 1) LEFT JOIN product_variation_quantity product_variation_quantity ON product_variation_quantity.variation = product_offer_variation.id LEFT JOIN product_variation_price product_variation_price ON product_variation_price.variation = product_offer_variation.id LEFT JOIN product_modification product_offer_modification ON product_offer_modification.variation = product_offer_variation.id LEFT JOIN product_modification_quantity product_modification_quantity ON product_modification_quantity.modification = product_offer_modification.id LEFT JOIN product_modification_price product_modification_price ON product_modification_price.modification = product_offer_modification.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
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_trans.name, product_info.url, product_info.sort, category_info.url 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 LIMIT 10
Parameters: [
"ru"
"01876af0-ddfc-70c3-ab25-5f85f55a9907"
]
SELECT product_trans.name AS product_name, product_info.url, product_info.sort, category_info.url AS category_url 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 INNER JOIN product_active product_active ON
product_active.event = product.event AND
product_active.active IS TRUE
LEFT JOIN product_offer product_offer ON product_offer.event = product.event INNER JOIN product_categories_product product_event_category ON
product_event_category.event = product.event AND
product_event_category.category = '01876af0-ddfc-70c3-ab25-5f85f55a9907' AND
product_event_category.root = true 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_variation product_offer_variation ON product_offer_variation.id = (SELECT tmp_product_offer_variation.id FROM product_variation tmp_product_offer_variation WHERE tmp_product_offer_variation.offer = product_offer.id ORDER BY tmp_product_offer_variation.id DESC LIMIT 1) LEFT JOIN product_variation_quantity product_variation_quantity ON product_variation_quantity.variation = product_offer_variation.id LEFT JOIN product_variation_price product_variation_price ON product_variation_price.variation = product_offer_variation.id LEFT JOIN product_modification product_offer_modification ON product_offer_modification.variation = product_offer_variation.id LEFT JOIN product_modification_quantity product_modification_quantity ON product_modification_quantity.modification = product_offer_modification.id LEFT JOIN product_modification_price product_modification_price ON product_modification_price.modification = product_offer_modification.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
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_trans.name, product_info.url, product_info.sort, category_info.url 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 LIMIT 10;
|
| 4 |
2.36 ms |
SELECT p0_.id AS sclr_0, p1_.name AS sclr_1, p0_.reference AS sclr_2 FROM product_category p2_ INNER JOIN product_category_offers p0_ ON (p0_.event = p2_.event) LEFT JOIN product_category_offers_trans p1_ ON (p1_.offer = p0_.id AND p1_.local = ?) WHERE p2_.id = ?
Parameters: [
"ru"
"01876af0-ddfc-70c3-ab25-5f85f55a9907"
]
SELECT p0_.id AS sclr_0, p1_.name AS sclr_1, p0_.reference AS sclr_2 FROM product_category p2_ INNER JOIN product_category_offers p0_ ON (p0_.event = p2_.event) LEFT JOIN product_category_offers_trans p1_ ON (p1_.offer = p0_.id AND p1_.local = 'ru') WHERE p2_.id = '01876af0-ddfc-70c3-ab25-5f85f55a9907';
|
| 5 |
7.34 ms |
SELECT DISTINCT
CASE
WHEN category_offers.reference = ?
THEN offer.value
WHEN category_variation.reference = ?
THEN variation.value
WHEN category_modification.reference = ?
THEN modification.value
ELSE NULL
END AS value
FROM product LEFT JOIN product_offer offer ON offer.event = product.event LEFT JOIN product_variation variation ON variation.offer = offer.id LEFT JOIN product_category_offers category_offers ON category_offers.id = offer.category_offer LEFT JOIN product_modification modification ON modification.variation = variation.id LEFT JOIN product_category_variation category_variation ON category_variation.id = variation.category_variation LEFT JOIN product_category_modification category_modification ON category_modification.id = modification.category_modification WHERE
(category_offers.reference = ? AND offer.value IN (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)) OR
(category_variation.reference = ? AND variation.value IN (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)) OR
(category_modification.reference = ? AND modification.value IN (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?))
ORDER BY value
Parameters: [
"tire_radius_field"
"tire_radius_field"
"tire_radius_field"
"tire_radius_field"
"12"
"12C"
"13"
"13C"
"14"
"14C"
"15"
"15C"
"16"
"16C"
"17"
"17C"
"18"
"18C"
"19"
"19C"
"20"
"20C"
"21"
"21C"
"22"
"22C"
"23"
"23C"
"24"
"24C"
"tire_radius_field"
"12"
"12C"
"13"
"13C"
"14"
"14C"
"15"
"15C"
"16"
"16C"
"17"
"17C"
"18"
"18C"
"19"
"19C"
"20"
"20C"
"21"
"21C"
"22"
"22C"
"23"
"23C"
"24"
"24C"
"tire_radius_field"
"12"
"12C"
"13"
"13C"
"14"
"14C"
"15"
"15C"
"16"
"16C"
"17"
"17C"
"18"
"18C"
"19"
"19C"
"20"
"20C"
"21"
"21C"
"22"
"22C"
"23"
"23C"
"24"
"24C"
]
SELECT
DISTINCT CASE WHEN category_offers.reference = ? THEN
offer.value
WHEN category_variation.reference = ? THEN
variation.value
WHEN category_modification.reference = ? THEN
modification.value
ELSE
NULL
END AS value
FROM
product
LEFT JOIN product_offer offer ON offer.event = product.event
LEFT JOIN product_variation variation ON variation.offer = offer.id
LEFT JOIN product_category_offers category_offers ON category_offers.id = offer.category_offer
LEFT JOIN product_modification modification ON modification.variation = variation.id
LEFT JOIN product_category_variation category_variation ON category_variation.id = variation.category_variation
LEFT JOIN product_category_modification category_modification ON category_modification.id = modification.category_modification
WHERE
(
category_offers.reference = ?
AND offer.value IN (
?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?,
?, ?, ?, ?, ?, ?, ?, ?, ?, ?
)
)
OR (
category_variation.reference = ?
AND variation.value IN (
?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?,
?, ?, ?, ?, ?, ?, ?, ?, ?, ?
)
)
OR (
category_modification.reference = ?
AND modification.value IN (
?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?,
?, ?, ?, ?, ?, ?, ?, ?, ?, ?
)
)
ORDER BY
value
SELECT DISTINCT
CASE
WHEN category_offers.reference = 'tire_radius_field'
THEN offer.value
WHEN category_variation.reference = 'tire_radius_field'
THEN variation.value
WHEN category_modification.reference = 'tire_radius_field'
THEN modification.value
ELSE NULL
END AS value
FROM product LEFT JOIN product_offer offer ON offer.event = product.event LEFT JOIN product_variation variation ON variation.offer = offer.id LEFT JOIN product_category_offers category_offers ON category_offers.id = offer.category_offer LEFT JOIN product_modification modification ON modification.variation = variation.id LEFT JOIN product_category_variation category_variation ON category_variation.id = variation.category_variation LEFT JOIN product_category_modification category_modification ON category_modification.id = modification.category_modification WHERE
(category_offers.reference = 'tire_radius_field' AND offer.value IN ('12', '12C', '13', '13C', '14', '14C', '15', '15C', '16', '16C', '17', '17C', '18', '18C', '19', '19C', '20', '20C', '21', '21C', '22', '22C', '23', '23C', '24', '24C')) OR
(category_variation.reference = 'tire_radius_field' AND variation.value IN ('12', '12C', '13', '13C', '14', '14C', '15', '15C', '16', '16C', '17', '17C', '18', '18C', '19', '19C', '20', '20C', '21', '21C', '22', '22C', '23', '23C', '24', '24C')) OR
(category_modification.reference = 'tire_radius_field' AND modification.value IN ('12', '12C', '13', '13C', '14', '14C', '15', '15C', '16', '16C', '17', '17C', '18', '18C', '19', '19C', '20', '20C', '21', '21C', '22', '22C', '23', '23C', '24', '24C'))
ORDER BY value;
|
| 6 |
2.53 ms |
SELECT p0_.id AS sclr_0, p1_.name AS sclr_1, p0_.reference AS sclr_2 FROM product_category_offers p2_ INNER JOIN product_category_variation p0_ ON (p0_.offer = p2_.id) LEFT JOIN product_category_variation_trans p1_ ON (p1_.variation = p0_.id AND p1_.local = ?) WHERE p2_.id = ?
Parameters: [
"ru"
"01876af0-ddff-727a-a933-76ca3c645d18"
]
SELECT p0_.id AS sclr_0, p1_.name AS sclr_1, p0_.reference AS sclr_2 FROM product_category_offers p2_ INNER JOIN product_category_variation p0_ ON (p0_.offer = p2_.id) LEFT JOIN product_category_variation_trans p1_ ON (p1_.variation = p0_.id AND p1_.local = 'ru') WHERE p2_.id = '01876af0-ddff-727a-a933-76ca3c645d18';
|
| 7 |
5.53 ms |
SELECT DISTINCT
CASE
WHEN category_offers.reference = ?
THEN offer.value
WHEN category_variation.reference = ?
THEN variation.value
WHEN category_modification.reference = ?
THEN modification.value
ELSE NULL
END AS value
FROM product LEFT JOIN product_offer offer ON offer.event = product.event LEFT JOIN product_variation variation ON variation.offer = offer.id LEFT JOIN product_category_offers category_offers ON category_offers.id = offer.category_offer LEFT JOIN product_modification modification ON modification.variation = variation.id LEFT JOIN product_category_variation category_variation ON category_variation.id = variation.category_variation LEFT JOIN product_category_modification category_modification ON category_modification.id = modification.category_modification WHERE
(category_offers.reference = ? AND offer.value IN (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)) OR
(category_variation.reference = ? AND variation.value IN (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)) OR
(category_modification.reference = ? AND modification.value IN (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?))
ORDER BY value
Parameters: [
"tire_width_field"
"tire_width_field"
"tire_width_field"
"tire_width_field"
30
31
32
33
34
35
36
37
145
155
165
175
185
195
205
215
225
235
245
255
265
275
285
295
305
315
325
"tire_width_field"
30
31
32
33
34
35
36
37
145
155
165
175
185
195
205
215
225
235
245
255
265
275
285
295
305
315
325
"tire_width_field"
30
31
32
33
34
35
36
37
145
155
165
175
185
195
205
215
225
235
245
255
265
275
285
295
305
315
325
]
SELECT
DISTINCT CASE WHEN category_offers.reference = ? THEN
offer.value
WHEN category_variation.reference = ? THEN
variation.value
WHEN category_modification.reference = ? THEN
modification.value
ELSE
NULL
END AS value
FROM
product
LEFT JOIN product_offer offer ON offer.event = product.event
LEFT JOIN product_variation variation ON variation.offer = offer.id
LEFT JOIN product_category_offers category_offers ON category_offers.id = offer.category_offer
LEFT JOIN product_modification modification ON modification.variation = variation.id
LEFT JOIN product_category_variation category_variation ON category_variation.id = variation.category_variation
LEFT JOIN product_category_modification category_modification ON category_modification.id = modification.category_modification
WHERE
(
category_offers.reference = ?
AND offer.value IN (
?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?,
?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?
)
)
OR (
category_variation.reference = ?
AND variation.value IN (
?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?,
?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?
)
)
OR (
category_modification.reference = ?
AND modification.value IN (
?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?,
?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?
)
)
ORDER BY
value
SELECT DISTINCT
CASE
WHEN category_offers.reference = 'tire_width_field'
THEN offer.value
WHEN category_variation.reference = 'tire_width_field'
THEN variation.value
WHEN category_modification.reference = 'tire_width_field'
THEN modification.value
ELSE NULL
END AS value
FROM product LEFT JOIN product_offer offer ON offer.event = product.event LEFT JOIN product_variation variation ON variation.offer = offer.id LEFT JOIN product_category_offers category_offers ON category_offers.id = offer.category_offer LEFT JOIN product_modification modification ON modification.variation = variation.id LEFT JOIN product_category_variation category_variation ON category_variation.id = variation.category_variation LEFT JOIN product_category_modification category_modification ON category_modification.id = modification.category_modification WHERE
(category_offers.reference = 'tire_width_field' AND offer.value IN (30, 31, 32, 33, 34, 35, 36, 37, 145, 155, 165, 175, 185, 195, 205, 215, 225, 235, 245, 255, 265, 275, 285, 295, 305, 315, 325)) OR
(category_variation.reference = 'tire_width_field' AND variation.value IN (30, 31, 32, 33, 34, 35, 36, 37, 145, 155, 165, 175, 185, 195, 205, 215, 225, 235, 245, 255, 265, 275, 285, 295, 305, 315, 325)) OR
(category_modification.reference = 'tire_width_field' AND modification.value IN (30, 31, 32, 33, 34, 35, 36, 37, 145, 155, 165, 175, 185, 195, 205, 215, 225, 235, 245, 255, 265, 275, 285, 295, 305, 315, 325))
ORDER BY value;
|
| 8 |
2.78 ms |
SELECT p0_.id AS sclr_0, p1_.name AS sclr_1, p0_.reference AS sclr_2 FROM product_category_variation p2_ INNER JOIN product_category_modification p0_ ON (p0_.variation = p2_.id) LEFT JOIN product_category_modification_trans p1_ ON (p1_.modification = p0_.id AND p1_.local = ?) WHERE p2_.id = ?
Parameters: [
"ru"
"01876af0-ddff-727a-a933-76ca3cb68e47"
]
SELECT p0_.id AS sclr_0, p1_.name AS sclr_1, p0_.reference AS sclr_2 FROM product_category_variation p2_ INNER JOIN product_category_modification p0_ ON (p0_.variation = p2_.id) LEFT JOIN product_category_modification_trans p1_ ON (p1_.modification = p0_.id AND p1_.local = 'ru') WHERE p2_.id = '01876af0-ddff-727a-a933-76ca3cb68e47';
|
| 9 |
5.23 ms |
SELECT DISTINCT
CASE
WHEN category_offers.reference = ?
THEN offer.value
WHEN category_variation.reference = ?
THEN variation.value
WHEN category_modification.reference = ?
THEN modification.value
ELSE NULL
END AS value
FROM product LEFT JOIN product_offer offer ON offer.event = product.event LEFT JOIN product_variation variation ON variation.offer = offer.id LEFT JOIN product_category_offers category_offers ON category_offers.id = offer.category_offer LEFT JOIN product_modification modification ON modification.variation = variation.id LEFT JOIN product_category_variation category_variation ON category_variation.id = variation.category_variation LEFT JOIN product_category_modification category_modification ON category_modification.id = modification.category_modification WHERE
(category_offers.reference = ? AND offer.value IN (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)) OR
(category_variation.reference = ? AND variation.value IN (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)) OR
(category_modification.reference = ? AND modification.value IN (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?))
ORDER BY value
Parameters: [
"tire_profile_field"
"tire_profile_field"
"tire_profile_field"
"tire_profile_field"
9.5
10.5
11.5
12.5
30
35
40
45
50
55
60
65
70
75
80
85
"tire_profile_field"
9.5
10.5
11.5
12.5
30
35
40
45
50
55
60
65
70
75
80
85
"tire_profile_field"
9.5
10.5
11.5
12.5
30
35
40
45
50
55
60
65
70
75
80
85
]
SELECT
DISTINCT CASE WHEN category_offers.reference = ? THEN
offer.value
WHEN category_variation.reference = ? THEN
variation.value
WHEN category_modification.reference = ? THEN
modification.value
ELSE
NULL
END AS value
FROM
product
LEFT JOIN product_offer offer ON offer.event = product.event
LEFT JOIN product_variation variation ON variation.offer = offer.id
LEFT JOIN product_category_offers category_offers ON category_offers.id = offer.category_offer
LEFT JOIN product_modification modification ON modification.variation = variation.id
LEFT JOIN product_category_variation category_variation ON category_variation.id = variation.category_variation
LEFT JOIN product_category_modification category_modification ON category_modification.id = modification.category_modification
WHERE
(
category_offers.reference = ?
AND offer.value IN (
?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?
)
)
OR (
category_variation.reference = ?
AND variation.value IN (
?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?
)
)
OR (
category_modification.reference = ?
AND modification.value IN (
?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?
)
)
ORDER BY
value
SELECT DISTINCT
CASE
WHEN category_offers.reference = 'tire_profile_field'
THEN offer.value
WHEN category_variation.reference = 'tire_profile_field'
THEN variation.value
WHEN category_modification.reference = 'tire_profile_field'
THEN modification.value
ELSE NULL
END AS value
FROM product LEFT JOIN product_offer offer ON offer.event = product.event LEFT JOIN product_variation variation ON variation.offer = offer.id LEFT JOIN product_category_offers category_offers ON category_offers.id = offer.category_offer LEFT JOIN product_modification modification ON modification.variation = variation.id LEFT JOIN product_category_variation category_variation ON category_variation.id = variation.category_variation LEFT JOIN product_category_modification category_modification ON category_modification.id = modification.category_modification WHERE
(category_offers.reference = 'tire_profile_field' AND offer.value IN (9.5, 10.5, 11.5, 12.5, 30, 35, 40, 45, 50, 55, 60, 65, 70, 75, 80, 85)) OR
(category_variation.reference = 'tire_profile_field' AND variation.value IN (9.5, 10.5, 11.5, 12.5, 30, 35, 40, 45, 50, 55, 60, 65, 70, 75, 80, 85)) OR
(category_modification.reference = 'tire_profile_field' AND modification.value IN (9.5, 10.5, 11.5, 12.5, 30, 35, 40, 45, 50, 55, 60, 65, 70, 75, 80, 85))
ORDER BY value;
|
| 10 |
0.97 ms |
SELECT category_section_field.id, category_section_field.const, category_section_field.type, category_section_field_trans.name FROM product_category category 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.filter = 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 = ? WHERE category.id = ? ORDER BY category_section.sort, category_section_field.sort
Parameters: [
"ru"
"01876af0-ddfc-70c3-ab25-5f85f55a9907"
]
SELECT category_section_field.id, category_section_field.const, category_section_field.type, category_section_field_trans.name FROM product_category category 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.filter = 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' WHERE category.id = '01876af0-ddfc-70c3-ab25-5f85f55a9907' ORDER BY category_section.sort, category_section_field.sort;
|
| 11 |
349.61 ms |
SELECT product.id AS product_id, product.event AS product_event, product_info.url AS product_url, product_trans.name AS product_name, product_active.active_from, 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_offer_variation.id as product_variation_uid, product_offer_variation.value as product_variation_value, product_offer_variation.postfix as product_variation_postfix, category_offer_variation.reference as product_variation_reference, product_offer_modification.id as product_modification_uid, product_offer_modification.value as product_modification_value, product_offer_modification.postfix as product_modification_postfix, category_offer_modification.reference as product_modification_reference,
CASE
WHEN product_offer_modification.article IS NOT NULL
THEN product_offer_modification.article
WHEN product_offer_variation.article IS NOT NULL
THEN product_offer_variation.article
WHEN product_offer.article IS NOT NULL
THEN product_offer.article
WHEN product_info.article IS NOT NULL
THEN product_info.article
ELSE NULL
END AS product_article
, 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_offer_variation_image.ext IS NOT NULL
THEN JSONB_BUILD_OBJECT
(
'img_root', product_offer_variation_image.root,
'img', CONCAT ( '/upload/product_variation_images' , '/', product_offer_variation_image.name),
'img_ext', product_offer_variation_image.ext,
'img_cdn', product_offer_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,
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, category_info.url AS category_url, category_trans.name AS category_name, JSON_AGG
( DISTINCT
JSONB_BUILD_OBJECT
(
'field_sort', category_section_field.sort,
'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, product_invariable.id AS product_invariable_id, project_profile_discount.value AS project_discount FROM product INNER JOIN product_event product_event ON product_event.id = product.event INNER JOIN product_categories_product product_event_category ON
product_event_category.event = product.event AND
product_event_category.category = ? AND
product_event_category.root = true INNER JOIN product_active product_active ON product_active.event = product.event LEFT JOIN product_offer product_offer ON product_offer.event = product.event INNER JOIN users_profile project_profile ON
project_profile.id = ? LEFT JOIN product_info product_info ON product_info.product = product.id LEFT JOIN product_trans product_trans ON product_trans.event = product_event.id AND product_trans.local = ? LEFT JOIN product_price product_price ON product_price.event = product_event.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 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_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 LEFT JOIN product_category_offers category_offer ON category_offer.id = product_offer.category_offer LEFT JOIN product_variation product_offer_variation ON product_offer_variation.offer = product_offer.id LEFT JOIN product_variation_images product_offer_variation_image ON product_offer_variation_image.variation = product_offer_variation.id AND product_offer_variation_image.root = true LEFT JOIN product_offer_images product_offer_images ON product_offer_variation_image.name IS NULL AND product_offer_images.offer = product_offer.id AND product_offer_images.root = true LEFT JOIN product_photo product_photo ON product_offer_images.name IS NULL AND product_photo.event = product_event.id AND product_photo.root = true LEFT JOIN product_category_variation category_offer_variation ON category_offer_variation.id = product_offer_variation.category_variation LEFT JOIN product_variation_price product_variation_price ON product_variation_price.variation = product_offer_variation.id LEFT JOIN product_variation_quantity product_variation_quantity ON product_variation_quantity.variation = product_offer_variation.id LEFT JOIN product_modification product_offer_modification ON product_offer_modification.variation = product_offer_variation.id LEFT JOIN product_modification_price product_modification_price ON product_modification_price.modification = product_offer_modification.id LEFT JOIN product_modification_quantity product_modification_quantity ON product_modification_quantity.modification = product_offer_modification.id LEFT JOIN product_category_modification category_offer_modification ON category_offer_modification.id = product_offer_modification.category_modification LEFT JOIN product_modification_images product_offer_modification_image ON product_offer_modification_image.modification = product_offer_modification.id AND product_offer_modification_image.root = true 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_offer_variation.const IS NOT NULL AND product_invariable.variation = product_offer_variation.const) OR
(product_offer_variation.const IS NULL AND product_invariable.variation IS NULL)
)
AND
(
(product_offer_modification.const IS NOT NULL AND product_invariable.modification = product_offer_modification.const) OR
(product_offer_modification.const IS NULL AND product_invariable.modification IS NULL)
)
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_info.article, product_info.sort, product_price.price, product_price.currency, product_price.quantity, product_price.reserve, product_offer_price.price, product_offer_price.currency, product_offer_quantity.quantity, product_offer_quantity.reserve, product_variation_price.price, product_variation_price.currency, product_variation_quantity.quantity, product_variation_quantity.reserve, product_modification_price.price, product_modification_price.currency, product_modification_quantity.quantity, product_modification_quantity.reserve, product.id, product.event, product_info.url, product_trans.name, product_active.active_from, product_offer.id, product_offer.value, product_offer.postfix, category_offer.reference, product_offer_variation.id, product_offer_variation.value, product_offer_variation.postfix, category_offer_variation.reference, product_offer_modification.id, product_offer_modification.value, product_offer_modification.postfix, category_offer_modification.reference, product_offer_modification.article, product_offer_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_info.url, category_trans.name, product_invariable.id, project_profile_discount.value ORDER BY product_info.sort DESC, product_modification_quantity.reserve DESC, product_variation_quantity.reserve DESC, product_offer_quantity.reserve DESC, product_price.reserve DESC LIMIT 4
Parameters: [
"018f4f55-53d4-7b35-bda7-fad6b852f2f6"
"019469c3-700f-76a9-9b34-ccde7b4e6f49"
"ru"
"ru"
"ru"
]
SELECT
product.id AS product_id,
product.event AS product_event,
product_info.url AS product_url,
product_trans.name AS product_name,
product_active.active_from,
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_offer_variation.id as product_variation_uid,
product_offer_variation.value as product_variation_value,
product_offer_variation.postfix as product_variation_postfix,
category_offer_variation.reference as product_variation_reference,
product_offer_modification.id as product_modification_uid,
product_offer_modification.value as product_modification_value,
product_offer_modification.postfix as product_modification_postfix,
category_offer_modification.reference as product_modification_reference,
CASE WHEN product_offer_modification.article IS NOT NULL THEN
product_offer_modification.article
WHEN product_offer_variation.article IS NOT NULL THEN
product_offer_variation.article
WHEN product_offer.article IS NOT NULL THEN
product_offer.article
WHEN product_info.article IS NOT NULL THEN
product_info.article
ELSE
NULL
END AS product_article,
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_offer_variation_image.ext IS NOT NULL THEN
JSONB_BUILD_OBJECT (
'img_root',
product_offer_variation_image.root,
'img',
CONCAT (
'/upload/product_variation_images',
'/', product_offer_variation_image.name
),
'img_ext',
product_offer_variation_image.ext,
'img_cdn',
product_offer_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,
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,
category_info.url AS category_url,
category_trans.name AS category_name,
JSON_AGG (
DISTINCT JSONB_BUILD_OBJECT (
'field_sort', category_section_field.sort,
'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,
product_invariable.id AS product_invariable_id,
project_profile_discount.value AS project_discount
FROM
product
INNER JOIN product_event product_event ON product_event.id = product.event
INNER JOIN product_categories_product product_event_category ON product_event_category.event = product.event
AND product_event_category.category = ?
AND product_event_category.root = true
INNER JOIN product_active product_active ON product_active.event = product.event
LEFT JOIN product_offer product_offer ON product_offer.event = product.event
INNER JOIN users_profile project_profile ON project_profile.id = ?
LEFT JOIN product_info product_info ON product_info.product = product.id
LEFT JOIN product_trans product_trans ON product_trans.event = product_event.id
AND product_trans.local = ?
LEFT JOIN product_price product_price ON product_price.event = product_event.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
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_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
LEFT JOIN product_category_offers category_offer ON category_offer.id = product_offer.category_offer
LEFT JOIN product_variation product_offer_variation ON product_offer_variation.offer = product_offer.id
LEFT JOIN product_variation_images product_offer_variation_image ON product_offer_variation_image.variation = product_offer_variation.id
AND product_offer_variation_image.root = true
LEFT JOIN product_offer_images product_offer_images ON product_offer_variation_image.name IS NULL
AND product_offer_images.offer = product_offer.id
AND product_offer_images.root = true
LEFT JOIN product_photo product_photo ON product_offer_images.name IS NULL
AND product_photo.event = product_event.id
AND product_photo.root = true
LEFT JOIN product_category_variation category_offer_variation ON category_offer_variation.id = product_offer_variation.category_variation
LEFT JOIN product_variation_price product_variation_price ON product_variation_price.variation = product_offer_variation.id
LEFT JOIN product_variation_quantity product_variation_quantity ON product_variation_quantity.variation = product_offer_variation.id
LEFT JOIN product_modification product_offer_modification ON product_offer_modification.variation = product_offer_variation.id
LEFT JOIN product_modification_price product_modification_price ON product_modification_price.modification = product_offer_modification.id
LEFT JOIN product_modification_quantity product_modification_quantity ON product_modification_quantity.modification = product_offer_modification.id
LEFT JOIN product_category_modification category_offer_modification ON category_offer_modification.id = product_offer_modification.category_modification
LEFT JOIN product_modification_images product_offer_modification_image ON product_offer_modification_image.modification = product_offer_modification.id
AND product_offer_modification_image.root = true
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_offer_variation.const IS NOT NULL
AND product_invariable.variation = product_offer_variation.const
)
OR (
product_offer_variation.const IS NULL
AND product_invariable.variation IS NULL
)
)
AND (
(
product_offer_modification.const IS NOT NULL
AND product_invariable.modification = product_offer_modification.const
)
OR (
product_offer_modification.const IS NULL
AND product_invariable.modification IS NULL
)
)
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_info.article,
product_info.sort,
product_price.price,
product_price.currency,
product_price.quantity,
product_price.reserve,
product_offer_price.price,
product_offer_price.currency,
product_offer_quantity.quantity,
product_offer_quantity.reserve,
product_variation_price.price,
product_variation_price.currency,
product_variation_quantity.quantity,
product_variation_quantity.reserve,
product_modification_price.price,
product_modification_price.currency,
product_modification_quantity.quantity,
product_modification_quantity.reserve,
product.id,
product.event,
product_info.url,
product_trans.name,
product_active.active_from,
product_offer.id,
product_offer.value,
product_offer.postfix,
category_offer.reference,
product_offer_variation.id,
product_offer_variation.value,
product_offer_variation.postfix,
category_offer_variation.reference,
product_offer_modification.id,
product_offer_modification.value,
product_offer_modification.postfix,
category_offer_modification.reference,
product_offer_modification.article,
product_offer_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_info.url,
category_trans.name,
product_invariable.id,
project_profile_discount.value
ORDER BY
product_info.sort DESC,
product_modification_quantity.reserve DESC,
product_variation_quantity.reserve DESC,
product_offer_quantity.reserve DESC,
product_price.reserve DESC
LIMIT
4
SELECT product.id AS product_id, product.event AS product_event, product_info.url AS product_url, product_trans.name AS product_name, product_active.active_from, 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_offer_variation.id as product_variation_uid, product_offer_variation.value as product_variation_value, product_offer_variation.postfix as product_variation_postfix, category_offer_variation.reference as product_variation_reference, product_offer_modification.id as product_modification_uid, product_offer_modification.value as product_modification_value, product_offer_modification.postfix as product_modification_postfix, category_offer_modification.reference as product_modification_reference,
CASE
WHEN product_offer_modification.article IS NOT NULL
THEN product_offer_modification.article
WHEN product_offer_variation.article IS NOT NULL
THEN product_offer_variation.article
WHEN product_offer.article IS NOT NULL
THEN product_offer.article
WHEN product_info.article IS NOT NULL
THEN product_info.article
ELSE NULL
END AS product_article
, 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_offer_variation_image.ext IS NOT NULL
THEN JSONB_BUILD_OBJECT
(
'img_root', product_offer_variation_image.root,
'img', CONCAT ( '/upload/product_variation_images' , '/', product_offer_variation_image.name),
'img_ext', product_offer_variation_image.ext,
'img_cdn', product_offer_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,
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, category_info.url AS category_url, category_trans.name AS category_name, JSON_AGG
( DISTINCT
JSONB_BUILD_OBJECT
(
'field_sort', category_section_field.sort,
'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, product_invariable.id AS product_invariable_id, project_profile_discount.value AS project_discount FROM product INNER JOIN product_event product_event ON product_event.id = product.event INNER JOIN product_categories_product product_event_category ON
product_event_category.event = product.event AND
product_event_category.category = '018f4f55-53d4-7b35-bda7-fad6b852f2f6' AND
product_event_category.root = true INNER JOIN product_active product_active ON product_active.event = product.event LEFT JOIN product_offer product_offer ON product_offer.event = product.event INNER JOIN users_profile project_profile ON
project_profile.id = '019469c3-700f-76a9-9b34-ccde7b4e6f49' LEFT JOIN product_info product_info ON product_info.product = product.id LEFT JOIN product_trans product_trans ON product_trans.event = product_event.id AND product_trans.local = 'ru' LEFT JOIN product_price product_price ON product_price.event = product_event.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 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_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 LEFT JOIN product_category_offers category_offer ON category_offer.id = product_offer.category_offer LEFT JOIN product_variation product_offer_variation ON product_offer_variation.offer = product_offer.id LEFT JOIN product_variation_images product_offer_variation_image ON product_offer_variation_image.variation = product_offer_variation.id AND product_offer_variation_image.root = true LEFT JOIN product_offer_images product_offer_images ON product_offer_variation_image.name IS NULL AND product_offer_images.offer = product_offer.id AND product_offer_images.root = true LEFT JOIN product_photo product_photo ON product_offer_images.name IS NULL AND product_photo.event = product_event.id AND product_photo.root = true LEFT JOIN product_category_variation category_offer_variation ON category_offer_variation.id = product_offer_variation.category_variation LEFT JOIN product_variation_price product_variation_price ON product_variation_price.variation = product_offer_variation.id LEFT JOIN product_variation_quantity product_variation_quantity ON product_variation_quantity.variation = product_offer_variation.id LEFT JOIN product_modification product_offer_modification ON product_offer_modification.variation = product_offer_variation.id LEFT JOIN product_modification_price product_modification_price ON product_modification_price.modification = product_offer_modification.id LEFT JOIN product_modification_quantity product_modification_quantity ON product_modification_quantity.modification = product_offer_modification.id LEFT JOIN product_category_modification category_offer_modification ON category_offer_modification.id = product_offer_modification.category_modification LEFT JOIN product_modification_images product_offer_modification_image ON product_offer_modification_image.modification = product_offer_modification.id AND product_offer_modification_image.root = true 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_offer_variation.const IS NOT NULL AND product_invariable.variation = product_offer_variation.const) OR
(product_offer_variation.const IS NULL AND product_invariable.variation IS NULL)
)
AND
(
(product_offer_modification.const IS NOT NULL AND product_invariable.modification = product_offer_modification.const) OR
(product_offer_modification.const IS NULL AND product_invariable.modification IS NULL)
)
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_info.article, product_info.sort, product_price.price, product_price.currency, product_price.quantity, product_price.reserve, product_offer_price.price, product_offer_price.currency, product_offer_quantity.quantity, product_offer_quantity.reserve, product_variation_price.price, product_variation_price.currency, product_variation_quantity.quantity, product_variation_quantity.reserve, product_modification_price.price, product_modification_price.currency, product_modification_quantity.quantity, product_modification_quantity.reserve, product.id, product.event, product_info.url, product_trans.name, product_active.active_from, product_offer.id, product_offer.value, product_offer.postfix, category_offer.reference, product_offer_variation.id, product_offer_variation.value, product_offer_variation.postfix, category_offer_variation.reference, product_offer_modification.id, product_offer_modification.value, product_offer_modification.postfix, category_offer_modification.reference, product_offer_modification.article, product_offer_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_info.url, category_trans.name, product_invariable.id, project_profile_discount.value ORDER BY product_info.sort DESC, product_modification_quantity.reserve DESC, product_variation_quantity.reserve DESC, product_offer_quantity.reserve DESC, product_price.reserve DESC LIMIT 4;
|
| 12 |
30.69 ms |
SELECT product_trans.name AS product_name, product_info.url, product_info.sort, category_info.url AS category_url 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 INNER JOIN product_active product_active ON
product_active.event = product.event AND
product_active.active IS TRUE
LEFT JOIN product_offer product_offer ON product_offer.event = product.event INNER JOIN product_categories_product product_event_category ON
product_event_category.event = product.event AND
product_event_category.category = ? AND
product_event_category.root = true 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_variation product_offer_variation ON product_offer_variation.id = (SELECT tmp_product_offer_variation.id FROM product_variation tmp_product_offer_variation WHERE tmp_product_offer_variation.offer = product_offer.id ORDER BY tmp_product_offer_variation.id DESC LIMIT 1) LEFT JOIN product_variation_quantity product_variation_quantity ON product_variation_quantity.variation = product_offer_variation.id LEFT JOIN product_variation_price product_variation_price ON product_variation_price.variation = product_offer_variation.id LEFT JOIN product_modification product_offer_modification ON product_offer_modification.variation = product_offer_variation.id LEFT JOIN product_modification_quantity product_modification_quantity ON product_modification_quantity.modification = product_offer_modification.id LEFT JOIN product_modification_price product_modification_price ON product_modification_price.modification = product_offer_modification.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
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_trans.name, product_info.url, product_info.sort, category_info.url 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 LIMIT 10
Parameters: [
"ru"
"018f4f55-53d4-7b35-bda7-fad6b852f2f6"
]
SELECT product_trans.name AS product_name, product_info.url, product_info.sort, category_info.url AS category_url 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 INNER JOIN product_active product_active ON
product_active.event = product.event AND
product_active.active IS TRUE
LEFT JOIN product_offer product_offer ON product_offer.event = product.event INNER JOIN product_categories_product product_event_category ON
product_event_category.event = product.event AND
product_event_category.category = '018f4f55-53d4-7b35-bda7-fad6b852f2f6' AND
product_event_category.root = true 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_variation product_offer_variation ON product_offer_variation.id = (SELECT tmp_product_offer_variation.id FROM product_variation tmp_product_offer_variation WHERE tmp_product_offer_variation.offer = product_offer.id ORDER BY tmp_product_offer_variation.id DESC LIMIT 1) LEFT JOIN product_variation_quantity product_variation_quantity ON product_variation_quantity.variation = product_offer_variation.id LEFT JOIN product_variation_price product_variation_price ON product_variation_price.variation = product_offer_variation.id LEFT JOIN product_modification product_offer_modification ON product_offer_modification.variation = product_offer_variation.id LEFT JOIN product_modification_quantity product_modification_quantity ON product_modification_quantity.modification = product_offer_modification.id LEFT JOIN product_modification_price product_modification_price ON product_modification_price.modification = product_offer_modification.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
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_trans.name, product_info.url, product_info.sort, category_info.url 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 LIMIT 10;
|
| 13 |
0.79 ms |
SELECT p0_.id AS sclr_0, p1_.name AS sclr_1, p0_.reference AS sclr_2 FROM product_category p2_ INNER JOIN product_category_offers p0_ ON (p0_.event = p2_.event) LEFT JOIN product_category_offers_trans p1_ ON (p1_.offer = p0_.id AND p1_.local = ?) WHERE p2_.id = ?
Parameters: [
"ru"
"018f4f55-53d4-7b35-bda7-fad6b852f2f6"
]
SELECT p0_.id AS sclr_0, p1_.name AS sclr_1, p0_.reference AS sclr_2 FROM product_category p2_ INNER JOIN product_category_offers p0_ ON (p0_.event = p2_.event) LEFT JOIN product_category_offers_trans p1_ ON (p1_.offer = p0_.id AND p1_.local = 'ru') WHERE p2_.id = '018f4f55-53d4-7b35-bda7-fad6b852f2f6';
|
| 14 |
0.63 ms |
SELECT p0_.id AS sclr_0, p1_.name AS sclr_1, p0_.reference AS sclr_2 FROM product_category_offers p2_ INNER JOIN product_category_variation p0_ ON (p0_.offer = p2_.id) LEFT JOIN product_category_variation_trans p1_ ON (p1_.variation = p0_.id AND p1_.local = ?) WHERE p2_.id = ?
Parameters: [
"ru"
"018f4f55-53e3-7f35-93f7-40bea0aeb585"
]
SELECT p0_.id AS sclr_0, p1_.name AS sclr_1, p0_.reference AS sclr_2 FROM product_category_offers p2_ INNER JOIN product_category_variation p0_ ON (p0_.offer = p2_.id) LEFT JOIN product_category_variation_trans p1_ ON (p1_.variation = p0_.id AND p1_.local = 'ru') WHERE p2_.id = '018f4f55-53e3-7f35-93f7-40bea0aeb585';
|
| 15 |
0.62 ms |
SELECT p0_.id AS sclr_0, p1_.name AS sclr_1, p0_.reference AS sclr_2 FROM product_category_variation p2_ INNER JOIN product_category_modification p0_ ON (p0_.variation = p2_.id) LEFT JOIN product_category_modification_trans p1_ ON (p1_.modification = p0_.id AND p1_.local = ?) WHERE p2_.id = ?
Parameters: [
"ru"
"018f4f55-53e4-7a45-8445-2e277e90380c"
]
SELECT p0_.id AS sclr_0, p1_.name AS sclr_1, p0_.reference AS sclr_2 FROM product_category_variation p2_ INNER JOIN product_category_modification p0_ ON (p0_.variation = p2_.id) LEFT JOIN product_category_modification_trans p1_ ON (p1_.modification = p0_.id AND p1_.local = 'ru') WHERE p2_.id = '018f4f55-53e4-7a45-8445-2e277e90380c';
|
| 16 |
1.06 ms |
SELECT category_section_field.id, category_section_field.const, category_section_field.type, category_section_field_trans.name FROM product_category category 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.filter = 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 = ? WHERE category.id = ? ORDER BY category_section.sort, category_section_field.sort
Parameters: [
"ru"
"018f4f55-53d4-7b35-bda7-fad6b852f2f6"
]
SELECT category_section_field.id, category_section_field.const, category_section_field.type, category_section_field_trans.name FROM product_category category 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.filter = 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' WHERE category.id = '018f4f55-53d4-7b35-bda7-fad6b852f2f6' ORDER BY category_section.sort, category_section_field.sort;
|
| 17 |
321.20 ms |
SELECT product.id AS product_id, product.event AS product_event, product_info.url AS product_url, product_trans.name AS product_name, product_active.active_from, 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_offer_variation.id as product_variation_uid, product_offer_variation.value as product_variation_value, product_offer_variation.postfix as product_variation_postfix, category_offer_variation.reference as product_variation_reference, product_offer_modification.id as product_modification_uid, product_offer_modification.value as product_modification_value, product_offer_modification.postfix as product_modification_postfix, category_offer_modification.reference as product_modification_reference,
CASE
WHEN product_offer_modification.article IS NOT NULL
THEN product_offer_modification.article
WHEN product_offer_variation.article IS NOT NULL
THEN product_offer_variation.article
WHEN product_offer.article IS NOT NULL
THEN product_offer.article
WHEN product_info.article IS NOT NULL
THEN product_info.article
ELSE NULL
END AS product_article
, 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_offer_variation_image.ext IS NOT NULL
THEN JSONB_BUILD_OBJECT
(
'img_root', product_offer_variation_image.root,
'img', CONCAT ( '/upload/product_variation_images' , '/', product_offer_variation_image.name),
'img_ext', product_offer_variation_image.ext,
'img_cdn', product_offer_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,
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, category_info.url AS category_url, category_trans.name AS category_name, JSON_AGG
( DISTINCT
JSONB_BUILD_OBJECT
(
'field_sort', category_section_field.sort,
'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, product_invariable.id AS product_invariable_id, project_profile_discount.value AS project_discount FROM product INNER JOIN product_event product_event ON product_event.id = product.event INNER JOIN product_categories_product product_event_category ON
product_event_category.event = product.event AND
product_event_category.category = ? AND
product_event_category.root = true INNER JOIN product_active product_active ON product_active.event = product.event LEFT JOIN product_offer product_offer ON product_offer.event = product.event INNER JOIN users_profile project_profile ON
project_profile.id = ? LEFT JOIN product_info product_info ON product_info.product = product.id LEFT JOIN product_trans product_trans ON product_trans.event = product_event.id AND product_trans.local = ? LEFT JOIN product_price product_price ON product_price.event = product_event.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 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_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 LEFT JOIN product_category_offers category_offer ON category_offer.id = product_offer.category_offer LEFT JOIN product_variation product_offer_variation ON product_offer_variation.offer = product_offer.id LEFT JOIN product_variation_images product_offer_variation_image ON product_offer_variation_image.variation = product_offer_variation.id AND product_offer_variation_image.root = true LEFT JOIN product_offer_images product_offer_images ON product_offer_variation_image.name IS NULL AND product_offer_images.offer = product_offer.id AND product_offer_images.root = true LEFT JOIN product_photo product_photo ON product_offer_images.name IS NULL AND product_photo.event = product_event.id AND product_photo.root = true LEFT JOIN product_category_variation category_offer_variation ON category_offer_variation.id = product_offer_variation.category_variation LEFT JOIN product_variation_price product_variation_price ON product_variation_price.variation = product_offer_variation.id LEFT JOIN product_variation_quantity product_variation_quantity ON product_variation_quantity.variation = product_offer_variation.id LEFT JOIN product_modification product_offer_modification ON product_offer_modification.variation = product_offer_variation.id LEFT JOIN product_modification_price product_modification_price ON product_modification_price.modification = product_offer_modification.id LEFT JOIN product_modification_quantity product_modification_quantity ON product_modification_quantity.modification = product_offer_modification.id LEFT JOIN product_category_modification category_offer_modification ON category_offer_modification.id = product_offer_modification.category_modification LEFT JOIN product_modification_images product_offer_modification_image ON product_offer_modification_image.modification = product_offer_modification.id AND product_offer_modification_image.root = true 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_offer_variation.const IS NOT NULL AND product_invariable.variation = product_offer_variation.const) OR
(product_offer_variation.const IS NULL AND product_invariable.variation IS NULL)
)
AND
(
(product_offer_modification.const IS NOT NULL AND product_invariable.modification = product_offer_modification.const) OR
(product_offer_modification.const IS NULL AND product_invariable.modification IS NULL)
)
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_info.article, product_info.sort, product_price.price, product_price.currency, product_price.quantity, product_price.reserve, product_offer_price.price, product_offer_price.currency, product_offer_quantity.quantity, product_offer_quantity.reserve, product_variation_price.price, product_variation_price.currency, product_variation_quantity.quantity, product_variation_quantity.reserve, product_modification_price.price, product_modification_price.currency, product_modification_quantity.quantity, product_modification_quantity.reserve, product.id, product.event, product_info.url, product_trans.name, product_active.active_from, product_offer.id, product_offer.value, product_offer.postfix, category_offer.reference, product_offer_variation.id, product_offer_variation.value, product_offer_variation.postfix, category_offer_variation.reference, product_offer_modification.id, product_offer_modification.value, product_offer_modification.postfix, category_offer_modification.reference, product_offer_modification.article, product_offer_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_info.url, category_trans.name, product_invariable.id, project_profile_discount.value ORDER BY product_info.sort DESC, product_modification_quantity.reserve DESC, product_variation_quantity.reserve DESC, product_offer_quantity.reserve DESC, product_price.reserve DESC LIMIT 4
Parameters: [
"01905ea6-805e-7a56-b53c-da41c19f9e38"
"019469c3-700f-76a9-9b34-ccde7b4e6f49"
"ru"
"ru"
"ru"
]
SELECT
product.id AS product_id,
product.event AS product_event,
product_info.url AS product_url,
product_trans.name AS product_name,
product_active.active_from,
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_offer_variation.id as product_variation_uid,
product_offer_variation.value as product_variation_value,
product_offer_variation.postfix as product_variation_postfix,
category_offer_variation.reference as product_variation_reference,
product_offer_modification.id as product_modification_uid,
product_offer_modification.value as product_modification_value,
product_offer_modification.postfix as product_modification_postfix,
category_offer_modification.reference as product_modification_reference,
CASE WHEN product_offer_modification.article IS NOT NULL THEN
product_offer_modification.article
WHEN product_offer_variation.article IS NOT NULL THEN
product_offer_variation.article
WHEN product_offer.article IS NOT NULL THEN
product_offer.article
WHEN product_info.article IS NOT NULL THEN
product_info.article
ELSE
NULL
END AS product_article,
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_offer_variation_image.ext IS NOT NULL THEN
JSONB_BUILD_OBJECT (
'img_root',
product_offer_variation_image.root,
'img',
CONCAT (
'/upload/product_variation_images',
'/', product_offer_variation_image.name
),
'img_ext',
product_offer_variation_image.ext,
'img_cdn',
product_offer_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,
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,
category_info.url AS category_url,
category_trans.name AS category_name,
JSON_AGG (
DISTINCT JSONB_BUILD_OBJECT (
'field_sort', category_section_field.sort,
'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,
product_invariable.id AS product_invariable_id,
project_profile_discount.value AS project_discount
FROM
product
INNER JOIN product_event product_event ON product_event.id = product.event
INNER JOIN product_categories_product product_event_category ON product_event_category.event = product.event
AND product_event_category.category = ?
AND product_event_category.root = true
INNER JOIN product_active product_active ON product_active.event = product.event
LEFT JOIN product_offer product_offer ON product_offer.event = product.event
INNER JOIN users_profile project_profile ON project_profile.id = ?
LEFT JOIN product_info product_info ON product_info.product = product.id
LEFT JOIN product_trans product_trans ON product_trans.event = product_event.id
AND product_trans.local = ?
LEFT JOIN product_price product_price ON product_price.event = product_event.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
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_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
LEFT JOIN product_category_offers category_offer ON category_offer.id = product_offer.category_offer
LEFT JOIN product_variation product_offer_variation ON product_offer_variation.offer = product_offer.id
LEFT JOIN product_variation_images product_offer_variation_image ON product_offer_variation_image.variation = product_offer_variation.id
AND product_offer_variation_image.root = true
LEFT JOIN product_offer_images product_offer_images ON product_offer_variation_image.name IS NULL
AND product_offer_images.offer = product_offer.id
AND product_offer_images.root = true
LEFT JOIN product_photo product_photo ON product_offer_images.name IS NULL
AND product_photo.event = product_event.id
AND product_photo.root = true
LEFT JOIN product_category_variation category_offer_variation ON category_offer_variation.id = product_offer_variation.category_variation
LEFT JOIN product_variation_price product_variation_price ON product_variation_price.variation = product_offer_variation.id
LEFT JOIN product_variation_quantity product_variation_quantity ON product_variation_quantity.variation = product_offer_variation.id
LEFT JOIN product_modification product_offer_modification ON product_offer_modification.variation = product_offer_variation.id
LEFT JOIN product_modification_price product_modification_price ON product_modification_price.modification = product_offer_modification.id
LEFT JOIN product_modification_quantity product_modification_quantity ON product_modification_quantity.modification = product_offer_modification.id
LEFT JOIN product_category_modification category_offer_modification ON category_offer_modification.id = product_offer_modification.category_modification
LEFT JOIN product_modification_images product_offer_modification_image ON product_offer_modification_image.modification = product_offer_modification.id
AND product_offer_modification_image.root = true
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_offer_variation.const IS NOT NULL
AND product_invariable.variation = product_offer_variation.const
)
OR (
product_offer_variation.const IS NULL
AND product_invariable.variation IS NULL
)
)
AND (
(
product_offer_modification.const IS NOT NULL
AND product_invariable.modification = product_offer_modification.const
)
OR (
product_offer_modification.const IS NULL
AND product_invariable.modification IS NULL
)
)
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_info.article,
product_info.sort,
product_price.price,
product_price.currency,
product_price.quantity,
product_price.reserve,
product_offer_price.price,
product_offer_price.currency,
product_offer_quantity.quantity,
product_offer_quantity.reserve,
product_variation_price.price,
product_variation_price.currency,
product_variation_quantity.quantity,
product_variation_quantity.reserve,
product_modification_price.price,
product_modification_price.currency,
product_modification_quantity.quantity,
product_modification_quantity.reserve,
product.id,
product.event,
product_info.url,
product_trans.name,
product_active.active_from,
product_offer.id,
product_offer.value,
product_offer.postfix,
category_offer.reference,
product_offer_variation.id,
product_offer_variation.value,
product_offer_variation.postfix,
category_offer_variation.reference,
product_offer_modification.id,
product_offer_modification.value,
product_offer_modification.postfix,
category_offer_modification.reference,
product_offer_modification.article,
product_offer_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_info.url,
category_trans.name,
product_invariable.id,
project_profile_discount.value
ORDER BY
product_info.sort DESC,
product_modification_quantity.reserve DESC,
product_variation_quantity.reserve DESC,
product_offer_quantity.reserve DESC,
product_price.reserve DESC
LIMIT
4
SELECT product.id AS product_id, product.event AS product_event, product_info.url AS product_url, product_trans.name AS product_name, product_active.active_from, 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_offer_variation.id as product_variation_uid, product_offer_variation.value as product_variation_value, product_offer_variation.postfix as product_variation_postfix, category_offer_variation.reference as product_variation_reference, product_offer_modification.id as product_modification_uid, product_offer_modification.value as product_modification_value, product_offer_modification.postfix as product_modification_postfix, category_offer_modification.reference as product_modification_reference,
CASE
WHEN product_offer_modification.article IS NOT NULL
THEN product_offer_modification.article
WHEN product_offer_variation.article IS NOT NULL
THEN product_offer_variation.article
WHEN product_offer.article IS NOT NULL
THEN product_offer.article
WHEN product_info.article IS NOT NULL
THEN product_info.article
ELSE NULL
END AS product_article
, 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_offer_variation_image.ext IS NOT NULL
THEN JSONB_BUILD_OBJECT
(
'img_root', product_offer_variation_image.root,
'img', CONCAT ( '/upload/product_variation_images' , '/', product_offer_variation_image.name),
'img_ext', product_offer_variation_image.ext,
'img_cdn', product_offer_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,
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, category_info.url AS category_url, category_trans.name AS category_name, JSON_AGG
( DISTINCT
JSONB_BUILD_OBJECT
(
'field_sort', category_section_field.sort,
'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, product_invariable.id AS product_invariable_id, project_profile_discount.value AS project_discount FROM product INNER JOIN product_event product_event ON product_event.id = product.event INNER JOIN product_categories_product product_event_category ON
product_event_category.event = product.event AND
product_event_category.category = '01905ea6-805e-7a56-b53c-da41c19f9e38' AND
product_event_category.root = true INNER JOIN product_active product_active ON product_active.event = product.event LEFT JOIN product_offer product_offer ON product_offer.event = product.event INNER JOIN users_profile project_profile ON
project_profile.id = '019469c3-700f-76a9-9b34-ccde7b4e6f49' LEFT JOIN product_info product_info ON product_info.product = product.id LEFT JOIN product_trans product_trans ON product_trans.event = product_event.id AND product_trans.local = 'ru' LEFT JOIN product_price product_price ON product_price.event = product_event.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 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_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 LEFT JOIN product_category_offers category_offer ON category_offer.id = product_offer.category_offer LEFT JOIN product_variation product_offer_variation ON product_offer_variation.offer = product_offer.id LEFT JOIN product_variation_images product_offer_variation_image ON product_offer_variation_image.variation = product_offer_variation.id AND product_offer_variation_image.root = true LEFT JOIN product_offer_images product_offer_images ON product_offer_variation_image.name IS NULL AND product_offer_images.offer = product_offer.id AND product_offer_images.root = true LEFT JOIN product_photo product_photo ON product_offer_images.name IS NULL AND product_photo.event = product_event.id AND product_photo.root = true LEFT JOIN product_category_variation category_offer_variation ON category_offer_variation.id = product_offer_variation.category_variation LEFT JOIN product_variation_price product_variation_price ON product_variation_price.variation = product_offer_variation.id LEFT JOIN product_variation_quantity product_variation_quantity ON product_variation_quantity.variation = product_offer_variation.id LEFT JOIN product_modification product_offer_modification ON product_offer_modification.variation = product_offer_variation.id LEFT JOIN product_modification_price product_modification_price ON product_modification_price.modification = product_offer_modification.id LEFT JOIN product_modification_quantity product_modification_quantity ON product_modification_quantity.modification = product_offer_modification.id LEFT JOIN product_category_modification category_offer_modification ON category_offer_modification.id = product_offer_modification.category_modification LEFT JOIN product_modification_images product_offer_modification_image ON product_offer_modification_image.modification = product_offer_modification.id AND product_offer_modification_image.root = true 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_offer_variation.const IS NOT NULL AND product_invariable.variation = product_offer_variation.const) OR
(product_offer_variation.const IS NULL AND product_invariable.variation IS NULL)
)
AND
(
(product_offer_modification.const IS NOT NULL AND product_invariable.modification = product_offer_modification.const) OR
(product_offer_modification.const IS NULL AND product_invariable.modification IS NULL)
)
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_info.article, product_info.sort, product_price.price, product_price.currency, product_price.quantity, product_price.reserve, product_offer_price.price, product_offer_price.currency, product_offer_quantity.quantity, product_offer_quantity.reserve, product_variation_price.price, product_variation_price.currency, product_variation_quantity.quantity, product_variation_quantity.reserve, product_modification_price.price, product_modification_price.currency, product_modification_quantity.quantity, product_modification_quantity.reserve, product.id, product.event, product_info.url, product_trans.name, product_active.active_from, product_offer.id, product_offer.value, product_offer.postfix, category_offer.reference, product_offer_variation.id, product_offer_variation.value, product_offer_variation.postfix, category_offer_variation.reference, product_offer_modification.id, product_offer_modification.value, product_offer_modification.postfix, category_offer_modification.reference, product_offer_modification.article, product_offer_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_info.url, category_trans.name, product_invariable.id, project_profile_discount.value ORDER BY product_info.sort DESC, product_modification_quantity.reserve DESC, product_variation_quantity.reserve DESC, product_offer_quantity.reserve DESC, product_price.reserve DESC LIMIT 4;
|
| 18 |
27.76 ms |
SELECT product_trans.name AS product_name, product_info.url, product_info.sort, category_info.url AS category_url 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 INNER JOIN product_active product_active ON
product_active.event = product.event AND
product_active.active IS TRUE
LEFT JOIN product_offer product_offer ON product_offer.event = product.event INNER JOIN product_categories_product product_event_category ON
product_event_category.event = product.event AND
product_event_category.category = ? AND
product_event_category.root = true 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_variation product_offer_variation ON product_offer_variation.id = (SELECT tmp_product_offer_variation.id FROM product_variation tmp_product_offer_variation WHERE tmp_product_offer_variation.offer = product_offer.id ORDER BY tmp_product_offer_variation.id DESC LIMIT 1) LEFT JOIN product_variation_quantity product_variation_quantity ON product_variation_quantity.variation = product_offer_variation.id LEFT JOIN product_variation_price product_variation_price ON product_variation_price.variation = product_offer_variation.id LEFT JOIN product_modification product_offer_modification ON product_offer_modification.variation = product_offer_variation.id LEFT JOIN product_modification_quantity product_modification_quantity ON product_modification_quantity.modification = product_offer_modification.id LEFT JOIN product_modification_price product_modification_price ON product_modification_price.modification = product_offer_modification.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
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_trans.name, product_info.url, product_info.sort, category_info.url 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 LIMIT 10
Parameters: [
"ru"
"01905ea6-805e-7a56-b53c-da41c19f9e38"
]
SELECT product_trans.name AS product_name, product_info.url, product_info.sort, category_info.url AS category_url 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 INNER JOIN product_active product_active ON
product_active.event = product.event AND
product_active.active IS TRUE
LEFT JOIN product_offer product_offer ON product_offer.event = product.event INNER JOIN product_categories_product product_event_category ON
product_event_category.event = product.event AND
product_event_category.category = '01905ea6-805e-7a56-b53c-da41c19f9e38' AND
product_event_category.root = true 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_variation product_offer_variation ON product_offer_variation.id = (SELECT tmp_product_offer_variation.id FROM product_variation tmp_product_offer_variation WHERE tmp_product_offer_variation.offer = product_offer.id ORDER BY tmp_product_offer_variation.id DESC LIMIT 1) LEFT JOIN product_variation_quantity product_variation_quantity ON product_variation_quantity.variation = product_offer_variation.id LEFT JOIN product_variation_price product_variation_price ON product_variation_price.variation = product_offer_variation.id LEFT JOIN product_modification product_offer_modification ON product_offer_modification.variation = product_offer_variation.id LEFT JOIN product_modification_quantity product_modification_quantity ON product_modification_quantity.modification = product_offer_modification.id LEFT JOIN product_modification_price product_modification_price ON product_modification_price.modification = product_offer_modification.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
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_trans.name, product_info.url, product_info.sort, category_info.url 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 LIMIT 10;
|
| 19 |
0.69 ms |
SELECT p0_.id AS sclr_0, p1_.name AS sclr_1, p0_.reference AS sclr_2 FROM product_category p2_ INNER JOIN product_category_offers p0_ ON (p0_.event = p2_.event) LEFT JOIN product_category_offers_trans p1_ ON (p1_.offer = p0_.id AND p1_.local = ?) WHERE p2_.id = ?
Parameters: [
"ru"
"01905ea6-805e-7a56-b53c-da41c19f9e38"
]
SELECT p0_.id AS sclr_0, p1_.name AS sclr_1, p0_.reference AS sclr_2 FROM product_category p2_ INNER JOIN product_category_offers p0_ ON (p0_.event = p2_.event) LEFT JOIN product_category_offers_trans p1_ ON (p1_.offer = p0_.id AND p1_.local = 'ru') WHERE p2_.id = '01905ea6-805e-7a56-b53c-da41c19f9e38';
|
| 20 |
0.58 ms |
SELECT p0_.id AS sclr_0, p1_.name AS sclr_1, p0_.reference AS sclr_2 FROM product_category_offers p2_ INNER JOIN product_category_variation p0_ ON (p0_.offer = p2_.id) LEFT JOIN product_category_variation_trans p1_ ON (p1_.variation = p0_.id AND p1_.local = ?) WHERE p2_.id = ?
Parameters: [
"ru"
"01905ea6-8067-7bcf-8bbf-faa0041e07d2"
]
SELECT p0_.id AS sclr_0, p1_.name AS sclr_1, p0_.reference AS sclr_2 FROM product_category_offers p2_ INNER JOIN product_category_variation p0_ ON (p0_.offer = p2_.id) LEFT JOIN product_category_variation_trans p1_ ON (p1_.variation = p0_.id AND p1_.local = 'ru') WHERE p2_.id = '01905ea6-8067-7bcf-8bbf-faa0041e07d2';
|
| 21 |
0.61 ms |
SELECT p0_.id AS sclr_0, p1_.name AS sclr_1, p0_.reference AS sclr_2 FROM product_category_variation p2_ INNER JOIN product_category_modification p0_ ON (p0_.variation = p2_.id) LEFT JOIN product_category_modification_trans p1_ ON (p1_.modification = p0_.id AND p1_.local = ?) WHERE p2_.id = ?
Parameters: [
"ru"
"01905ea6-8068-712f-92a7-80ae9386495d"
]
SELECT p0_.id AS sclr_0, p1_.name AS sclr_1, p0_.reference AS sclr_2 FROM product_category_variation p2_ INNER JOIN product_category_modification p0_ ON (p0_.variation = p2_.id) LEFT JOIN product_category_modification_trans p1_ ON (p1_.modification = p0_.id AND p1_.local = 'ru') WHERE p2_.id = '01905ea6-8068-712f-92a7-80ae9386495d';
|
| 22 |
0.99 ms |
SELECT category_section_field.id, category_section_field.const, category_section_field.type, category_section_field_trans.name FROM product_category category 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.filter = 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 = ? WHERE category.id = ? ORDER BY category_section.sort, category_section_field.sort
Parameters: [
"ru"
"01905ea6-805e-7a56-b53c-da41c19f9e38"
]
SELECT category_section_field.id, category_section_field.const, category_section_field.type, category_section_field_trans.name FROM product_category category 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.filter = 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' WHERE category.id = '01905ea6-805e-7a56-b53c-da41c19f9e38' ORDER BY category_section.sort, category_section_field.sort;
|
| 23 |
319.37 ms |
SELECT product.id AS product_id, product.event AS product_event, product_info.url AS product_url, product_trans.name AS product_name, product_active.active_from, 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_offer_variation.id as product_variation_uid, product_offer_variation.value as product_variation_value, product_offer_variation.postfix as product_variation_postfix, category_offer_variation.reference as product_variation_reference, product_offer_modification.id as product_modification_uid, product_offer_modification.value as product_modification_value, product_offer_modification.postfix as product_modification_postfix, category_offer_modification.reference as product_modification_reference,
CASE
WHEN product_offer_modification.article IS NOT NULL
THEN product_offer_modification.article
WHEN product_offer_variation.article IS NOT NULL
THEN product_offer_variation.article
WHEN product_offer.article IS NOT NULL
THEN product_offer.article
WHEN product_info.article IS NOT NULL
THEN product_info.article
ELSE NULL
END AS product_article
, 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_offer_variation_image.ext IS NOT NULL
THEN JSONB_BUILD_OBJECT
(
'img_root', product_offer_variation_image.root,
'img', CONCAT ( '/upload/product_variation_images' , '/', product_offer_variation_image.name),
'img_ext', product_offer_variation_image.ext,
'img_cdn', product_offer_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,
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, category_info.url AS category_url, category_trans.name AS category_name, JSON_AGG
( DISTINCT
JSONB_BUILD_OBJECT
(
'field_sort', category_section_field.sort,
'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, product_invariable.id AS product_invariable_id, project_profile_discount.value AS project_discount FROM product INNER JOIN product_event product_event ON product_event.id = product.event INNER JOIN product_categories_product product_event_category ON
product_event_category.event = product.event AND
product_event_category.category = ? AND
product_event_category.root = true INNER JOIN product_active product_active ON product_active.event = product.event LEFT JOIN product_offer product_offer ON product_offer.event = product.event INNER JOIN users_profile project_profile ON
project_profile.id = ? LEFT JOIN product_info product_info ON product_info.product = product.id LEFT JOIN product_trans product_trans ON product_trans.event = product_event.id AND product_trans.local = ? LEFT JOIN product_price product_price ON product_price.event = product_event.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 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_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 LEFT JOIN product_category_offers category_offer ON category_offer.id = product_offer.category_offer LEFT JOIN product_variation product_offer_variation ON product_offer_variation.offer = product_offer.id LEFT JOIN product_variation_images product_offer_variation_image ON product_offer_variation_image.variation = product_offer_variation.id AND product_offer_variation_image.root = true LEFT JOIN product_offer_images product_offer_images ON product_offer_variation_image.name IS NULL AND product_offer_images.offer = product_offer.id AND product_offer_images.root = true LEFT JOIN product_photo product_photo ON product_offer_images.name IS NULL AND product_photo.event = product_event.id AND product_photo.root = true LEFT JOIN product_category_variation category_offer_variation ON category_offer_variation.id = product_offer_variation.category_variation LEFT JOIN product_variation_price product_variation_price ON product_variation_price.variation = product_offer_variation.id LEFT JOIN product_variation_quantity product_variation_quantity ON product_variation_quantity.variation = product_offer_variation.id LEFT JOIN product_modification product_offer_modification ON product_offer_modification.variation = product_offer_variation.id LEFT JOIN product_modification_price product_modification_price ON product_modification_price.modification = product_offer_modification.id LEFT JOIN product_modification_quantity product_modification_quantity ON product_modification_quantity.modification = product_offer_modification.id LEFT JOIN product_category_modification category_offer_modification ON category_offer_modification.id = product_offer_modification.category_modification LEFT JOIN product_modification_images product_offer_modification_image ON product_offer_modification_image.modification = product_offer_modification.id AND product_offer_modification_image.root = true 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_offer_variation.const IS NOT NULL AND product_invariable.variation = product_offer_variation.const) OR
(product_offer_variation.const IS NULL AND product_invariable.variation IS NULL)
)
AND
(
(product_offer_modification.const IS NOT NULL AND product_invariable.modification = product_offer_modification.const) OR
(product_offer_modification.const IS NULL AND product_invariable.modification IS NULL)
)
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_info.article, product_info.sort, product_price.price, product_price.currency, product_price.quantity, product_price.reserve, product_offer_price.price, product_offer_price.currency, product_offer_quantity.quantity, product_offer_quantity.reserve, product_variation_price.price, product_variation_price.currency, product_variation_quantity.quantity, product_variation_quantity.reserve, product_modification_price.price, product_modification_price.currency, product_modification_quantity.quantity, product_modification_quantity.reserve, product.id, product.event, product_info.url, product_trans.name, product_active.active_from, product_offer.id, product_offer.value, product_offer.postfix, category_offer.reference, product_offer_variation.id, product_offer_variation.value, product_offer_variation.postfix, category_offer_variation.reference, product_offer_modification.id, product_offer_modification.value, product_offer_modification.postfix, category_offer_modification.reference, product_offer_modification.article, product_offer_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_info.url, category_trans.name, product_invariable.id, project_profile_discount.value ORDER BY product_info.sort DESC, product_modification_quantity.reserve DESC, product_variation_quantity.reserve DESC, product_offer_quantity.reserve DESC, product_price.reserve DESC LIMIT 4
Parameters: [
"0190ef25-1d90-79d0-8d42-1c2baf9319c2"
"019469c3-700f-76a9-9b34-ccde7b4e6f49"
"ru"
"ru"
"ru"
]
SELECT
product.id AS product_id,
product.event AS product_event,
product_info.url AS product_url,
product_trans.name AS product_name,
product_active.active_from,
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_offer_variation.id as product_variation_uid,
product_offer_variation.value as product_variation_value,
product_offer_variation.postfix as product_variation_postfix,
category_offer_variation.reference as product_variation_reference,
product_offer_modification.id as product_modification_uid,
product_offer_modification.value as product_modification_value,
product_offer_modification.postfix as product_modification_postfix,
category_offer_modification.reference as product_modification_reference,
CASE WHEN product_offer_modification.article IS NOT NULL THEN
product_offer_modification.article
WHEN product_offer_variation.article IS NOT NULL THEN
product_offer_variation.article
WHEN product_offer.article IS NOT NULL THEN
product_offer.article
WHEN product_info.article IS NOT NULL THEN
product_info.article
ELSE
NULL
END AS product_article,
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_offer_variation_image.ext IS NOT NULL THEN
JSONB_BUILD_OBJECT (
'img_root',
product_offer_variation_image.root,
'img',
CONCAT (
'/upload/product_variation_images',
'/', product_offer_variation_image.name
),
'img_ext',
product_offer_variation_image.ext,
'img_cdn',
product_offer_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,
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,
category_info.url AS category_url,
category_trans.name AS category_name,
JSON_AGG (
DISTINCT JSONB_BUILD_OBJECT (
'field_sort', category_section_field.sort,
'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,
product_invariable.id AS product_invariable_id,
project_profile_discount.value AS project_discount
FROM
product
INNER JOIN product_event product_event ON product_event.id = product.event
INNER JOIN product_categories_product product_event_category ON product_event_category.event = product.event
AND product_event_category.category = ?
AND product_event_category.root = true
INNER JOIN product_active product_active ON product_active.event = product.event
LEFT JOIN product_offer product_offer ON product_offer.event = product.event
INNER JOIN users_profile project_profile ON project_profile.id = ?
LEFT JOIN product_info product_info ON product_info.product = product.id
LEFT JOIN product_trans product_trans ON product_trans.event = product_event.id
AND product_trans.local = ?
LEFT JOIN product_price product_price ON product_price.event = product_event.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
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_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
LEFT JOIN product_category_offers category_offer ON category_offer.id = product_offer.category_offer
LEFT JOIN product_variation product_offer_variation ON product_offer_variation.offer = product_offer.id
LEFT JOIN product_variation_images product_offer_variation_image ON product_offer_variation_image.variation = product_offer_variation.id
AND product_offer_variation_image.root = true
LEFT JOIN product_offer_images product_offer_images ON product_offer_variation_image.name IS NULL
AND product_offer_images.offer = product_offer.id
AND product_offer_images.root = true
LEFT JOIN product_photo product_photo ON product_offer_images.name IS NULL
AND product_photo.event = product_event.id
AND product_photo.root = true
LEFT JOIN product_category_variation category_offer_variation ON category_offer_variation.id = product_offer_variation.category_variation
LEFT JOIN product_variation_price product_variation_price ON product_variation_price.variation = product_offer_variation.id
LEFT JOIN product_variation_quantity product_variation_quantity ON product_variation_quantity.variation = product_offer_variation.id
LEFT JOIN product_modification product_offer_modification ON product_offer_modification.variation = product_offer_variation.id
LEFT JOIN product_modification_price product_modification_price ON product_modification_price.modification = product_offer_modification.id
LEFT JOIN product_modification_quantity product_modification_quantity ON product_modification_quantity.modification = product_offer_modification.id
LEFT JOIN product_category_modification category_offer_modification ON category_offer_modification.id = product_offer_modification.category_modification
LEFT JOIN product_modification_images product_offer_modification_image ON product_offer_modification_image.modification = product_offer_modification.id
AND product_offer_modification_image.root = true
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_offer_variation.const IS NOT NULL
AND product_invariable.variation = product_offer_variation.const
)
OR (
product_offer_variation.const IS NULL
AND product_invariable.variation IS NULL
)
)
AND (
(
product_offer_modification.const IS NOT NULL
AND product_invariable.modification = product_offer_modification.const
)
OR (
product_offer_modification.const IS NULL
AND product_invariable.modification IS NULL
)
)
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_info.article,
product_info.sort,
product_price.price,
product_price.currency,
product_price.quantity,
product_price.reserve,
product_offer_price.price,
product_offer_price.currency,
product_offer_quantity.quantity,
product_offer_quantity.reserve,
product_variation_price.price,
product_variation_price.currency,
product_variation_quantity.quantity,
product_variation_quantity.reserve,
product_modification_price.price,
product_modification_price.currency,
product_modification_quantity.quantity,
product_modification_quantity.reserve,
product.id,
product.event,
product_info.url,
product_trans.name,
product_active.active_from,
product_offer.id,
product_offer.value,
product_offer.postfix,
category_offer.reference,
product_offer_variation.id,
product_offer_variation.value,
product_offer_variation.postfix,
category_offer_variation.reference,
product_offer_modification.id,
product_offer_modification.value,
product_offer_modification.postfix,
category_offer_modification.reference,
product_offer_modification.article,
product_offer_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_info.url,
category_trans.name,
product_invariable.id,
project_profile_discount.value
ORDER BY
product_info.sort DESC,
product_modification_quantity.reserve DESC,
product_variation_quantity.reserve DESC,
product_offer_quantity.reserve DESC,
product_price.reserve DESC
LIMIT
4
SELECT product.id AS product_id, product.event AS product_event, product_info.url AS product_url, product_trans.name AS product_name, product_active.active_from, 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_offer_variation.id as product_variation_uid, product_offer_variation.value as product_variation_value, product_offer_variation.postfix as product_variation_postfix, category_offer_variation.reference as product_variation_reference, product_offer_modification.id as product_modification_uid, product_offer_modification.value as product_modification_value, product_offer_modification.postfix as product_modification_postfix, category_offer_modification.reference as product_modification_reference,
CASE
WHEN product_offer_modification.article IS NOT NULL
THEN product_offer_modification.article
WHEN product_offer_variation.article IS NOT NULL
THEN product_offer_variation.article
WHEN product_offer.article IS NOT NULL
THEN product_offer.article
WHEN product_info.article IS NOT NULL
THEN product_info.article
ELSE NULL
END AS product_article
, 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_offer_variation_image.ext IS NOT NULL
THEN JSONB_BUILD_OBJECT
(
'img_root', product_offer_variation_image.root,
'img', CONCAT ( '/upload/product_variation_images' , '/', product_offer_variation_image.name),
'img_ext', product_offer_variation_image.ext,
'img_cdn', product_offer_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,
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, category_info.url AS category_url, category_trans.name AS category_name, JSON_AGG
( DISTINCT
JSONB_BUILD_OBJECT
(
'field_sort', category_section_field.sort,
'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, product_invariable.id AS product_invariable_id, project_profile_discount.value AS project_discount FROM product INNER JOIN product_event product_event ON product_event.id = product.event INNER JOIN product_categories_product product_event_category ON
product_event_category.event = product.event AND
product_event_category.category = '0190ef25-1d90-79d0-8d42-1c2baf9319c2' AND
product_event_category.root = true INNER JOIN product_active product_active ON product_active.event = product.event LEFT JOIN product_offer product_offer ON product_offer.event = product.event INNER JOIN users_profile project_profile ON
project_profile.id = '019469c3-700f-76a9-9b34-ccde7b4e6f49' LEFT JOIN product_info product_info ON product_info.product = product.id LEFT JOIN product_trans product_trans ON product_trans.event = product_event.id AND product_trans.local = 'ru' LEFT JOIN product_price product_price ON product_price.event = product_event.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 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_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 LEFT JOIN product_category_offers category_offer ON category_offer.id = product_offer.category_offer LEFT JOIN product_variation product_offer_variation ON product_offer_variation.offer = product_offer.id LEFT JOIN product_variation_images product_offer_variation_image ON product_offer_variation_image.variation = product_offer_variation.id AND product_offer_variation_image.root = true LEFT JOIN product_offer_images product_offer_images ON product_offer_variation_image.name IS NULL AND product_offer_images.offer = product_offer.id AND product_offer_images.root = true LEFT JOIN product_photo product_photo ON product_offer_images.name IS NULL AND product_photo.event = product_event.id AND product_photo.root = true LEFT JOIN product_category_variation category_offer_variation ON category_offer_variation.id = product_offer_variation.category_variation LEFT JOIN product_variation_price product_variation_price ON product_variation_price.variation = product_offer_variation.id LEFT JOIN product_variation_quantity product_variation_quantity ON product_variation_quantity.variation = product_offer_variation.id LEFT JOIN product_modification product_offer_modification ON product_offer_modification.variation = product_offer_variation.id LEFT JOIN product_modification_price product_modification_price ON product_modification_price.modification = product_offer_modification.id LEFT JOIN product_modification_quantity product_modification_quantity ON product_modification_quantity.modification = product_offer_modification.id LEFT JOIN product_category_modification category_offer_modification ON category_offer_modification.id = product_offer_modification.category_modification LEFT JOIN product_modification_images product_offer_modification_image ON product_offer_modification_image.modification = product_offer_modification.id AND product_offer_modification_image.root = true 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_offer_variation.const IS NOT NULL AND product_invariable.variation = product_offer_variation.const) OR
(product_offer_variation.const IS NULL AND product_invariable.variation IS NULL)
)
AND
(
(product_offer_modification.const IS NOT NULL AND product_invariable.modification = product_offer_modification.const) OR
(product_offer_modification.const IS NULL AND product_invariable.modification IS NULL)
)
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_info.article, product_info.sort, product_price.price, product_price.currency, product_price.quantity, product_price.reserve, product_offer_price.price, product_offer_price.currency, product_offer_quantity.quantity, product_offer_quantity.reserve, product_variation_price.price, product_variation_price.currency, product_variation_quantity.quantity, product_variation_quantity.reserve, product_modification_price.price, product_modification_price.currency, product_modification_quantity.quantity, product_modification_quantity.reserve, product.id, product.event, product_info.url, product_trans.name, product_active.active_from, product_offer.id, product_offer.value, product_offer.postfix, category_offer.reference, product_offer_variation.id, product_offer_variation.value, product_offer_variation.postfix, category_offer_variation.reference, product_offer_modification.id, product_offer_modification.value, product_offer_modification.postfix, category_offer_modification.reference, product_offer_modification.article, product_offer_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_info.url, category_trans.name, product_invariable.id, project_profile_discount.value ORDER BY product_info.sort DESC, product_modification_quantity.reserve DESC, product_variation_quantity.reserve DESC, product_offer_quantity.reserve DESC, product_price.reserve DESC LIMIT 4;
|
| 24 |
26.62 ms |
SELECT product_trans.name AS product_name, product_info.url, product_info.sort, category_info.url AS category_url 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 INNER JOIN product_active product_active ON
product_active.event = product.event AND
product_active.active IS TRUE
LEFT JOIN product_offer product_offer ON product_offer.event = product.event INNER JOIN product_categories_product product_event_category ON
product_event_category.event = product.event AND
product_event_category.category = ? AND
product_event_category.root = true 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_variation product_offer_variation ON product_offer_variation.id = (SELECT tmp_product_offer_variation.id FROM product_variation tmp_product_offer_variation WHERE tmp_product_offer_variation.offer = product_offer.id ORDER BY tmp_product_offer_variation.id DESC LIMIT 1) LEFT JOIN product_variation_quantity product_variation_quantity ON product_variation_quantity.variation = product_offer_variation.id LEFT JOIN product_variation_price product_variation_price ON product_variation_price.variation = product_offer_variation.id LEFT JOIN product_modification product_offer_modification ON product_offer_modification.variation = product_offer_variation.id LEFT JOIN product_modification_quantity product_modification_quantity ON product_modification_quantity.modification = product_offer_modification.id LEFT JOIN product_modification_price product_modification_price ON product_modification_price.modification = product_offer_modification.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
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_trans.name, product_info.url, product_info.sort, category_info.url 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 LIMIT 10
Parameters: [
"ru"
"0190ef25-1d90-79d0-8d42-1c2baf9319c2"
]
SELECT product_trans.name AS product_name, product_info.url, product_info.sort, category_info.url AS category_url 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 INNER JOIN product_active product_active ON
product_active.event = product.event AND
product_active.active IS TRUE
LEFT JOIN product_offer product_offer ON product_offer.event = product.event INNER JOIN product_categories_product product_event_category ON
product_event_category.event = product.event AND
product_event_category.category = '0190ef25-1d90-79d0-8d42-1c2baf9319c2' AND
product_event_category.root = true 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_variation product_offer_variation ON product_offer_variation.id = (SELECT tmp_product_offer_variation.id FROM product_variation tmp_product_offer_variation WHERE tmp_product_offer_variation.offer = product_offer.id ORDER BY tmp_product_offer_variation.id DESC LIMIT 1) LEFT JOIN product_variation_quantity product_variation_quantity ON product_variation_quantity.variation = product_offer_variation.id LEFT JOIN product_variation_price product_variation_price ON product_variation_price.variation = product_offer_variation.id LEFT JOIN product_modification product_offer_modification ON product_offer_modification.variation = product_offer_variation.id LEFT JOIN product_modification_quantity product_modification_quantity ON product_modification_quantity.modification = product_offer_modification.id LEFT JOIN product_modification_price product_modification_price ON product_modification_price.modification = product_offer_modification.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
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_trans.name, product_info.url, product_info.sort, category_info.url 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 LIMIT 10;
|
| 25 |
0.73 ms |
SELECT p0_.id AS sclr_0, p1_.name AS sclr_1, p0_.reference AS sclr_2 FROM product_category p2_ INNER JOIN product_category_offers p0_ ON (p0_.event = p2_.event) LEFT JOIN product_category_offers_trans p1_ ON (p1_.offer = p0_.id AND p1_.local = ?) WHERE p2_.id = ?
Parameters: [
"ru"
"0190ef25-1d90-79d0-8d42-1c2baf9319c2"
]
SELECT p0_.id AS sclr_0, p1_.name AS sclr_1, p0_.reference AS sclr_2 FROM product_category p2_ INNER JOIN product_category_offers p0_ ON (p0_.event = p2_.event) LEFT JOIN product_category_offers_trans p1_ ON (p1_.offer = p0_.id AND p1_.local = 'ru') WHERE p2_.id = '0190ef25-1d90-79d0-8d42-1c2baf9319c2';
|
| 26 |
0.59 ms |
SELECT p0_.id AS sclr_0, p1_.name AS sclr_1, p0_.reference AS sclr_2 FROM product_category_offers p2_ INNER JOIN product_category_variation p0_ ON (p0_.offer = p2_.id) LEFT JOIN product_category_variation_trans p1_ ON (p1_.variation = p0_.id AND p1_.local = ?) WHERE p2_.id = ?
Parameters: [
"ru"
"0190ef25-1da2-7c6c-8070-038b6aebf0ec"
]
SELECT p0_.id AS sclr_0, p1_.name AS sclr_1, p0_.reference AS sclr_2 FROM product_category_offers p2_ INNER JOIN product_category_variation p0_ ON (p0_.offer = p2_.id) LEFT JOIN product_category_variation_trans p1_ ON (p1_.variation = p0_.id AND p1_.local = 'ru') WHERE p2_.id = '0190ef25-1da2-7c6c-8070-038b6aebf0ec';
|
| 27 |
0.61 ms |
SELECT p0_.id AS sclr_0, p1_.name AS sclr_1, p0_.reference AS sclr_2 FROM product_category_variation p2_ INNER JOIN product_category_modification p0_ ON (p0_.variation = p2_.id) LEFT JOIN product_category_modification_trans p1_ ON (p1_.modification = p0_.id AND p1_.local = ?) WHERE p2_.id = ?
Parameters: [
"ru"
"0190ef25-1da4-7985-9d8a-465bff6d5417"
]
SELECT p0_.id AS sclr_0, p1_.name AS sclr_1, p0_.reference AS sclr_2 FROM product_category_variation p2_ INNER JOIN product_category_modification p0_ ON (p0_.variation = p2_.id) LEFT JOIN product_category_modification_trans p1_ ON (p1_.modification = p0_.id AND p1_.local = 'ru') WHERE p2_.id = '0190ef25-1da4-7985-9d8a-465bff6d5417';
|
| 28 |
1.01 ms |
SELECT category_section_field.id, category_section_field.const, category_section_field.type, category_section_field_trans.name FROM product_category category 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.filter = 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 = ? WHERE category.id = ? ORDER BY category_section.sort, category_section_field.sort
Parameters: [
"ru"
"0190ef25-1d90-79d0-8d42-1c2baf9319c2"
]
SELECT category_section_field.id, category_section_field.const, category_section_field.type, category_section_field_trans.name FROM product_category category 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.filter = 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' WHERE category.id = '0190ef25-1d90-79d0-8d42-1c2baf9319c2' ORDER BY category_section.sort, category_section_field.sort;
|
| 29 |
281.13 ms |
SELECT product.id AS product_id, product.event AS product_event, product_info.url AS product_url, product_trans.name AS product_name, product_active.active_from, 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_offer_variation.id as product_variation_uid, product_offer_variation.value as product_variation_value, product_offer_variation.postfix as product_variation_postfix, category_offer_variation.reference as product_variation_reference, product_offer_modification.id as product_modification_uid, product_offer_modification.value as product_modification_value, product_offer_modification.postfix as product_modification_postfix, category_offer_modification.reference as product_modification_reference,
CASE
WHEN product_offer_modification.article IS NOT NULL
THEN product_offer_modification.article
WHEN product_offer_variation.article IS NOT NULL
THEN product_offer_variation.article
WHEN product_offer.article IS NOT NULL
THEN product_offer.article
WHEN product_info.article IS NOT NULL
THEN product_info.article
ELSE NULL
END AS product_article
, 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_offer_variation_image.ext IS NOT NULL
THEN JSONB_BUILD_OBJECT
(
'img_root', product_offer_variation_image.root,
'img', CONCAT ( '/upload/product_variation_images' , '/', product_offer_variation_image.name),
'img_ext', product_offer_variation_image.ext,
'img_cdn', product_offer_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,
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, category_info.url AS category_url, category_trans.name AS category_name, JSON_AGG
( DISTINCT
JSONB_BUILD_OBJECT
(
'field_sort', category_section_field.sort,
'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, product_invariable.id AS product_invariable_id, project_profile_discount.value AS project_discount FROM product INNER JOIN product_event product_event ON product_event.id = product.event INNER JOIN product_categories_product product_event_category ON
product_event_category.event = product.event AND
product_event_category.category = ? AND
product_event_category.root = true INNER JOIN product_active product_active ON product_active.event = product.event LEFT JOIN product_offer product_offer ON product_offer.event = product.event INNER JOIN users_profile project_profile ON
project_profile.id = ? LEFT JOIN product_info product_info ON product_info.product = product.id LEFT JOIN product_trans product_trans ON product_trans.event = product_event.id AND product_trans.local = ? LEFT JOIN product_price product_price ON product_price.event = product_event.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 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_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 LEFT JOIN product_category_offers category_offer ON category_offer.id = product_offer.category_offer LEFT JOIN product_variation product_offer_variation ON product_offer_variation.offer = product_offer.id LEFT JOIN product_variation_images product_offer_variation_image ON product_offer_variation_image.variation = product_offer_variation.id AND product_offer_variation_image.root = true LEFT JOIN product_offer_images product_offer_images ON product_offer_variation_image.name IS NULL AND product_offer_images.offer = product_offer.id AND product_offer_images.root = true LEFT JOIN product_photo product_photo ON product_offer_images.name IS NULL AND product_photo.event = product_event.id AND product_photo.root = true LEFT JOIN product_category_variation category_offer_variation ON category_offer_variation.id = product_offer_variation.category_variation LEFT JOIN product_variation_price product_variation_price ON product_variation_price.variation = product_offer_variation.id LEFT JOIN product_variation_quantity product_variation_quantity ON product_variation_quantity.variation = product_offer_variation.id LEFT JOIN product_modification product_offer_modification ON product_offer_modification.variation = product_offer_variation.id LEFT JOIN product_modification_price product_modification_price ON product_modification_price.modification = product_offer_modification.id LEFT JOIN product_modification_quantity product_modification_quantity ON product_modification_quantity.modification = product_offer_modification.id LEFT JOIN product_category_modification category_offer_modification ON category_offer_modification.id = product_offer_modification.category_modification LEFT JOIN product_modification_images product_offer_modification_image ON product_offer_modification_image.modification = product_offer_modification.id AND product_offer_modification_image.root = true 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_offer_variation.const IS NOT NULL AND product_invariable.variation = product_offer_variation.const) OR
(product_offer_variation.const IS NULL AND product_invariable.variation IS NULL)
)
AND
(
(product_offer_modification.const IS NOT NULL AND product_invariable.modification = product_offer_modification.const) OR
(product_offer_modification.const IS NULL AND product_invariable.modification IS NULL)
)
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_info.article, product_info.sort, product_price.price, product_price.currency, product_price.quantity, product_price.reserve, product_offer_price.price, product_offer_price.currency, product_offer_quantity.quantity, product_offer_quantity.reserve, product_variation_price.price, product_variation_price.currency, product_variation_quantity.quantity, product_variation_quantity.reserve, product_modification_price.price, product_modification_price.currency, product_modification_quantity.quantity, product_modification_quantity.reserve, product.id, product.event, product_info.url, product_trans.name, product_active.active_from, product_offer.id, product_offer.value, product_offer.postfix, category_offer.reference, product_offer_variation.id, product_offer_variation.value, product_offer_variation.postfix, category_offer_variation.reference, product_offer_modification.id, product_offer_modification.value, product_offer_modification.postfix, category_offer_modification.reference, product_offer_modification.article, product_offer_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_info.url, category_trans.name, product_invariable.id, project_profile_discount.value ORDER BY product_info.sort DESC, product_modification_quantity.reserve DESC, product_variation_quantity.reserve DESC, product_offer_quantity.reserve DESC, product_price.reserve DESC LIMIT 4
Parameters: [
"0190ef2a-66cd-70ea-b793-2eb962175a4c"
"019469c3-700f-76a9-9b34-ccde7b4e6f49"
"ru"
"ru"
"ru"
]
SELECT
product.id AS product_id,
product.event AS product_event,
product_info.url AS product_url,
product_trans.name AS product_name,
product_active.active_from,
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_offer_variation.id as product_variation_uid,
product_offer_variation.value as product_variation_value,
product_offer_variation.postfix as product_variation_postfix,
category_offer_variation.reference as product_variation_reference,
product_offer_modification.id as product_modification_uid,
product_offer_modification.value as product_modification_value,
product_offer_modification.postfix as product_modification_postfix,
category_offer_modification.reference as product_modification_reference,
CASE WHEN product_offer_modification.article IS NOT NULL THEN
product_offer_modification.article
WHEN product_offer_variation.article IS NOT NULL THEN
product_offer_variation.article
WHEN product_offer.article IS NOT NULL THEN
product_offer.article
WHEN product_info.article IS NOT NULL THEN
product_info.article
ELSE
NULL
END AS product_article,
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_offer_variation_image.ext IS NOT NULL THEN
JSONB_BUILD_OBJECT (
'img_root',
product_offer_variation_image.root,
'img',
CONCAT (
'/upload/product_variation_images',
'/', product_offer_variation_image.name
),
'img_ext',
product_offer_variation_image.ext,
'img_cdn',
product_offer_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,
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,
category_info.url AS category_url,
category_trans.name AS category_name,
JSON_AGG (
DISTINCT JSONB_BUILD_OBJECT (
'field_sort', category_section_field.sort,
'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,
product_invariable.id AS product_invariable_id,
project_profile_discount.value AS project_discount
FROM
product
INNER JOIN product_event product_event ON product_event.id = product.event
INNER JOIN product_categories_product product_event_category ON product_event_category.event = product.event
AND product_event_category.category = ?
AND product_event_category.root = true
INNER JOIN product_active product_active ON product_active.event = product.event
LEFT JOIN product_offer product_offer ON product_offer.event = product.event
INNER JOIN users_profile project_profile ON project_profile.id = ?
LEFT JOIN product_info product_info ON product_info.product = product.id
LEFT JOIN product_trans product_trans ON product_trans.event = product_event.id
AND product_trans.local = ?
LEFT JOIN product_price product_price ON product_price.event = product_event.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
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_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
LEFT JOIN product_category_offers category_offer ON category_offer.id = product_offer.category_offer
LEFT JOIN product_variation product_offer_variation ON product_offer_variation.offer = product_offer.id
LEFT JOIN product_variation_images product_offer_variation_image ON product_offer_variation_image.variation = product_offer_variation.id
AND product_offer_variation_image.root = true
LEFT JOIN product_offer_images product_offer_images ON product_offer_variation_image.name IS NULL
AND product_offer_images.offer = product_offer.id
AND product_offer_images.root = true
LEFT JOIN product_photo product_photo ON product_offer_images.name IS NULL
AND product_photo.event = product_event.id
AND product_photo.root = true
LEFT JOIN product_category_variation category_offer_variation ON category_offer_variation.id = product_offer_variation.category_variation
LEFT JOIN product_variation_price product_variation_price ON product_variation_price.variation = product_offer_variation.id
LEFT JOIN product_variation_quantity product_variation_quantity ON product_variation_quantity.variation = product_offer_variation.id
LEFT JOIN product_modification product_offer_modification ON product_offer_modification.variation = product_offer_variation.id
LEFT JOIN product_modification_price product_modification_price ON product_modification_price.modification = product_offer_modification.id
LEFT JOIN product_modification_quantity product_modification_quantity ON product_modification_quantity.modification = product_offer_modification.id
LEFT JOIN product_category_modification category_offer_modification ON category_offer_modification.id = product_offer_modification.category_modification
LEFT JOIN product_modification_images product_offer_modification_image ON product_offer_modification_image.modification = product_offer_modification.id
AND product_offer_modification_image.root = true
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_offer_variation.const IS NOT NULL
AND product_invariable.variation = product_offer_variation.const
)
OR (
product_offer_variation.const IS NULL
AND product_invariable.variation IS NULL
)
)
AND (
(
product_offer_modification.const IS NOT NULL
AND product_invariable.modification = product_offer_modification.const
)
OR (
product_offer_modification.const IS NULL
AND product_invariable.modification IS NULL
)
)
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_info.article,
product_info.sort,
product_price.price,
product_price.currency,
product_price.quantity,
product_price.reserve,
product_offer_price.price,
product_offer_price.currency,
product_offer_quantity.quantity,
product_offer_quantity.reserve,
product_variation_price.price,
product_variation_price.currency,
product_variation_quantity.quantity,
product_variation_quantity.reserve,
product_modification_price.price,
product_modification_price.currency,
product_modification_quantity.quantity,
product_modification_quantity.reserve,
product.id,
product.event,
product_info.url,
product_trans.name,
product_active.active_from,
product_offer.id,
product_offer.value,
product_offer.postfix,
category_offer.reference,
product_offer_variation.id,
product_offer_variation.value,
product_offer_variation.postfix,
category_offer_variation.reference,
product_offer_modification.id,
product_offer_modification.value,
product_offer_modification.postfix,
category_offer_modification.reference,
product_offer_modification.article,
product_offer_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_info.url,
category_trans.name,
product_invariable.id,
project_profile_discount.value
ORDER BY
product_info.sort DESC,
product_modification_quantity.reserve DESC,
product_variation_quantity.reserve DESC,
product_offer_quantity.reserve DESC,
product_price.reserve DESC
LIMIT
4
SELECT product.id AS product_id, product.event AS product_event, product_info.url AS product_url, product_trans.name AS product_name, product_active.active_from, 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_offer_variation.id as product_variation_uid, product_offer_variation.value as product_variation_value, product_offer_variation.postfix as product_variation_postfix, category_offer_variation.reference as product_variation_reference, product_offer_modification.id as product_modification_uid, product_offer_modification.value as product_modification_value, product_offer_modification.postfix as product_modification_postfix, category_offer_modification.reference as product_modification_reference,
CASE
WHEN product_offer_modification.article IS NOT NULL
THEN product_offer_modification.article
WHEN product_offer_variation.article IS NOT NULL
THEN product_offer_variation.article
WHEN product_offer.article IS NOT NULL
THEN product_offer.article
WHEN product_info.article IS NOT NULL
THEN product_info.article
ELSE NULL
END AS product_article
, 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_offer_variation_image.ext IS NOT NULL
THEN JSONB_BUILD_OBJECT
(
'img_root', product_offer_variation_image.root,
'img', CONCAT ( '/upload/product_variation_images' , '/', product_offer_variation_image.name),
'img_ext', product_offer_variation_image.ext,
'img_cdn', product_offer_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,
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, category_info.url AS category_url, category_trans.name AS category_name, JSON_AGG
( DISTINCT
JSONB_BUILD_OBJECT
(
'field_sort', category_section_field.sort,
'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, product_invariable.id AS product_invariable_id, project_profile_discount.value AS project_discount FROM product INNER JOIN product_event product_event ON product_event.id = product.event INNER JOIN product_categories_product product_event_category ON
product_event_category.event = product.event AND
product_event_category.category = '0190ef2a-66cd-70ea-b793-2eb962175a4c' AND
product_event_category.root = true INNER JOIN product_active product_active ON product_active.event = product.event LEFT JOIN product_offer product_offer ON product_offer.event = product.event INNER JOIN users_profile project_profile ON
project_profile.id = '019469c3-700f-76a9-9b34-ccde7b4e6f49' LEFT JOIN product_info product_info ON product_info.product = product.id LEFT JOIN product_trans product_trans ON product_trans.event = product_event.id AND product_trans.local = 'ru' LEFT JOIN product_price product_price ON product_price.event = product_event.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 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_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 LEFT JOIN product_category_offers category_offer ON category_offer.id = product_offer.category_offer LEFT JOIN product_variation product_offer_variation ON product_offer_variation.offer = product_offer.id LEFT JOIN product_variation_images product_offer_variation_image ON product_offer_variation_image.variation = product_offer_variation.id AND product_offer_variation_image.root = true LEFT JOIN product_offer_images product_offer_images ON product_offer_variation_image.name IS NULL AND product_offer_images.offer = product_offer.id AND product_offer_images.root = true LEFT JOIN product_photo product_photo ON product_offer_images.name IS NULL AND product_photo.event = product_event.id AND product_photo.root = true LEFT JOIN product_category_variation category_offer_variation ON category_offer_variation.id = product_offer_variation.category_variation LEFT JOIN product_variation_price product_variation_price ON product_variation_price.variation = product_offer_variation.id LEFT JOIN product_variation_quantity product_variation_quantity ON product_variation_quantity.variation = product_offer_variation.id LEFT JOIN product_modification product_offer_modification ON product_offer_modification.variation = product_offer_variation.id LEFT JOIN product_modification_price product_modification_price ON product_modification_price.modification = product_offer_modification.id LEFT JOIN product_modification_quantity product_modification_quantity ON product_modification_quantity.modification = product_offer_modification.id LEFT JOIN product_category_modification category_offer_modification ON category_offer_modification.id = product_offer_modification.category_modification LEFT JOIN product_modification_images product_offer_modification_image ON product_offer_modification_image.modification = product_offer_modification.id AND product_offer_modification_image.root = true 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_offer_variation.const IS NOT NULL AND product_invariable.variation = product_offer_variation.const) OR
(product_offer_variation.const IS NULL AND product_invariable.variation IS NULL)
)
AND
(
(product_offer_modification.const IS NOT NULL AND product_invariable.modification = product_offer_modification.const) OR
(product_offer_modification.const IS NULL AND product_invariable.modification IS NULL)
)
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_info.article, product_info.sort, product_price.price, product_price.currency, product_price.quantity, product_price.reserve, product_offer_price.price, product_offer_price.currency, product_offer_quantity.quantity, product_offer_quantity.reserve, product_variation_price.price, product_variation_price.currency, product_variation_quantity.quantity, product_variation_quantity.reserve, product_modification_price.price, product_modification_price.currency, product_modification_quantity.quantity, product_modification_quantity.reserve, product.id, product.event, product_info.url, product_trans.name, product_active.active_from, product_offer.id, product_offer.value, product_offer.postfix, category_offer.reference, product_offer_variation.id, product_offer_variation.value, product_offer_variation.postfix, category_offer_variation.reference, product_offer_modification.id, product_offer_modification.value, product_offer_modification.postfix, category_offer_modification.reference, product_offer_modification.article, product_offer_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_info.url, category_trans.name, product_invariable.id, project_profile_discount.value ORDER BY product_info.sort DESC, product_modification_quantity.reserve DESC, product_variation_quantity.reserve DESC, product_offer_quantity.reserve DESC, product_price.reserve DESC LIMIT 4;
|
| 30 |
24.57 ms |
SELECT product_trans.name AS product_name, product_info.url, product_info.sort, category_info.url AS category_url 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 INNER JOIN product_active product_active ON
product_active.event = product.event AND
product_active.active IS TRUE
LEFT JOIN product_offer product_offer ON product_offer.event = product.event INNER JOIN product_categories_product product_event_category ON
product_event_category.event = product.event AND
product_event_category.category = ? AND
product_event_category.root = true 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_variation product_offer_variation ON product_offer_variation.id = (SELECT tmp_product_offer_variation.id FROM product_variation tmp_product_offer_variation WHERE tmp_product_offer_variation.offer = product_offer.id ORDER BY tmp_product_offer_variation.id DESC LIMIT 1) LEFT JOIN product_variation_quantity product_variation_quantity ON product_variation_quantity.variation = product_offer_variation.id LEFT JOIN product_variation_price product_variation_price ON product_variation_price.variation = product_offer_variation.id LEFT JOIN product_modification product_offer_modification ON product_offer_modification.variation = product_offer_variation.id LEFT JOIN product_modification_quantity product_modification_quantity ON product_modification_quantity.modification = product_offer_modification.id LEFT JOIN product_modification_price product_modification_price ON product_modification_price.modification = product_offer_modification.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
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_trans.name, product_info.url, product_info.sort, category_info.url 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 LIMIT 10
Parameters: [
"ru"
"0190ef2a-66cd-70ea-b793-2eb962175a4c"
]
SELECT product_trans.name AS product_name, product_info.url, product_info.sort, category_info.url AS category_url 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 INNER JOIN product_active product_active ON
product_active.event = product.event AND
product_active.active IS TRUE
LEFT JOIN product_offer product_offer ON product_offer.event = product.event INNER JOIN product_categories_product product_event_category ON
product_event_category.event = product.event AND
product_event_category.category = '0190ef2a-66cd-70ea-b793-2eb962175a4c' AND
product_event_category.root = true 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_variation product_offer_variation ON product_offer_variation.id = (SELECT tmp_product_offer_variation.id FROM product_variation tmp_product_offer_variation WHERE tmp_product_offer_variation.offer = product_offer.id ORDER BY tmp_product_offer_variation.id DESC LIMIT 1) LEFT JOIN product_variation_quantity product_variation_quantity ON product_variation_quantity.variation = product_offer_variation.id LEFT JOIN product_variation_price product_variation_price ON product_variation_price.variation = product_offer_variation.id LEFT JOIN product_modification product_offer_modification ON product_offer_modification.variation = product_offer_variation.id LEFT JOIN product_modification_quantity product_modification_quantity ON product_modification_quantity.modification = product_offer_modification.id LEFT JOIN product_modification_price product_modification_price ON product_modification_price.modification = product_offer_modification.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
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_trans.name, product_info.url, product_info.sort, category_info.url 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 LIMIT 10;
|
| 31 |
0.78 ms |
SELECT p0_.id AS sclr_0, p1_.name AS sclr_1, p0_.reference AS sclr_2 FROM product_category p2_ INNER JOIN product_category_offers p0_ ON (p0_.event = p2_.event) LEFT JOIN product_category_offers_trans p1_ ON (p1_.offer = p0_.id AND p1_.local = ?) WHERE p2_.id = ?
Parameters: [
"ru"
"0190ef2a-66cd-70ea-b793-2eb962175a4c"
]
SELECT p0_.id AS sclr_0, p1_.name AS sclr_1, p0_.reference AS sclr_2 FROM product_category p2_ INNER JOIN product_category_offers p0_ ON (p0_.event = p2_.event) LEFT JOIN product_category_offers_trans p1_ ON (p1_.offer = p0_.id AND p1_.local = 'ru') WHERE p2_.id = '0190ef2a-66cd-70ea-b793-2eb962175a4c';
|
| 32 |
0.61 ms |
SELECT p0_.id AS sclr_0, p1_.name AS sclr_1, p0_.reference AS sclr_2 FROM product_category_offers p2_ INNER JOIN product_category_variation p0_ ON (p0_.offer = p2_.id) LEFT JOIN product_category_variation_trans p1_ ON (p1_.variation = p0_.id AND p1_.local = ?) WHERE p2_.id = ?
Parameters: [
"ru"
"0190ef2a-66df-7272-9efc-3a129a1ee20e"
]
SELECT p0_.id AS sclr_0, p1_.name AS sclr_1, p0_.reference AS sclr_2 FROM product_category_offers p2_ INNER JOIN product_category_variation p0_ ON (p0_.offer = p2_.id) LEFT JOIN product_category_variation_trans p1_ ON (p1_.variation = p0_.id AND p1_.local = 'ru') WHERE p2_.id = '0190ef2a-66df-7272-9efc-3a129a1ee20e';
|
| 33 |
0.63 ms |
SELECT p0_.id AS sclr_0, p1_.name AS sclr_1, p0_.reference AS sclr_2 FROM product_category_variation p2_ INNER JOIN product_category_modification p0_ ON (p0_.variation = p2_.id) LEFT JOIN product_category_modification_trans p1_ ON (p1_.modification = p0_.id AND p1_.local = ?) WHERE p2_.id = ?
Parameters: [
"ru"
"0190ef2a-66e1-7a42-8220-e4333920eff9"
]
SELECT p0_.id AS sclr_0, p1_.name AS sclr_1, p0_.reference AS sclr_2 FROM product_category_variation p2_ INNER JOIN product_category_modification p0_ ON (p0_.variation = p2_.id) LEFT JOIN product_category_modification_trans p1_ ON (p1_.modification = p0_.id AND p1_.local = 'ru') WHERE p2_.id = '0190ef2a-66e1-7a42-8220-e4333920eff9';
|
| 34 |
1.04 ms |
SELECT category_section_field.id, category_section_field.const, category_section_field.type, category_section_field_trans.name FROM product_category category 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.filter = 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 = ? WHERE category.id = ? ORDER BY category_section.sort, category_section_field.sort
Parameters: [
"ru"
"0190ef2a-66cd-70ea-b793-2eb962175a4c"
]
SELECT category_section_field.id, category_section_field.const, category_section_field.type, category_section_field_trans.name FROM product_category category 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.filter = 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' WHERE category.id = '0190ef2a-66cd-70ea-b793-2eb962175a4c' ORDER BY category_section.sort, category_section_field.sort;
|
| 35 |
318.31 ms |
SELECT product.id AS product_id, product.event AS product_event, product_info.url AS product_url, product_trans.name AS product_name, product_active.active_from, 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_offer_variation.id as product_variation_uid, product_offer_variation.value as product_variation_value, product_offer_variation.postfix as product_variation_postfix, category_offer_variation.reference as product_variation_reference, product_offer_modification.id as product_modification_uid, product_offer_modification.value as product_modification_value, product_offer_modification.postfix as product_modification_postfix, category_offer_modification.reference as product_modification_reference,
CASE
WHEN product_offer_modification.article IS NOT NULL
THEN product_offer_modification.article
WHEN product_offer_variation.article IS NOT NULL
THEN product_offer_variation.article
WHEN product_offer.article IS NOT NULL
THEN product_offer.article
WHEN product_info.article IS NOT NULL
THEN product_info.article
ELSE NULL
END AS product_article
, 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_offer_variation_image.ext IS NOT NULL
THEN JSONB_BUILD_OBJECT
(
'img_root', product_offer_variation_image.root,
'img', CONCAT ( '/upload/product_variation_images' , '/', product_offer_variation_image.name),
'img_ext', product_offer_variation_image.ext,
'img_cdn', product_offer_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,
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, category_info.url AS category_url, category_trans.name AS category_name, JSON_AGG
( DISTINCT
JSONB_BUILD_OBJECT
(
'field_sort', category_section_field.sort,
'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, product_invariable.id AS product_invariable_id, project_profile_discount.value AS project_discount FROM product INNER JOIN product_event product_event ON product_event.id = product.event INNER JOIN product_categories_product product_event_category ON
product_event_category.event = product.event AND
product_event_category.category = ? AND
product_event_category.root = true INNER JOIN product_active product_active ON product_active.event = product.event LEFT JOIN product_offer product_offer ON product_offer.event = product.event INNER JOIN users_profile project_profile ON
project_profile.id = ? LEFT JOIN product_info product_info ON product_info.product = product.id LEFT JOIN product_trans product_trans ON product_trans.event = product_event.id AND product_trans.local = ? LEFT JOIN product_price product_price ON product_price.event = product_event.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 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_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 LEFT JOIN product_category_offers category_offer ON category_offer.id = product_offer.category_offer LEFT JOIN product_variation product_offer_variation ON product_offer_variation.offer = product_offer.id LEFT JOIN product_variation_images product_offer_variation_image ON product_offer_variation_image.variation = product_offer_variation.id AND product_offer_variation_image.root = true LEFT JOIN product_offer_images product_offer_images ON product_offer_variation_image.name IS NULL AND product_offer_images.offer = product_offer.id AND product_offer_images.root = true LEFT JOIN product_photo product_photo ON product_offer_images.name IS NULL AND product_photo.event = product_event.id AND product_photo.root = true LEFT JOIN product_category_variation category_offer_variation ON category_offer_variation.id = product_offer_variation.category_variation LEFT JOIN product_variation_price product_variation_price ON product_variation_price.variation = product_offer_variation.id LEFT JOIN product_variation_quantity product_variation_quantity ON product_variation_quantity.variation = product_offer_variation.id LEFT JOIN product_modification product_offer_modification ON product_offer_modification.variation = product_offer_variation.id LEFT JOIN product_modification_price product_modification_price ON product_modification_price.modification = product_offer_modification.id LEFT JOIN product_modification_quantity product_modification_quantity ON product_modification_quantity.modification = product_offer_modification.id LEFT JOIN product_category_modification category_offer_modification ON category_offer_modification.id = product_offer_modification.category_modification LEFT JOIN product_modification_images product_offer_modification_image ON product_offer_modification_image.modification = product_offer_modification.id AND product_offer_modification_image.root = true 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_offer_variation.const IS NOT NULL AND product_invariable.variation = product_offer_variation.const) OR
(product_offer_variation.const IS NULL AND product_invariable.variation IS NULL)
)
AND
(
(product_offer_modification.const IS NOT NULL AND product_invariable.modification = product_offer_modification.const) OR
(product_offer_modification.const IS NULL AND product_invariable.modification IS NULL)
)
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_info.article, product_info.sort, product_price.price, product_price.currency, product_price.quantity, product_price.reserve, product_offer_price.price, product_offer_price.currency, product_offer_quantity.quantity, product_offer_quantity.reserve, product_variation_price.price, product_variation_price.currency, product_variation_quantity.quantity, product_variation_quantity.reserve, product_modification_price.price, product_modification_price.currency, product_modification_quantity.quantity, product_modification_quantity.reserve, product.id, product.event, product_info.url, product_trans.name, product_active.active_from, product_offer.id, product_offer.value, product_offer.postfix, category_offer.reference, product_offer_variation.id, product_offer_variation.value, product_offer_variation.postfix, category_offer_variation.reference, product_offer_modification.id, product_offer_modification.value, product_offer_modification.postfix, category_offer_modification.reference, product_offer_modification.article, product_offer_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_info.url, category_trans.name, product_invariable.id, project_profile_discount.value ORDER BY product_info.sort DESC, product_modification_quantity.reserve DESC, product_variation_quantity.reserve DESC, product_offer_quantity.reserve DESC, product_price.reserve DESC LIMIT 4
Parameters: [
"01924385-2099-71c1-a793-c114a808e485"
"019469c3-700f-76a9-9b34-ccde7b4e6f49"
"ru"
"ru"
"ru"
]
SELECT
product.id AS product_id,
product.event AS product_event,
product_info.url AS product_url,
product_trans.name AS product_name,
product_active.active_from,
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_offer_variation.id as product_variation_uid,
product_offer_variation.value as product_variation_value,
product_offer_variation.postfix as product_variation_postfix,
category_offer_variation.reference as product_variation_reference,
product_offer_modification.id as product_modification_uid,
product_offer_modification.value as product_modification_value,
product_offer_modification.postfix as product_modification_postfix,
category_offer_modification.reference as product_modification_reference,
CASE WHEN product_offer_modification.article IS NOT NULL THEN
product_offer_modification.article
WHEN product_offer_variation.article IS NOT NULL THEN
product_offer_variation.article
WHEN product_offer.article IS NOT NULL THEN
product_offer.article
WHEN product_info.article IS NOT NULL THEN
product_info.article
ELSE
NULL
END AS product_article,
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_offer_variation_image.ext IS NOT NULL THEN
JSONB_BUILD_OBJECT (
'img_root',
product_offer_variation_image.root,
'img',
CONCAT (
'/upload/product_variation_images',
'/', product_offer_variation_image.name
),
'img_ext',
product_offer_variation_image.ext,
'img_cdn',
product_offer_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,
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,
category_info.url AS category_url,
category_trans.name AS category_name,
JSON_AGG (
DISTINCT JSONB_BUILD_OBJECT (
'field_sort', category_section_field.sort,
'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,
product_invariable.id AS product_invariable_id,
project_profile_discount.value AS project_discount
FROM
product
INNER JOIN product_event product_event ON product_event.id = product.event
INNER JOIN product_categories_product product_event_category ON product_event_category.event = product.event
AND product_event_category.category = ?
AND product_event_category.root = true
INNER JOIN product_active product_active ON product_active.event = product.event
LEFT JOIN product_offer product_offer ON product_offer.event = product.event
INNER JOIN users_profile project_profile ON project_profile.id = ?
LEFT JOIN product_info product_info ON product_info.product = product.id
LEFT JOIN product_trans product_trans ON product_trans.event = product_event.id
AND product_trans.local = ?
LEFT JOIN product_price product_price ON product_price.event = product_event.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
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_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
LEFT JOIN product_category_offers category_offer ON category_offer.id = product_offer.category_offer
LEFT JOIN product_variation product_offer_variation ON product_offer_variation.offer = product_offer.id
LEFT JOIN product_variation_images product_offer_variation_image ON product_offer_variation_image.variation = product_offer_variation.id
AND product_offer_variation_image.root = true
LEFT JOIN product_offer_images product_offer_images ON product_offer_variation_image.name IS NULL
AND product_offer_images.offer = product_offer.id
AND product_offer_images.root = true
LEFT JOIN product_photo product_photo ON product_offer_images.name IS NULL
AND product_photo.event = product_event.id
AND product_photo.root = true
LEFT JOIN product_category_variation category_offer_variation ON category_offer_variation.id = product_offer_variation.category_variation
LEFT JOIN product_variation_price product_variation_price ON product_variation_price.variation = product_offer_variation.id
LEFT JOIN product_variation_quantity product_variation_quantity ON product_variation_quantity.variation = product_offer_variation.id
LEFT JOIN product_modification product_offer_modification ON product_offer_modification.variation = product_offer_variation.id
LEFT JOIN product_modification_price product_modification_price ON product_modification_price.modification = product_offer_modification.id
LEFT JOIN product_modification_quantity product_modification_quantity ON product_modification_quantity.modification = product_offer_modification.id
LEFT JOIN product_category_modification category_offer_modification ON category_offer_modification.id = product_offer_modification.category_modification
LEFT JOIN product_modification_images product_offer_modification_image ON product_offer_modification_image.modification = product_offer_modification.id
AND product_offer_modification_image.root = true
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_offer_variation.const IS NOT NULL
AND product_invariable.variation = product_offer_variation.const
)
OR (
product_offer_variation.const IS NULL
AND product_invariable.variation IS NULL
)
)
AND (
(
product_offer_modification.const IS NOT NULL
AND product_invariable.modification = product_offer_modification.const
)
OR (
product_offer_modification.const IS NULL
AND product_invariable.modification IS NULL
)
)
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_info.article,
product_info.sort,
product_price.price,
product_price.currency,
product_price.quantity,
product_price.reserve,
product_offer_price.price,
product_offer_price.currency,
product_offer_quantity.quantity,
product_offer_quantity.reserve,
product_variation_price.price,
product_variation_price.currency,
product_variation_quantity.quantity,
product_variation_quantity.reserve,
product_modification_price.price,
product_modification_price.currency,
product_modification_quantity.quantity,
product_modification_quantity.reserve,
product.id,
product.event,
product_info.url,
product_trans.name,
product_active.active_from,
product_offer.id,
product_offer.value,
product_offer.postfix,
category_offer.reference,
product_offer_variation.id,
product_offer_variation.value,
product_offer_variation.postfix,
category_offer_variation.reference,
product_offer_modification.id,
product_offer_modification.value,
product_offer_modification.postfix,
category_offer_modification.reference,
product_offer_modification.article,
product_offer_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_info.url,
category_trans.name,
product_invariable.id,
project_profile_discount.value
ORDER BY
product_info.sort DESC,
product_modification_quantity.reserve DESC,
product_variation_quantity.reserve DESC,
product_offer_quantity.reserve DESC,
product_price.reserve DESC
LIMIT
4
SELECT product.id AS product_id, product.event AS product_event, product_info.url AS product_url, product_trans.name AS product_name, product_active.active_from, 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_offer_variation.id as product_variation_uid, product_offer_variation.value as product_variation_value, product_offer_variation.postfix as product_variation_postfix, category_offer_variation.reference as product_variation_reference, product_offer_modification.id as product_modification_uid, product_offer_modification.value as product_modification_value, product_offer_modification.postfix as product_modification_postfix, category_offer_modification.reference as product_modification_reference,
CASE
WHEN product_offer_modification.article IS NOT NULL
THEN product_offer_modification.article
WHEN product_offer_variation.article IS NOT NULL
THEN product_offer_variation.article
WHEN product_offer.article IS NOT NULL
THEN product_offer.article
WHEN product_info.article IS NOT NULL
THEN product_info.article
ELSE NULL
END AS product_article
, 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_offer_variation_image.ext IS NOT NULL
THEN JSONB_BUILD_OBJECT
(
'img_root', product_offer_variation_image.root,
'img', CONCAT ( '/upload/product_variation_images' , '/', product_offer_variation_image.name),
'img_ext', product_offer_variation_image.ext,
'img_cdn', product_offer_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,
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, category_info.url AS category_url, category_trans.name AS category_name, JSON_AGG
( DISTINCT
JSONB_BUILD_OBJECT
(
'field_sort', category_section_field.sort,
'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, product_invariable.id AS product_invariable_id, project_profile_discount.value AS project_discount FROM product INNER JOIN product_event product_event ON product_event.id = product.event INNER JOIN product_categories_product product_event_category ON
product_event_category.event = product.event AND
product_event_category.category = '01924385-2099-71c1-a793-c114a808e485' AND
product_event_category.root = true INNER JOIN product_active product_active ON product_active.event = product.event LEFT JOIN product_offer product_offer ON product_offer.event = product.event INNER JOIN users_profile project_profile ON
project_profile.id = '019469c3-700f-76a9-9b34-ccde7b4e6f49' LEFT JOIN product_info product_info ON product_info.product = product.id LEFT JOIN product_trans product_trans ON product_trans.event = product_event.id AND product_trans.local = 'ru' LEFT JOIN product_price product_price ON product_price.event = product_event.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 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_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 LEFT JOIN product_category_offers category_offer ON category_offer.id = product_offer.category_offer LEFT JOIN product_variation product_offer_variation ON product_offer_variation.offer = product_offer.id LEFT JOIN product_variation_images product_offer_variation_image ON product_offer_variation_image.variation = product_offer_variation.id AND product_offer_variation_image.root = true LEFT JOIN product_offer_images product_offer_images ON product_offer_variation_image.name IS NULL AND product_offer_images.offer = product_offer.id AND product_offer_images.root = true LEFT JOIN product_photo product_photo ON product_offer_images.name IS NULL AND product_photo.event = product_event.id AND product_photo.root = true LEFT JOIN product_category_variation category_offer_variation ON category_offer_variation.id = product_offer_variation.category_variation LEFT JOIN product_variation_price product_variation_price ON product_variation_price.variation = product_offer_variation.id LEFT JOIN product_variation_quantity product_variation_quantity ON product_variation_quantity.variation = product_offer_variation.id LEFT JOIN product_modification product_offer_modification ON product_offer_modification.variation = product_offer_variation.id LEFT JOIN product_modification_price product_modification_price ON product_modification_price.modification = product_offer_modification.id LEFT JOIN product_modification_quantity product_modification_quantity ON product_modification_quantity.modification = product_offer_modification.id LEFT JOIN product_category_modification category_offer_modification ON category_offer_modification.id = product_offer_modification.category_modification LEFT JOIN product_modification_images product_offer_modification_image ON product_offer_modification_image.modification = product_offer_modification.id AND product_offer_modification_image.root = true 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_offer_variation.const IS NOT NULL AND product_invariable.variation = product_offer_variation.const) OR
(product_offer_variation.const IS NULL AND product_invariable.variation IS NULL)
)
AND
(
(product_offer_modification.const IS NOT NULL AND product_invariable.modification = product_offer_modification.const) OR
(product_offer_modification.const IS NULL AND product_invariable.modification IS NULL)
)
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_info.article, product_info.sort, product_price.price, product_price.currency, product_price.quantity, product_price.reserve, product_offer_price.price, product_offer_price.currency, product_offer_quantity.quantity, product_offer_quantity.reserve, product_variation_price.price, product_variation_price.currency, product_variation_quantity.quantity, product_variation_quantity.reserve, product_modification_price.price, product_modification_price.currency, product_modification_quantity.quantity, product_modification_quantity.reserve, product.id, product.event, product_info.url, product_trans.name, product_active.active_from, product_offer.id, product_offer.value, product_offer.postfix, category_offer.reference, product_offer_variation.id, product_offer_variation.value, product_offer_variation.postfix, category_offer_variation.reference, product_offer_modification.id, product_offer_modification.value, product_offer_modification.postfix, category_offer_modification.reference, product_offer_modification.article, product_offer_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_info.url, category_trans.name, product_invariable.id, project_profile_discount.value ORDER BY product_info.sort DESC, product_modification_quantity.reserve DESC, product_variation_quantity.reserve DESC, product_offer_quantity.reserve DESC, product_price.reserve DESC LIMIT 4;
|
| 36 |
26.67 ms |
SELECT product_trans.name AS product_name, product_info.url, product_info.sort, category_info.url AS category_url 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 INNER JOIN product_active product_active ON
product_active.event = product.event AND
product_active.active IS TRUE
LEFT JOIN product_offer product_offer ON product_offer.event = product.event INNER JOIN product_categories_product product_event_category ON
product_event_category.event = product.event AND
product_event_category.category = ? AND
product_event_category.root = true 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_variation product_offer_variation ON product_offer_variation.id = (SELECT tmp_product_offer_variation.id FROM product_variation tmp_product_offer_variation WHERE tmp_product_offer_variation.offer = product_offer.id ORDER BY tmp_product_offer_variation.id DESC LIMIT 1) LEFT JOIN product_variation_quantity product_variation_quantity ON product_variation_quantity.variation = product_offer_variation.id LEFT JOIN product_variation_price product_variation_price ON product_variation_price.variation = product_offer_variation.id LEFT JOIN product_modification product_offer_modification ON product_offer_modification.variation = product_offer_variation.id LEFT JOIN product_modification_quantity product_modification_quantity ON product_modification_quantity.modification = product_offer_modification.id LEFT JOIN product_modification_price product_modification_price ON product_modification_price.modification = product_offer_modification.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
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_trans.name, product_info.url, product_info.sort, category_info.url 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 LIMIT 10
Parameters: [
"ru"
"01924385-2099-71c1-a793-c114a808e485"
]
SELECT product_trans.name AS product_name, product_info.url, product_info.sort, category_info.url AS category_url 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 INNER JOIN product_active product_active ON
product_active.event = product.event AND
product_active.active IS TRUE
LEFT JOIN product_offer product_offer ON product_offer.event = product.event INNER JOIN product_categories_product product_event_category ON
product_event_category.event = product.event AND
product_event_category.category = '01924385-2099-71c1-a793-c114a808e485' AND
product_event_category.root = true 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_variation product_offer_variation ON product_offer_variation.id = (SELECT tmp_product_offer_variation.id FROM product_variation tmp_product_offer_variation WHERE tmp_product_offer_variation.offer = product_offer.id ORDER BY tmp_product_offer_variation.id DESC LIMIT 1) LEFT JOIN product_variation_quantity product_variation_quantity ON product_variation_quantity.variation = product_offer_variation.id LEFT JOIN product_variation_price product_variation_price ON product_variation_price.variation = product_offer_variation.id LEFT JOIN product_modification product_offer_modification ON product_offer_modification.variation = product_offer_variation.id LEFT JOIN product_modification_quantity product_modification_quantity ON product_modification_quantity.modification = product_offer_modification.id LEFT JOIN product_modification_price product_modification_price ON product_modification_price.modification = product_offer_modification.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
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_trans.name, product_info.url, product_info.sort, category_info.url 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 LIMIT 10;
|
| 37 |
0.68 ms |
SELECT p0_.id AS sclr_0, p1_.name AS sclr_1, p0_.reference AS sclr_2 FROM product_category p2_ INNER JOIN product_category_offers p0_ ON (p0_.event = p2_.event) LEFT JOIN product_category_offers_trans p1_ ON (p1_.offer = p0_.id AND p1_.local = ?) WHERE p2_.id = ?
Parameters: [
"ru"
"01924385-2099-71c1-a793-c114a808e485"
]
SELECT p0_.id AS sclr_0, p1_.name AS sclr_1, p0_.reference AS sclr_2 FROM product_category p2_ INNER JOIN product_category_offers p0_ ON (p0_.event = p2_.event) LEFT JOIN product_category_offers_trans p1_ ON (p1_.offer = p0_.id AND p1_.local = 'ru') WHERE p2_.id = '01924385-2099-71c1-a793-c114a808e485';
|
| 38 |
0.58 ms |
SELECT p0_.id AS sclr_0, p1_.name AS sclr_1, p0_.reference AS sclr_2 FROM product_category_offers p2_ INNER JOIN product_category_variation p0_ ON (p0_.offer = p2_.id) LEFT JOIN product_category_variation_trans p1_ ON (p1_.variation = p0_.id AND p1_.local = ?) WHERE p2_.id = ?
Parameters: [
"ru"
"01924385-20a2-757a-b892-8a2a42ba917b"
]
SELECT p0_.id AS sclr_0, p1_.name AS sclr_1, p0_.reference AS sclr_2 FROM product_category_offers p2_ INNER JOIN product_category_variation p0_ ON (p0_.offer = p2_.id) LEFT JOIN product_category_variation_trans p1_ ON (p1_.variation = p0_.id AND p1_.local = 'ru') WHERE p2_.id = '01924385-20a2-757a-b892-8a2a42ba917b';
|
| 39 |
0.61 ms |
SELECT p0_.id AS sclr_0, p1_.name AS sclr_1, p0_.reference AS sclr_2 FROM product_category_variation p2_ INNER JOIN product_category_modification p0_ ON (p0_.variation = p2_.id) LEFT JOIN product_category_modification_trans p1_ ON (p1_.modification = p0_.id AND p1_.local = ?) WHERE p2_.id = ?
Parameters: [
"ru"
"01924385-20a3-7c98-a3d8-aae28d8c3589"
]
SELECT p0_.id AS sclr_0, p1_.name AS sclr_1, p0_.reference AS sclr_2 FROM product_category_variation p2_ INNER JOIN product_category_modification p0_ ON (p0_.variation = p2_.id) LEFT JOIN product_category_modification_trans p1_ ON (p1_.modification = p0_.id AND p1_.local = 'ru') WHERE p2_.id = '01924385-20a3-7c98-a3d8-aae28d8c3589';
|
| 40 |
0.96 ms |
SELECT category_section_field.id, category_section_field.const, category_section_field.type, category_section_field_trans.name FROM product_category category 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.filter = 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 = ? WHERE category.id = ? ORDER BY category_section.sort, category_section_field.sort
Parameters: [
"ru"
"01924385-2099-71c1-a793-c114a808e485"
]
SELECT category_section_field.id, category_section_field.const, category_section_field.type, category_section_field_trans.name FROM product_category category 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.filter = 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' WHERE category.id = '01924385-2099-71c1-a793-c114a808e485' ORDER BY category_section.sort, category_section_field.sort;
|