273.75 ms
(60.49%)
|
1 |
SELECT product.id AS product_id, product.event AS product_event, product_trans.name AS product_name, product_info.url AS product_url, product_info.sort AS product_sort, product_active.active_from AS product_active_from, category_offer.card AS category_offer_card , category_offer.reference AS product_offer_reference,
CASE
WHEN category_offer.card IS NOT NULL AND category_offer.card IS TRUE
THEN product_offer.value
ELSE NULL
END AS product_offer_value
,
CASE
WHEN category_offer.card IS NOT NULL AND category_offer.card IS TRUE
THEN product_offer.postfix
ELSE NULL
END AS product_offer_postfix
,
JSON_AGG( DISTINCT
CASE
WHEN product_offer.value IS NOT NULL THEN
JSONB_BUILD_OBJECT (
'offer_id', product_offer.id,
'offer_value', product_offer.value,
'offer_postfix', product_offer.postfix
)
ELSE NULL
END
) AS offer_agg, category_variation.card AS category_variation_card, category_variation.reference as product_variation_reference,
CASE
WHEN category_variation.card IS NOT NULL AND category_variation.card IS TRUE
THEN product_variation.value
ELSE NULL
END AS product_variation_value,
CASE
WHEN category_variation.card IS NOT NULL AND category_variation.card IS TRUE
THEN product_variation.postfix
ELSE NULL
END
AS product_variation_postfix
,
JSON_AGG( DISTINCT
CASE
WHEN product_variation.value IS NOT NULL THEN
JSONB_BUILD_OBJECT (
'variation_id', product_variation.id,
'variation_value', product_variation.value,
'variation_postfix', product_variation.postfix
)
ELSE NULL
END
) AS variation_agg, category_modification.card AS category_modification_card , category_modification.reference as product_modification_reference,
CASE
WHEN category_modification.card IS NOT NULL AND category_modification.card IS TRUE
THEN product_modification.value
ELSE NULL
END
AS product_modification_value
,
CASE
WHEN category_modification.card IS NOT NULL AND category_modification.card IS TRUE
THEN product_modification.postfix
ELSE NULL
END
AS product_modification_postfix
,
JSON_AGG( DISTINCT
CASE
WHEN product_modification.value IS NOT NULL THEN
JSONB_BUILD_OBJECT (
'modification_id', product_modification.id,
'modification_value', product_modification.value,
'modification_postfix', product_modification.postfix
)
ELSE NULL
END
) AS modification_agg, JSON_AGG( DISTINCT product_invariable.id) AS invariable,
JSON_AGG
(DISTINCT
CASE
WHEN product_offer_images.ext IS NOT NULL
THEN JSONB_BUILD_OBJECT
(
'img_root', product_offer_images.root,
'img', CONCAT ( '/upload/product_offer_images' , '/', product_offer_images.name),
'img_ext', product_offer_images.ext,
'img_cdn', product_offer_images.cdn
)
WHEN product_variation_image.ext IS NOT NULL
THEN JSONB_BUILD_OBJECT
(
'img_root', product_variation_image.root,
'img', CONCAT ( '/upload/product_variation_images' , '/', product_variation_image.name),
'img_ext', product_variation_image.ext,
'img_cdn', product_variation_image.cdn
)
WHEN product_modification_image.ext IS NOT NULL
THEN JSONB_BUILD_OBJECT
(
'img_root', product_modification_image.root,
'img', CONCAT ( '/upload/product_modification_images' , '/', product_modification_image.name),
'img_ext', product_modification_image.ext,
'img_cdn', product_modification_image.cdn
)
WHEN product_photo.ext IS NOT NULL
THEN JSONB_BUILD_OBJECT
(
'img_root', product_photo.root,
'img', CONCAT ( '/upload/product_photo' , '/', product_photo.name),
'img_ext', product_photo.ext,
'img_cdn', product_photo.cdn
)
ELSE NULL
END
)
AS product_root_images, category_info.url AS category_url, category_trans.name AS category_name, JSON_AGG
( DISTINCT
JSONB_BUILD_OBJECT
(
'field_name', category_section_field.name,
'field_card', category_section_field.card,
'field_photo', category_section_field.photo,
'field_type', category_section_field.type,
'field_trans', category_section_field_trans.name,
'field_value', product_property.value
)
)
AS category_section_field,
COALESCE(
NULLIF(MIN(product_modification_price.price), 0),
NULLIF(MIN(product_variation_price.price), 0),
NULLIF(MIN(product_offer_price.price), 0),
NULLIF(MIN(product_price.price), 0),
0
) AS product_price
,
COALESCE(
NULLIF(MIN(product_modification_price.old), 0),
NULLIF(MIN(product_variation_price.old), 0),
NULLIF(MIN(product_offer_price.old), 0),
NULLIF(MIN(product_price.old), 0),
0
) AS product_old_price
,
CASE
WHEN MIN(product_modification_price.price) IS NOT NULL
THEN product_modification_price.currency
WHEN MIN(product_variation_price.price) IS NOT NULL
THEN product_variation_price.currency
WHEN MIN(product_offer_price.price) IS NOT NULL
THEN product_offer_price.currency
WHEN product_price.price IS NOT NULL
THEN product_price.currency
ELSE NULL
END AS product_currency
,
CASE
WHEN SUM(product_modification_quantity.quantity) - SUM(product_modification_quantity.reserve) > 0
THEN SUM(product_modification_quantity.quantity) - SUM(product_modification_quantity.reserve)
WHEN SUM(product_variation_quantity.quantity) - SUM(product_variation_quantity.reserve) > 0
THEN SUM(product_variation_quantity.quantity) - SUM(product_variation_quantity.reserve)
WHEN SUM(product_offer_quantity.quantity) - SUM(product_offer_quantity.reserve) > 0
THEN SUM(product_offer_quantity.quantity) - SUM(product_offer_quantity.reserve)
WHEN SUM(product_price.quantity) - SUM(product_price.reserve) > 0
THEN SUM(product_price.quantity) - SUM(product_price.reserve)
ELSE 0
END AS product_quantity
, project_profile_discount.value AS project_discount FROM product INNER JOIN product_categories_product product_event_category ON product_event_category.event = product.event AND product_event_category.category IN (?) LEFT JOIN product_trans product_trans ON product_trans.event = product.event AND product_trans.local = ? LEFT JOIN product_description product_desc ON product_desc.event = product.event AND product_desc.device = ? AND product_desc.local = ? LEFT JOIN product_info product_info ON product_info.product = product.id INNER JOIN product_active product_active ON
product_active.event = product.event AND
product_active.active IS TRUE AND
(product_active.active_to IS NULL OR product_active.active_to > NOW())
LEFT JOIN product_price product_price ON product_price.event = product.event AND product_price.price > 0 LEFT JOIN product_offer product_offer ON product_offer.event = product.event LEFT JOIN product_photo product_photo ON product_photo.event = product.event AND product_photo.root = true INNER JOIN users_profile project_profile ON
project_profile.id = ? LEFT JOIN product_category category ON category.id = product_event_category.category LEFT JOIN product_category_info category_info ON category_info.event = category.event AND category_info.active IS TRUE LEFT JOIN product_category_trans category_trans ON category_trans.event = category.event AND category_trans.local = ? LEFT JOIN product_category_section category_section ON category_section.event = category.event LEFT JOIN product_category_section_field category_section_field ON category_section_field.section = category_section.id AND
(
category_section_field.card = TRUE OR
category_section_field.photo = TRUE OR
category_section_field.name = TRUE
)
LEFT JOIN product_category_section_field_trans category_section_field_trans ON category_section_field_trans.field = category_section_field.id AND category_section_field_trans.local = ? LEFT JOIN product_property product_property ON product_property.event = product.event AND product_property.field = category_section_field.const LEFT JOIN product_category_offers category_offer ON category_offer.id = product_offer.category_offer LEFT JOIN product_offer_price product_offer_price ON product_offer_price.offer = product_offer.id AND product_offer_price.price > 0 LEFT JOIN product_offer_quantity product_offer_quantity ON product_offer_quantity.offer = product_offer.id LEFT JOIN product_variation product_variation ON product_variation.offer = product_offer.id LEFT JOIN product_offer_images product_offer_images ON product_offer_images.offer = product_offer.id AND product_offer_images.root = true LEFT JOIN product_variation_images product_variation_image ON product_variation_image.variation = product_variation.id AND product_variation_image.root = true LEFT JOIN product_category_variation category_variation ON category_variation.id = product_variation.category_variation LEFT JOIN product_modification product_modification ON product_modification.variation = product_variation.id LEFT JOIN product_invariable product_invariable ON
product_invariable.product = product.id
AND
CASE
WHEN product_offer.const IS NOT NULL THEN product_invariable.offer = product_offer.const
ELSE product_invariable.offer IS NULL
END
AND
CASE
WHEN product_variation.const IS NOT NULL THEN product_invariable.variation = product_variation.const
ELSE product_invariable.variation IS NULL
END
AND
CASE
WHEN product_modification.const IS NOT NULL THEN product_invariable.modification = product_modification.const
ELSE product_invariable.modification IS NULL
END
LEFT JOIN product_variation_price product_variation_price ON product_variation_price.variation = product_variation.id AND product_variation_price.price > 0 LEFT JOIN product_variation_quantity product_variation_quantity ON product_variation_quantity.variation = product_variation.id LEFT JOIN product_category_modification category_modification ON category_modification.id = product_modification.category_modification LEFT JOIN product_modification_price product_modification_price ON product_modification_price.modification = product_modification.id AND product_modification_price.price > 0 LEFT JOIN product_modification_quantity product_modification_quantity ON product_modification_quantity.modification = product_modification.id LEFT JOIN product_modification_images product_modification_image ON product_modification_image.modification = product_modification.id AND product_modification_image.root = true LEFT JOIN user_profile_discount project_profile_discount ON
project_profile_discount.event = project_profile.event WHERE
COALESCE(
NULLIF(product_modification_price.price, 0),
NULLIF(product_variation_price.price, 0),
NULLIF(product_offer_price.price, 0),
NULLIF(product_price.price, 0),
0
) <> 0 GROUP BY product_price.price, product_price.currency,
CASE
WHEN category_offer.card IS NOT NULL AND category_offer.card IS TRUE
THEN product_offer.value
END
,
CASE
WHEN category_offer.card IS NOT NULL AND category_offer.card IS TRUE
THEN product_offer.postfix
END
, product_offer_price.currency,
CASE
WHEN category_variation.card IS NOT NULL AND category_variation.card IS TRUE
THEN product_variation.value
END,
CASE
WHEN category_variation.card IS NOT NULL AND category_variation.card IS TRUE
THEN product_variation.postfix
END, product_variation_price.currency,
CASE
WHEN category_modification.card IS NOT NULL AND category_modification.card IS TRUE
THEN product_modification.value
END,
CASE
WHEN category_modification.card IS NOT NULL AND category_modification.card IS TRUE
THEN product_modification.postfix
END, product_modification_price.currency, product_modification_price.currency, product.id, product.event, product_trans.name, product_info.url, product_info.sort, product_active.active_from, category_offer.card, category_offer.reference, category_variation.card, category_variation.reference, category_modification.card, category_modification.reference, category_info.url, category_trans.name, project_profile_discount.value ORDER BY product_info.sort DESC, SUM(product_modification_quantity.reserve) DESC, SUM(product_variation_quantity.reserve) DESC, SUM(product_offer_quantity.reserve) DESC, SUM(product_price.reserve) DESC, SUM(product_modification_quantity.quantity) DESC, SUM(product_variation_quantity.quantity) DESC, SUM(product_offer_quantity.quantity) DESC, SUM(product_price.quantity) DESC LIMIT 24 OFFSET 24
SELECT
product.id AS product_id,
product.event AS product_event,
product_trans.name AS product_name,
product_info.url AS product_url,
product_info.sort AS product_sort,
product_active.active_from AS product_active_from,
category_offer.card AS category_offer_card,
category_offer.reference AS product_offer_reference,
CASE WHEN category_offer.card IS NOT NULL
AND category_offer.card IS TRUE THEN
product_offer.value
ELSE
NULL
END AS product_offer_value,
CASE WHEN category_offer.card IS NOT NULL
AND category_offer.card IS TRUE THEN
product_offer.postfix
ELSE
NULL
END AS product_offer_postfix,
JSON_AGG(
DISTINCT CASE WHEN product_offer.value IS NOT NULL THEN
JSONB_BUILD_OBJECT (
'offer_id', product_offer.id, 'offer_value',
product_offer.value, 'offer_postfix',
product_offer.postfix
)
ELSE
NULL
END
) AS offer_agg,
category_variation.card AS category_variation_card,
category_variation.reference as product_variation_reference,
CASE WHEN category_variation.card IS NOT NULL
AND category_variation.card IS TRUE THEN
product_variation.value
ELSE
NULL
END AS product_variation_value,
CASE WHEN category_variation.card IS NOT NULL
AND category_variation.card IS TRUE THEN
product_variation.postfix
ELSE
NULL
END AS product_variation_postfix,
JSON_AGG(
DISTINCT CASE WHEN product_variation.value IS NOT NULL THEN
JSONB_BUILD_OBJECT (
'variation_id', product_variation.id,
'variation_value', product_variation.value,
'variation_postfix', product_variation.postfix
)
ELSE
NULL
END
) AS variation_agg,
category_modification.card AS category_modification_card,
category_modification.reference as product_modification_reference,
CASE WHEN category_modification.card IS NOT NULL
AND category_modification.card IS TRUE THEN
product_modification.value
ELSE
NULL
END AS product_modification_value,
CASE WHEN category_modification.card IS NOT NULL
AND category_modification.card IS TRUE THEN
product_modification.postfix
ELSE
NULL
END AS product_modification_postfix,
JSON_AGG(
DISTINCT CASE WHEN product_modification.value IS NOT NULL THEN
JSONB_BUILD_OBJECT (
'modification_id', product_modification.id,
'modification_value', product_modification.value,
'modification_postfix', product_modification.postfix
)
ELSE
NULL
END
) AS modification_agg,
JSON_AGG(DISTINCT product_invariable.id) AS invariable,
JSON_AGG (
DISTINCT CASE WHEN product_offer_images.ext IS NOT NULL THEN
JSONB_BUILD_OBJECT (
'img_root',
product_offer_images.root,
'img',
CONCAT (
'/upload/product_offer_images',
'/', product_offer_images.name
),
'img_ext',
product_offer_images.ext,
'img_cdn',
product_offer_images.cdn
)
WHEN product_variation_image.ext IS NOT NULL THEN
JSONB_BUILD_OBJECT (
'img_root',
product_variation_image.root,
'img',
CONCAT (
'/upload/product_variation_images',
'/', product_variation_image.name
),
'img_ext',
product_variation_image.ext,
'img_cdn',
product_variation_image.cdn
)
WHEN product_modification_image.ext IS NOT NULL THEN
JSONB_BUILD_OBJECT (
'img_root',
product_modification_image.root,
'img',
CONCAT (
'/upload/product_modification_images',
'/', product_modification_image.name
),
'img_ext',
product_modification_image.ext,
'img_cdn',
product_modification_image.cdn
)
WHEN product_photo.ext IS NOT NULL THEN
JSONB_BUILD_OBJECT (
'img_root',
product_photo.root,
'img',
CONCAT (
'/upload/product_photo', '/', product_photo.name
),
'img_ext',
product_photo.ext,
'img_cdn',
product_photo.cdn
)
ELSE
NULL
END
) AS product_root_images,
category_info.url AS category_url,
category_trans.name AS category_name,
JSON_AGG (
DISTINCT JSONB_BUILD_OBJECT (
'field_name', category_section_field.name,
'field_card', category_section_field.card,
'field_photo', category_section_field.photo,
'field_type', category_section_field.type,
'field_trans', category_section_field_trans.name,
'field_value', product_property.value
)
) AS category_section_field,
COALESCE(
NULLIF(
MIN(
product_modification_price.price
),
0
),
NULLIF(
MIN(product_variation_price.price),
0
),
NULLIF(
MIN(product_offer_price.price),
0
),
NULLIF(
MIN(product_price.price),
0
),
0
) AS product_price,
COALESCE(
NULLIF(
MIN(product_modification_price.old),
0
),
NULLIF(
MIN(product_variation_price.old),
0
),
NULLIF(
MIN(product_offer_price.old),
0
),
NULLIF(
MIN(product_price.old),
0
),
0
) AS product_old_price,
CASE WHEN MIN(
product_modification_price.price
) IS NOT NULL THEN
product_modification_price.currency
WHEN MIN(product_variation_price.price) IS NOT NULL THEN
product_variation_price.currency
WHEN MIN(product_offer_price.price) IS NOT NULL THEN
product_offer_price.currency
WHEN product_price.price IS NOT NULL THEN
product_price.currency
ELSE
NULL
END AS product_currency,
CASE WHEN SUM(
product_modification_quantity.quantity
) - SUM(
product_modification_quantity.reserve
) > 0 THEN
SUM(
product_modification_quantity.quantity
) - SUM(
product_modification_quantity.reserve
)
WHEN SUM(
product_variation_quantity.quantity
) - SUM(
product_variation_quantity.reserve
) > 0 THEN
SUM(
product_variation_quantity.quantity
) - SUM(
product_variation_quantity.reserve
)
WHEN SUM(
product_offer_quantity.quantity
) - SUM(product_offer_quantity.reserve) > 0 THEN
SUM(
product_offer_quantity.quantity
) - SUM(product_offer_quantity.reserve)
WHEN SUM(product_price.quantity) - SUM(product_price.reserve) > 0 THEN
SUM(product_price.quantity) - SUM(product_price.reserve)
ELSE
0
END AS product_quantity,
project_profile_discount.value AS project_discount
FROM
product
INNER JOIN product_categories_product product_event_category ON product_event_category.event = product.event
AND product_event_category.category IN (?)
LEFT JOIN product_trans product_trans ON product_trans.event = product.event
AND product_trans.local = ?
LEFT JOIN product_description product_desc ON product_desc.event = product.event
AND product_desc.device = ?
AND product_desc.local = ?
LEFT JOIN product_info product_info ON product_info.product = product.id
INNER JOIN product_active product_active ON product_active.event = product.event
AND product_active.active IS TRUE
AND (
product_active.active_to IS NULL
OR product_active.active_to > NOW()
)
LEFT JOIN product_price product_price ON product_price.event = product.event
AND product_price.price > 0
LEFT JOIN product_offer product_offer ON product_offer.event = product.event
LEFT JOIN product_photo product_photo ON product_photo.event = product.event
AND product_photo.root = true
INNER JOIN users_profile project_profile ON project_profile.id = ?
LEFT JOIN product_category category ON category.id = product_event_category.category
LEFT JOIN product_category_info category_info ON category_info.event = category.event
AND category_info.active IS TRUE
LEFT JOIN product_category_trans category_trans ON category_trans.event = category.event
AND category_trans.local = ?
LEFT JOIN product_category_section category_section ON category_section.event = category.event
LEFT JOIN product_category_section_field category_section_field ON category_section_field.section = category_section.id
AND (
category_section_field.card = TRUE
OR category_section_field.photo = TRUE
OR category_section_field.name = TRUE
)
LEFT JOIN product_category_section_field_trans category_section_field_trans ON category_section_field_trans.field = category_section_field.id
AND category_section_field_trans.local = ?
LEFT JOIN product_property product_property ON product_property.event = product.event
AND product_property.field = category_section_field.const
LEFT JOIN product_category_offers category_offer ON category_offer.id = product_offer.category_offer
LEFT JOIN product_offer_price product_offer_price ON product_offer_price.offer = product_offer.id
AND product_offer_price.price > 0
LEFT JOIN product_offer_quantity product_offer_quantity ON product_offer_quantity.offer = product_offer.id
LEFT JOIN product_variation product_variation ON product_variation.offer = product_offer.id
LEFT JOIN product_offer_images product_offer_images ON product_offer_images.offer = product_offer.id
AND product_offer_images.root = true
LEFT JOIN product_variation_images product_variation_image ON product_variation_image.variation = product_variation.id
AND product_variation_image.root = true
LEFT JOIN product_category_variation category_variation ON category_variation.id = product_variation.category_variation
LEFT JOIN product_modification product_modification ON product_modification.variation = product_variation.id
LEFT JOIN product_invariable product_invariable ON product_invariable.product = product.id
AND CASE WHEN product_offer.const IS NOT NULL THEN
product_invariable.offer = product_offer.const
ELSE
product_invariable.offer IS NULL
END
AND CASE WHEN product_variation.const IS NOT NULL THEN
product_invariable.variation = product_variation.const
ELSE
product_invariable.variation IS NULL
END
AND CASE WHEN product_modification.const IS NOT NULL THEN
product_invariable.modification = product_modification.const
ELSE
product_invariable.modification IS NULL
END
LEFT JOIN product_variation_price product_variation_price ON product_variation_price.variation = product_variation.id
AND product_variation_price.price > 0
LEFT JOIN product_variation_quantity product_variation_quantity ON product_variation_quantity.variation = product_variation.id
LEFT JOIN product_category_modification category_modification ON category_modification.id = product_modification.category_modification
LEFT JOIN product_modification_price product_modification_price ON product_modification_price.modification = product_modification.id
AND product_modification_price.price > 0
LEFT JOIN product_modification_quantity product_modification_quantity ON product_modification_quantity.modification = product_modification.id
LEFT JOIN product_modification_images product_modification_image ON product_modification_image.modification = product_modification.id
AND product_modification_image.root = true
LEFT JOIN user_profile_discount project_profile_discount ON project_profile_discount.event = project_profile.event
WHERE
COALESCE(
NULLIF(
product_modification_price.price,
0
),
NULLIF(
product_variation_price.price, 0
),
NULLIF(product_offer_price.price, 0),
NULLIF(product_price.price, 0),
0
) <> 0
GROUP BY
product_price.price,
product_price.currency,
CASE WHEN category_offer.card IS NOT NULL
AND category_offer.card IS TRUE THEN
product_offer.value
END,
CASE WHEN category_offer.card IS NOT NULL
AND category_offer.card IS TRUE THEN
product_offer.postfix
END,
product_offer_price.currency,
CASE WHEN category_variation.card IS NOT NULL
AND category_variation.card IS TRUE THEN
product_variation.value
END,
CASE WHEN category_variation.card IS NOT NULL
AND category_variation.card IS TRUE THEN
product_variation.postfix
END,
product_variation_price.currency,
CASE WHEN category_modification.card IS NOT NULL
AND category_modification.card IS TRUE THEN
product_modification.value
END,
CASE WHEN category_modification.card IS NOT NULL
AND category_modification.card IS TRUE THEN
product_modification.postfix
END,
product_modification_price.currency,
product_modification_price.currency,
product.id,
product.event,
product_trans.name,
product_info.url,
product_info.sort,
product_active.active_from,
category_offer.card,
category_offer.reference,
category_variation.card,
category_variation.reference,
category_modification.card,
category_modification.reference,
category_info.url,
category_trans.name,
project_profile_discount.value
ORDER BY
product_info.sort DESC,
SUM(
product_modification_quantity.reserve
) DESC,
SUM(
product_variation_quantity.reserve
) DESC,
SUM(product_offer_quantity.reserve) DESC,
SUM(product_price.reserve) DESC,
SUM(
product_modification_quantity.quantity
) DESC,
SUM(
product_variation_quantity.quantity
) DESC,
SUM(
product_offer_quantity.quantity
) DESC,
SUM(product_price.quantity) DESC
LIMIT
24
OFFSET
24
SELECT product.id AS product_id, product.event AS product_event, product_trans.name AS product_name, product_info.url AS product_url, product_info.sort AS product_sort, product_active.active_from AS product_active_from, category_offer.card AS category_offer_card , category_offer.reference AS product_offer_reference,
CASE
WHEN category_offer.card IS NOT NULL AND category_offer.card IS TRUE
THEN product_offer.value
ELSE NULL
END AS product_offer_value
,
CASE
WHEN category_offer.card IS NOT NULL AND category_offer.card IS TRUE
THEN product_offer.postfix
ELSE NULL
END AS product_offer_postfix
,
JSON_AGG( DISTINCT
CASE
WHEN product_offer.value IS NOT NULL THEN
JSONB_BUILD_OBJECT (
'offer_id', product_offer.id,
'offer_value', product_offer.value,
'offer_postfix', product_offer.postfix
)
ELSE NULL
END
) AS offer_agg, category_variation.card AS category_variation_card, category_variation.reference as product_variation_reference,
CASE
WHEN category_variation.card IS NOT NULL AND category_variation.card IS TRUE
THEN product_variation.value
ELSE NULL
END AS product_variation_value,
CASE
WHEN category_variation.card IS NOT NULL AND category_variation.card IS TRUE
THEN product_variation.postfix
ELSE NULL
END
AS product_variation_postfix
,
JSON_AGG( DISTINCT
CASE
WHEN product_variation.value IS NOT NULL THEN
JSONB_BUILD_OBJECT (
'variation_id', product_variation.id,
'variation_value', product_variation.value,
'variation_postfix', product_variation.postfix
)
ELSE NULL
END
) AS variation_agg, category_modification.card AS category_modification_card , category_modification.reference as product_modification_reference,
CASE
WHEN category_modification.card IS NOT NULL AND category_modification.card IS TRUE
THEN product_modification.value
ELSE NULL
END
AS product_modification_value
,
CASE
WHEN category_modification.card IS NOT NULL AND category_modification.card IS TRUE
THEN product_modification.postfix
ELSE NULL
END
AS product_modification_postfix
,
JSON_AGG( DISTINCT
CASE
WHEN product_modification.value IS NOT NULL THEN
JSONB_BUILD_OBJECT (
'modification_id', product_modification.id,
'modification_value', product_modification.value,
'modification_postfix', product_modification.postfix
)
ELSE NULL
END
) AS modification_agg, JSON_AGG( DISTINCT product_invariable.id) AS invariable,
JSON_AGG
(DISTINCT
CASE
WHEN product_offer_images.ext IS NOT NULL
THEN JSONB_BUILD_OBJECT
(
'img_root', product_offer_images.root,
'img', CONCAT ( '/upload/product_offer_images' , '/', product_offer_images.name),
'img_ext', product_offer_images.ext,
'img_cdn', product_offer_images.cdn
)
WHEN product_variation_image.ext IS NOT NULL
THEN JSONB_BUILD_OBJECT
(
'img_root', product_variation_image.root,
'img', CONCAT ( '/upload/product_variation_images' , '/', product_variation_image.name),
'img_ext', product_variation_image.ext,
'img_cdn', product_variation_image.cdn
)
WHEN product_modification_image.ext IS NOT NULL
THEN JSONB_BUILD_OBJECT
(
'img_root', product_modification_image.root,
'img', CONCAT ( '/upload/product_modification_images' , '/', product_modification_image.name),
'img_ext', product_modification_image.ext,
'img_cdn', product_modification_image.cdn
)
WHEN product_photo.ext IS NOT NULL
THEN JSONB_BUILD_OBJECT
(
'img_root', product_photo.root,
'img', CONCAT ( '/upload/product_photo' , '/', product_photo.name),
'img_ext', product_photo.ext,
'img_cdn', product_photo.cdn
)
ELSE NULL
END
)
AS product_root_images, category_info.url AS category_url, category_trans.name AS category_name, JSON_AGG
( DISTINCT
JSONB_BUILD_OBJECT
(
'field_name', category_section_field.name,
'field_card', category_section_field.card,
'field_photo', category_section_field.photo,
'field_type', category_section_field.type,
'field_trans', category_section_field_trans.name,
'field_value', product_property.value
)
)
AS category_section_field,
COALESCE(
NULLIF(MIN(product_modification_price.price), 0),
NULLIF(MIN(product_variation_price.price), 0),
NULLIF(MIN(product_offer_price.price), 0),
NULLIF(MIN(product_price.price), 0),
0
) AS product_price
,
COALESCE(
NULLIF(MIN(product_modification_price.old), 0),
NULLIF(MIN(product_variation_price.old), 0),
NULLIF(MIN(product_offer_price.old), 0),
NULLIF(MIN(product_price.old), 0),
0
) AS product_old_price
,
CASE
WHEN MIN(product_modification_price.price) IS NOT NULL
THEN product_modification_price.currency
WHEN MIN(product_variation_price.price) IS NOT NULL
THEN product_variation_price.currency
WHEN MIN(product_offer_price.price) IS NOT NULL
THEN product_offer_price.currency
WHEN product_price.price IS NOT NULL
THEN product_price.currency
ELSE NULL
END AS product_currency
,
CASE
WHEN SUM(product_modification_quantity.quantity) - SUM(product_modification_quantity.reserve) > 0
THEN SUM(product_modification_quantity.quantity) - SUM(product_modification_quantity.reserve)
WHEN SUM(product_variation_quantity.quantity) - SUM(product_variation_quantity.reserve) > 0
THEN SUM(product_variation_quantity.quantity) - SUM(product_variation_quantity.reserve)
WHEN SUM(product_offer_quantity.quantity) - SUM(product_offer_quantity.reserve) > 0
THEN SUM(product_offer_quantity.quantity) - SUM(product_offer_quantity.reserve)
WHEN SUM(product_price.quantity) - SUM(product_price.reserve) > 0
THEN SUM(product_price.quantity) - SUM(product_price.reserve)
ELSE 0
END AS product_quantity
, project_profile_discount.value AS project_discount FROM product INNER JOIN product_categories_product product_event_category ON product_event_category.event = product.event AND product_event_category.category IN ('01905ea6-805e-7a56-b53c-da41c19f9e38') LEFT JOIN product_trans product_trans ON product_trans.event = product.event AND product_trans.local = 'ru' LEFT JOIN product_description product_desc ON product_desc.event = product.event AND product_desc.device = 'pc' AND product_desc.local = 'ru' LEFT JOIN product_info product_info ON product_info.product = product.id INNER JOIN product_active product_active ON
product_active.event = product.event AND
product_active.active IS TRUE AND
(product_active.active_to IS NULL OR product_active.active_to > NOW())
LEFT JOIN product_price product_price ON product_price.event = product.event AND product_price.price > 0 LEFT JOIN product_offer product_offer ON product_offer.event = product.event LEFT JOIN product_photo product_photo ON product_photo.event = product.event AND product_photo.root = true INNER JOIN users_profile project_profile ON
project_profile.id = '019469c3-700f-76a9-9b34-ccde7b4e6f49' LEFT JOIN product_category category ON category.id = product_event_category.category LEFT JOIN product_category_info category_info ON category_info.event = category.event AND category_info.active IS TRUE LEFT JOIN product_category_trans category_trans ON category_trans.event = category.event AND category_trans.local = 'ru' LEFT JOIN product_category_section category_section ON category_section.event = category.event LEFT JOIN product_category_section_field category_section_field ON category_section_field.section = category_section.id AND
(
category_section_field.card = TRUE OR
category_section_field.photo = TRUE OR
category_section_field.name = TRUE
)
LEFT JOIN product_category_section_field_trans category_section_field_trans ON category_section_field_trans.field = category_section_field.id AND category_section_field_trans.local = 'ru' LEFT JOIN product_property product_property ON product_property.event = product.event AND product_property.field = category_section_field.const LEFT JOIN product_category_offers category_offer ON category_offer.id = product_offer.category_offer LEFT JOIN product_offer_price product_offer_price ON product_offer_price.offer = product_offer.id AND product_offer_price.price > 0 LEFT JOIN product_offer_quantity product_offer_quantity ON product_offer_quantity.offer = product_offer.id LEFT JOIN product_variation product_variation ON product_variation.offer = product_offer.id LEFT JOIN product_offer_images product_offer_images ON product_offer_images.offer = product_offer.id AND product_offer_images.root = true LEFT JOIN product_variation_images product_variation_image ON product_variation_image.variation = product_variation.id AND product_variation_image.root = true LEFT JOIN product_category_variation category_variation ON category_variation.id = product_variation.category_variation LEFT JOIN product_modification product_modification ON product_modification.variation = product_variation.id LEFT JOIN product_invariable product_invariable ON
product_invariable.product = product.id
AND
CASE
WHEN product_offer.const IS NOT NULL THEN product_invariable.offer = product_offer.const
ELSE product_invariable.offer IS NULL
END
AND
CASE
WHEN product_variation.const IS NOT NULL THEN product_invariable.variation = product_variation.const
ELSE product_invariable.variation IS NULL
END
AND
CASE
WHEN product_modification.const IS NOT NULL THEN product_invariable.modification = product_modification.const
ELSE product_invariable.modification IS NULL
END
LEFT JOIN product_variation_price product_variation_price ON product_variation_price.variation = product_variation.id AND product_variation_price.price > 0 LEFT JOIN product_variation_quantity product_variation_quantity ON product_variation_quantity.variation = product_variation.id LEFT JOIN product_category_modification category_modification ON category_modification.id = product_modification.category_modification LEFT JOIN product_modification_price product_modification_price ON product_modification_price.modification = product_modification.id AND product_modification_price.price > 0 LEFT JOIN product_modification_quantity product_modification_quantity ON product_modification_quantity.modification = product_modification.id LEFT JOIN product_modification_images product_modification_image ON product_modification_image.modification = product_modification.id AND product_modification_image.root = true LEFT JOIN user_profile_discount project_profile_discount ON
project_profile_discount.event = project_profile.event WHERE
COALESCE(
NULLIF(product_modification_price.price, 0),
NULLIF(product_variation_price.price, 0),
NULLIF(product_offer_price.price, 0),
NULLIF(product_price.price, 0),
0
) <> 0 GROUP BY product_price.price, product_price.currency,
CASE
WHEN category_offer.card IS NOT NULL AND category_offer.card IS TRUE
THEN product_offer.value
END
,
CASE
WHEN category_offer.card IS NOT NULL AND category_offer.card IS TRUE
THEN product_offer.postfix
END
, product_offer_price.currency,
CASE
WHEN category_variation.card IS NOT NULL AND category_variation.card IS TRUE
THEN product_variation.value
END,
CASE
WHEN category_variation.card IS NOT NULL AND category_variation.card IS TRUE
THEN product_variation.postfix
END, product_variation_price.currency,
CASE
WHEN category_modification.card IS NOT NULL AND category_modification.card IS TRUE
THEN product_modification.value
END,
CASE
WHEN category_modification.card IS NOT NULL AND category_modification.card IS TRUE
THEN product_modification.postfix
END, product_modification_price.currency, product_modification_price.currency, product.id, product.event, product_trans.name, product_info.url, product_info.sort, product_active.active_from, category_offer.card, category_offer.reference, category_variation.card, category_variation.reference, category_modification.card, category_modification.reference, category_info.url, category_trans.name, project_profile_discount.value ORDER BY product_info.sort DESC, SUM(product_modification_quantity.reserve) DESC, SUM(product_variation_quantity.reserve) DESC, SUM(product_offer_quantity.reserve) DESC, SUM(product_price.reserve) DESC, SUM(product_modification_quantity.quantity) DESC, SUM(product_variation_quantity.quantity) DESC, SUM(product_offer_quantity.quantity) DESC, SUM(product_price.quantity) DESC LIMIT 24 OFFSET 24;
|
122.57 ms
(27.08%)
|
1 |
SELECT product_trans.name AS product_name, product_info.url, product_info.sort, product_offer.id as product_offer_uid, product_offer.value as product_offer_value, product_offer.postfix as product_offer_postfix, category_offer.reference as product_offer_reference, product_variation.id as product_variation_uid, product_variation.value as product_variation_value, product_variation.postfix as product_variation_postfix, category_offer_variation.reference as product_variation_reference, product_modification.id as product_modification_uid, product_modification.value as product_modification_value, product_modification.postfix as product_modification_postfix, category_offer_modification.reference as product_modification_reference, category.id as category_id, category_info.url AS category_url,
COALESCE(
NULLIF(product_modification_price.price, 0),
NULLIF(product_variation_price.price, 0),
NULLIF(product_offer_price.price, 0),
NULLIF(product_price.price, 0),
0
) AS product_price
,
COALESCE(
NULLIF(product_modification_price.old, 0),
NULLIF(product_variation_price.old, 0),
NULLIF(product_offer_price.old, 0),
NULLIF(product_price.old, 0),
0
) AS product_old_price
,
CASE
WHEN COALESCE(product_modification_price.price, 0) != 0
THEN product_modification_price.currency
WHEN COALESCE(product_variation_price.price, 0) != 0
THEN product_variation_price.currency
WHEN COALESCE(product_offer_price.price, 0) != 0
THEN product_offer_price.currency
WHEN COALESCE(product_price.price, 0) != 0
THEN product_price.currency
ELSE NULL
END AS product_currency, JSON_AGG
(DISTINCT
CASE
WHEN product_offer_images.ext IS NOT NULL
THEN JSONB_BUILD_OBJECT
(
'img_root', product_offer_images.root,
'img', CONCAT ( '/upload/product_offer_images' , '/', product_offer_images.name),
'img_ext', product_offer_images.ext,
'img_cdn', product_offer_images.cdn
)
WHEN product_variation_image.ext IS NOT NULL
THEN JSONB_BUILD_OBJECT
(
'img_root', product_variation_image.root,
'img', CONCAT ( '/upload/product_variation_images' , '/', product_variation_image.name),
'img_ext', product_variation_image.ext,
'img_cdn', product_variation_image.cdn
)
WHEN product_modification_image.ext IS NOT NULL
THEN JSONB_BUILD_OBJECT
(
'img_root', product_modification_image.root,
'img', CONCAT ( '/upload/product_modification_images' , '/', product_modification_image.name),
'img_ext', product_modification_image.ext,
'img_cdn', product_modification_image.cdn
)
WHEN product_photo.ext IS NOT NULL
THEN JSONB_BUILD_OBJECT
(
'img_root', product_photo.root,
'img', CONCAT ( '/upload/product_photo' , '/', product_photo.name),
'img_ext', product_photo.ext,
'img_cdn', product_photo.cdn
)
END) AS product_images, product_invariable.id AS product_invariable_id, project_profile_discount.value AS project_discount FROM product LEFT JOIN product_trans product_trans ON product_trans.event = product.event AND product_trans.local = ? LEFT JOIN product_price product_price ON product_price.event = product.event LEFT JOIN product_info product_info ON product_info.product = product.id LEFT JOIN product_offer product_offer ON product_offer.event = product.event LEFT JOIN product_categories_product product_event_category ON
product_event_category.event = product.event AND
product_event_category.root = true LEFT JOIN product_photo product_photo ON product_photo.event = product.event INNER JOIN users_profile project_profile ON
project_profile.id = ? LEFT JOIN product_offer_quantity product_offer_quantity ON product_offer_quantity.offer = product_offer.id LEFT JOIN product_offer_price product_offer_price ON product_offer_price.offer = product_offer.id LEFT JOIN product_category_offers category_offer ON category_offer.id = product_offer.category_offer LEFT JOIN product_variation product_variation ON product_variation.id = (SELECT tmp_product_variation.id FROM product_variation tmp_product_variation WHERE tmp_product_variation.offer = product_offer.id ORDER BY tmp_product_variation.id DESC LIMIT 1) LEFT JOIN product_variation_images product_variation_image ON product_variation_image.variation = product_variation.id LEFT JOIN product_offer_images product_offer_images ON product_offer_images.offer = product_offer.id LEFT JOIN product_variation_quantity product_variation_quantity ON product_variation_quantity.variation = product_variation.id LEFT JOIN product_variation_price product_variation_price ON product_variation_price.variation = product_variation.id LEFT JOIN product_category_variation category_offer_variation ON category_offer_variation.id = product_variation.category_variation LEFT JOIN product_modification product_modification ON product_modification.variation = product_variation.id LEFT JOIN product_modification_quantity product_modification_quantity ON product_modification_quantity.modification = product_modification.id LEFT JOIN product_modification_price product_modification_price ON product_modification_price.modification = product_modification.id LEFT JOIN product_category_modification category_offer_modification ON category_offer_modification.id = product_modification.category_modification LEFT JOIN product_modification_images product_modification_image ON product_modification_image.modification = product_modification.id LEFT JOIN product_invariable product_invariable ON
product_invariable.product = product.id AND
(
(product_offer.const IS NOT NULL AND product_invariable.offer = product_offer.const) OR
(product_offer.const IS NULL AND product_invariable.offer IS NULL)
)
AND
(
(product_variation.const IS NOT NULL AND product_invariable.variation = product_variation.const) OR
(product_variation.const IS NULL AND product_invariable.variation IS NULL)
)
AND
(
(product_modification.const IS NOT NULL AND product_invariable.modification = product_modification.const) OR
(product_modification.const IS NULL AND product_invariable.modification IS NULL)
)
INNER JOIN product_category category ON
category.id = product_event_category.category AND
category.id = ? LEFT JOIN product_category_info category_info ON category_info.event = category.event LEFT JOIN user_profile_discount project_profile_discount ON
project_profile_discount.event = project_profile.event WHERE (
CASE
WHEN product_modification_price.price IS NOT NULL THEN product_modification_price.price
WHEN product_variation_price.price IS NOT NULL THEN product_variation_price.price
WHEN product_offer_price.price IS NOT NULL THEN product_offer_price.price
WHEN product_price.price IS NOT NULL THEN product_price.price
ELSE 0
END > 0) AND (
CASE
WHEN product_modification_quantity.quantity IS NOT NULL THEN (product_modification_quantity.quantity - product_modification_quantity.reserve)
WHEN product_variation_quantity.quantity IS NOT NULL THEN (product_variation_quantity.quantity - product_variation_quantity.reserve)
WHEN product_offer_quantity.quantity IS NOT NULL THEN (product_offer_quantity.quantity - product_offer_quantity.reserve)
WHEN product_price.quantity IS NOT NULL THEN (product_price.quantity - product_price.reserve)
ELSE 0
END > 0) GROUP BY product_price.price, product_price.currency, product_price.reserve, product_price.quantity, product_offer_quantity.quantity, product_offer_quantity.reserve, product_offer_price.price, product_offer_price.currency, product_variation_quantity.quantity, product_variation_quantity.reserve, product_variation_price.price, product_variation_price.currency, product_modification_quantity.quantity, product_modification_quantity.reserve, product_modification_price.price, product_modification_price.currency, product_trans.name, product_info.url, product_info.sort, product_offer.id, product_offer.value, product_offer.postfix, category_offer.reference, product_variation.id, product_variation.value, product_variation.postfix, category_offer_variation.reference, product_modification.id, product_modification.value, product_modification.postfix, category_offer_modification.reference, category.id, category_info.url, product_modification_price.price, product_variation_price.price, product_offer_price.price, product_price.price, product_modification_price.old, product_variation_price.old, product_offer_price.old, product_price.old, product_modification_price.currency, product_variation_price.currency, product_offer_price.currency, product_price.currency, product_invariable.id, project_profile_discount.value ORDER BY product_modification_quantity.reserve DESC, product_variation_quantity.reserve DESC, product_offer_quantity.reserve DESC, product_price.reserve DESC LIMIT 2
Parameters: [
"ru"
"019469c3-700f-76a9-9b34-ccde7b4e6f49"
"01905ea6-805e-7a56-b53c-da41c19f9e38"
]
SELECT
product_trans.name AS product_name,
product_info.url,
product_info.sort,
product_offer.id as product_offer_uid,
product_offer.value as product_offer_value,
product_offer.postfix as product_offer_postfix,
category_offer.reference as product_offer_reference,
product_variation.id as product_variation_uid,
product_variation.value as product_variation_value,
product_variation.postfix as product_variation_postfix,
category_offer_variation.reference as product_variation_reference,
product_modification.id as product_modification_uid,
product_modification.value as product_modification_value,
product_modification.postfix as product_modification_postfix,
category_offer_modification.reference as product_modification_reference,
category.id as category_id,
category_info.url AS category_url,
COALESCE(
NULLIF(
product_modification_price.price,
0
),
NULLIF(
product_variation_price.price, 0
),
NULLIF(product_offer_price.price, 0),
NULLIF(product_price.price, 0),
0
) AS product_price,
COALESCE(
NULLIF(
product_modification_price.old,
0
),
NULLIF(product_variation_price.old, 0),
NULLIF(product_offer_price.old, 0),
NULLIF(product_price.old, 0),
0
) AS product_old_price,
CASE WHEN COALESCE(
product_modification_price.price,
0
) != 0 THEN
product_modification_price.currency
WHEN COALESCE(
product_variation_price.price, 0
) != 0 THEN
product_variation_price.currency
WHEN COALESCE(product_offer_price.price, 0) != 0 THEN
product_offer_price.currency
WHEN COALESCE(product_price.price, 0) != 0 THEN
product_price.currency
ELSE
NULL
END AS product_currency,
JSON_AGG (
DISTINCT CASE WHEN product_offer_images.ext IS NOT NULL THEN
JSONB_BUILD_OBJECT (
'img_root',
product_offer_images.root,
'img',
CONCAT (
'/upload/product_offer_images',
'/', product_offer_images.name
),
'img_ext',
product_offer_images.ext,
'img_cdn',
product_offer_images.cdn
)
WHEN product_variation_image.ext IS NOT NULL THEN
JSONB_BUILD_OBJECT (
'img_root',
product_variation_image.root,
'img',
CONCAT (
'/upload/product_variation_images',
'/', product_variation_image.name
),
'img_ext',
product_variation_image.ext,
'img_cdn',
product_variation_image.cdn
)
WHEN product_modification_image.ext IS NOT NULL THEN
JSONB_BUILD_OBJECT (
'img_root',
product_modification_image.root,
'img',
CONCAT (
'/upload/product_modification_images',
'/', product_modification_image.name
),
'img_ext',
product_modification_image.ext,
'img_cdn',
product_modification_image.cdn
)
WHEN product_photo.ext IS NOT NULL THEN
JSONB_BUILD_OBJECT (
'img_root',
product_photo.root,
'img',
CONCAT (
'/upload/product_photo', '/', product_photo.name
),
'img_ext',
product_photo.ext,
'img_cdn',
product_photo.cdn
)
END
) AS product_images,
product_invariable.id AS product_invariable_id,
project_profile_discount.value AS project_discount
FROM
product
LEFT JOIN product_trans product_trans ON product_trans.event = product.event
AND product_trans.local = ?
LEFT JOIN product_price product_price ON product_price.event = product.event
LEFT JOIN product_info product_info ON product_info.product = product.id
LEFT JOIN product_offer product_offer ON product_offer.event = product.event
LEFT JOIN product_categories_product product_event_category ON product_event_category.event = product.event
AND product_event_category.root = true
LEFT JOIN product_photo product_photo ON product_photo.event = product.event
INNER JOIN users_profile project_profile ON project_profile.id = ?
LEFT JOIN product_offer_quantity product_offer_quantity ON product_offer_quantity.offer = product_offer.id
LEFT JOIN product_offer_price product_offer_price ON product_offer_price.offer = product_offer.id
LEFT JOIN product_category_offers category_offer ON category_offer.id = product_offer.category_offer
LEFT JOIN product_variation product_variation ON product_variation.id = (
SELECT
tmp_product_variation.id
FROM
product_variation tmp_product_variation
WHERE
tmp_product_variation.offer = product_offer.id
ORDER BY
tmp_product_variation.id DESC
LIMIT
1
) LEFT JOIN product_variation_images product_variation_image ON product_variation_image.variation = product_variation.id
LEFT JOIN product_offer_images product_offer_images ON product_offer_images.offer = product_offer.id
LEFT JOIN product_variation_quantity product_variation_quantity ON product_variation_quantity.variation = product_variation.id
LEFT JOIN product_variation_price product_variation_price ON product_variation_price.variation = product_variation.id
LEFT JOIN product_category_variation category_offer_variation ON category_offer_variation.id = product_variation.category_variation
LEFT JOIN product_modification product_modification ON product_modification.variation = product_variation.id
LEFT JOIN product_modification_quantity product_modification_quantity ON product_modification_quantity.modification = product_modification.id
LEFT JOIN product_modification_price product_modification_price ON product_modification_price.modification = product_modification.id
LEFT JOIN product_category_modification category_offer_modification ON category_offer_modification.id = product_modification.category_modification
LEFT JOIN product_modification_images product_modification_image ON product_modification_image.modification = product_modification.id
LEFT JOIN product_invariable product_invariable ON product_invariable.product = product.id
AND (
(
product_offer.const IS NOT NULL
AND product_invariable.offer = product_offer.const
)
OR (
product_offer.const IS NULL
AND product_invariable.offer IS NULL
)
)
AND (
(
product_variation.const IS NOT NULL
AND product_invariable.variation = product_variation.const
)
OR (
product_variation.const IS NULL
AND product_invariable.variation IS NULL
)
)
AND (
(
product_modification.const IS NOT NULL
AND product_invariable.modification = product_modification.const
)
OR (
product_modification.const IS NULL
AND product_invariable.modification IS NULL
)
)
INNER JOIN product_category category ON category.id = product_event_category.category
AND category.id = ?
LEFT JOIN product_category_info category_info ON category_info.event = category.event
LEFT JOIN user_profile_discount project_profile_discount ON project_profile_discount.event = project_profile.event
WHERE
(
CASE WHEN product_modification_price.price IS NOT NULL THEN
product_modification_price.price
WHEN product_variation_price.price IS NOT NULL THEN
product_variation_price.price
WHEN product_offer_price.price IS NOT NULL THEN
product_offer_price.price
WHEN product_price.price IS NOT NULL THEN
product_price.price
ELSE
0
END > 0
)
AND (
CASE WHEN product_modification_quantity.quantity IS NOT NULL THEN
(
product_modification_quantity.quantity - product_modification_quantity.reserve
)
WHEN product_variation_quantity.quantity IS NOT NULL THEN
(
product_variation_quantity.quantity - product_variation_quantity.reserve
)
WHEN product_offer_quantity.quantity IS NOT NULL THEN
(
product_offer_quantity.quantity - product_offer_quantity.reserve
)
WHEN product_price.quantity IS NOT NULL THEN
(
product_price.quantity - product_price.reserve
)
ELSE
0
END > 0
)
GROUP BY
product_price.price,
product_price.currency,
product_price.reserve,
product_price.quantity,
product_offer_quantity.quantity,
product_offer_quantity.reserve,
product_offer_price.price,
product_offer_price.currency,
product_variation_quantity.quantity,
product_variation_quantity.reserve,
product_variation_price.price,
product_variation_price.currency,
product_modification_quantity.quantity,
product_modification_quantity.reserve,
product_modification_price.price,
product_modification_price.currency,
product_trans.name,
product_info.url,
product_info.sort,
product_offer.id,
product_offer.value,
product_offer.postfix,
category_offer.reference,
product_variation.id,
product_variation.value,
product_variation.postfix,
category_offer_variation.reference,
product_modification.id,
product_modification.value,
product_modification.postfix,
category_offer_modification.reference,
category.id,
category_info.url,
product_modification_price.price,
product_variation_price.price,
product_offer_price.price,
product_price.price,
product_modification_price.old,
product_variation_price.old,
product_offer_price.old,
product_price.old,
product_modification_price.currency,
product_variation_price.currency,
product_offer_price.currency,
product_price.currency,
product_invariable.id,
project_profile_discount.value
ORDER BY
product_modification_quantity.reserve DESC,
product_variation_quantity.reserve DESC,
product_offer_quantity.reserve DESC,
product_price.reserve DESC
LIMIT
2
SELECT product_trans.name AS product_name, product_info.url, product_info.sort, product_offer.id as product_offer_uid, product_offer.value as product_offer_value, product_offer.postfix as product_offer_postfix, category_offer.reference as product_offer_reference, product_variation.id as product_variation_uid, product_variation.value as product_variation_value, product_variation.postfix as product_variation_postfix, category_offer_variation.reference as product_variation_reference, product_modification.id as product_modification_uid, product_modification.value as product_modification_value, product_modification.postfix as product_modification_postfix, category_offer_modification.reference as product_modification_reference, category.id as category_id, category_info.url AS category_url,
COALESCE(
NULLIF(product_modification_price.price, 0),
NULLIF(product_variation_price.price, 0),
NULLIF(product_offer_price.price, 0),
NULLIF(product_price.price, 0),
0
) AS product_price
,
COALESCE(
NULLIF(product_modification_price.old, 0),
NULLIF(product_variation_price.old, 0),
NULLIF(product_offer_price.old, 0),
NULLIF(product_price.old, 0),
0
) AS product_old_price
,
CASE
WHEN COALESCE(product_modification_price.price, 0) != 0
THEN product_modification_price.currency
WHEN COALESCE(product_variation_price.price, 0) != 0
THEN product_variation_price.currency
WHEN COALESCE(product_offer_price.price, 0) != 0
THEN product_offer_price.currency
WHEN COALESCE(product_price.price, 0) != 0
THEN product_price.currency
ELSE NULL
END AS product_currency, JSON_AGG
(DISTINCT
CASE
WHEN product_offer_images.ext IS NOT NULL
THEN JSONB_BUILD_OBJECT
(
'img_root', product_offer_images.root,
'img', CONCAT ( '/upload/product_offer_images' , '/', product_offer_images.name),
'img_ext', product_offer_images.ext,
'img_cdn', product_offer_images.cdn
)
WHEN product_variation_image.ext IS NOT NULL
THEN JSONB_BUILD_OBJECT
(
'img_root', product_variation_image.root,
'img', CONCAT ( '/upload/product_variation_images' , '/', product_variation_image.name),
'img_ext', product_variation_image.ext,
'img_cdn', product_variation_image.cdn
)
WHEN product_modification_image.ext IS NOT NULL
THEN JSONB_BUILD_OBJECT
(
'img_root', product_modification_image.root,
'img', CONCAT ( '/upload/product_modification_images' , '/', product_modification_image.name),
'img_ext', product_modification_image.ext,
'img_cdn', product_modification_image.cdn
)
WHEN product_photo.ext IS NOT NULL
THEN JSONB_BUILD_OBJECT
(
'img_root', product_photo.root,
'img', CONCAT ( '/upload/product_photo' , '/', product_photo.name),
'img_ext', product_photo.ext,
'img_cdn', product_photo.cdn
)
END) AS product_images, product_invariable.id AS product_invariable_id, project_profile_discount.value AS project_discount FROM product LEFT JOIN product_trans product_trans ON product_trans.event = product.event AND product_trans.local = 'ru' LEFT JOIN product_price product_price ON product_price.event = product.event LEFT JOIN product_info product_info ON product_info.product = product.id LEFT JOIN product_offer product_offer ON product_offer.event = product.event LEFT JOIN product_categories_product product_event_category ON
product_event_category.event = product.event AND
product_event_category.root = true LEFT JOIN product_photo product_photo ON product_photo.event = product.event INNER JOIN users_profile project_profile ON
project_profile.id = '019469c3-700f-76a9-9b34-ccde7b4e6f49' LEFT JOIN product_offer_quantity product_offer_quantity ON product_offer_quantity.offer = product_offer.id LEFT JOIN product_offer_price product_offer_price ON product_offer_price.offer = product_offer.id LEFT JOIN product_category_offers category_offer ON category_offer.id = product_offer.category_offer LEFT JOIN product_variation product_variation ON product_variation.id = (SELECT tmp_product_variation.id FROM product_variation tmp_product_variation WHERE tmp_product_variation.offer = product_offer.id ORDER BY tmp_product_variation.id DESC LIMIT 1) LEFT JOIN product_variation_images product_variation_image ON product_variation_image.variation = product_variation.id LEFT JOIN product_offer_images product_offer_images ON product_offer_images.offer = product_offer.id LEFT JOIN product_variation_quantity product_variation_quantity ON product_variation_quantity.variation = product_variation.id LEFT JOIN product_variation_price product_variation_price ON product_variation_price.variation = product_variation.id LEFT JOIN product_category_variation category_offer_variation ON category_offer_variation.id = product_variation.category_variation LEFT JOIN product_modification product_modification ON product_modification.variation = product_variation.id LEFT JOIN product_modification_quantity product_modification_quantity ON product_modification_quantity.modification = product_modification.id LEFT JOIN product_modification_price product_modification_price ON product_modification_price.modification = product_modification.id LEFT JOIN product_category_modification category_offer_modification ON category_offer_modification.id = product_modification.category_modification LEFT JOIN product_modification_images product_modification_image ON product_modification_image.modification = product_modification.id LEFT JOIN product_invariable product_invariable ON
product_invariable.product = product.id AND
(
(product_offer.const IS NOT NULL AND product_invariable.offer = product_offer.const) OR
(product_offer.const IS NULL AND product_invariable.offer IS NULL)
)
AND
(
(product_variation.const IS NOT NULL AND product_invariable.variation = product_variation.const) OR
(product_variation.const IS NULL AND product_invariable.variation IS NULL)
)
AND
(
(product_modification.const IS NOT NULL AND product_invariable.modification = product_modification.const) OR
(product_modification.const IS NULL AND product_invariable.modification IS NULL)
)
INNER JOIN product_category category ON
category.id = product_event_category.category AND
category.id = '01905ea6-805e-7a56-b53c-da41c19f9e38' LEFT JOIN product_category_info category_info ON category_info.event = category.event LEFT JOIN user_profile_discount project_profile_discount ON
project_profile_discount.event = project_profile.event WHERE (
CASE
WHEN product_modification_price.price IS NOT NULL THEN product_modification_price.price
WHEN product_variation_price.price IS NOT NULL THEN product_variation_price.price
WHEN product_offer_price.price IS NOT NULL THEN product_offer_price.price
WHEN product_price.price IS NOT NULL THEN product_price.price
ELSE 0
END > 0) AND (
CASE
WHEN product_modification_quantity.quantity IS NOT NULL THEN (product_modification_quantity.quantity - product_modification_quantity.reserve)
WHEN product_variation_quantity.quantity IS NOT NULL THEN (product_variation_quantity.quantity - product_variation_quantity.reserve)
WHEN product_offer_quantity.quantity IS NOT NULL THEN (product_offer_quantity.quantity - product_offer_quantity.reserve)
WHEN product_price.quantity IS NOT NULL THEN (product_price.quantity - product_price.reserve)
ELSE 0
END > 0) GROUP BY product_price.price, product_price.currency, product_price.reserve, product_price.quantity, product_offer_quantity.quantity, product_offer_quantity.reserve, product_offer_price.price, product_offer_price.currency, product_variation_quantity.quantity, product_variation_quantity.reserve, product_variation_price.price, product_variation_price.currency, product_modification_quantity.quantity, product_modification_quantity.reserve, product_modification_price.price, product_modification_price.currency, product_trans.name, product_info.url, product_info.sort, product_offer.id, product_offer.value, product_offer.postfix, category_offer.reference, product_variation.id, product_variation.value, product_variation.postfix, category_offer_variation.reference, product_modification.id, product_modification.value, product_modification.postfix, category_offer_modification.reference, category.id, category_info.url, product_modification_price.price, product_variation_price.price, product_offer_price.price, product_price.price, product_modification_price.old, product_variation_price.old, product_offer_price.old, product_price.old, product_modification_price.currency, product_variation_price.currency, product_offer_price.currency, product_price.currency, product_invariable.id, project_profile_discount.value ORDER BY product_modification_quantity.reserve DESC, product_variation_quantity.reserve DESC, product_offer_quantity.reserve DESC, product_price.reserve DESC LIMIT 2;
|
21.54 ms
(4.76%)
|
1 |
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_offer_quantity offer_quantity ON offer_quantity.offer = offer.id 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_variation_quantity variation_quantity ON variation_quantity.variation = variation.id LEFT JOIN product_category_modification category_modification ON category_modification.id = modification.category_modification LEFT JOIN product_modification_quantity modification_quantity ON modification_quantity.modification = modification.id WHERE (
(category_offers.reference = ? AND offer.value IN (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)) OR
(category_variation.reference = ? AND variation.value IN (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)) OR
(category_modification.reference = ? AND modification.value IN (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?))
) AND ((modification_quantity.quantity > 0
OR variation_quantity.quantity > 0
OR offer_quantity.quantity > 0)) 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_offer_quantity offer_quantity ON offer_quantity.offer = offer.id
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_variation_quantity variation_quantity ON variation_quantity.variation = variation.id
LEFT JOIN product_category_modification category_modification ON category_modification.id = modification.category_modification
LEFT JOIN product_modification_quantity modification_quantity ON modification_quantity.modification = modification.id
WHERE
(
(
category_offers.reference = ?
AND offer.value IN (
?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?,
?, ?, ?, ?, ?, ?, ?, ?, ?, ?
)
)
OR (
category_variation.reference = ?
AND variation.value IN (
?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?,
?, ?, ?, ?, ?, ?, ?, ?, ?, ?
)
)
OR (
category_modification.reference = ?
AND modification.value IN (
?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?,
?, ?, ?, ?, ?, ?, ?, ?, ?, ?
)
)
)
AND (
(
modification_quantity.quantity > 0
OR variation_quantity.quantity > 0
OR offer_quantity.quantity > 0
)
)
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_offer_quantity offer_quantity ON offer_quantity.offer = offer.id 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_variation_quantity variation_quantity ON variation_quantity.variation = variation.id LEFT JOIN product_category_modification category_modification ON category_modification.id = modification.category_modification LEFT JOIN product_modification_quantity modification_quantity ON modification_quantity.modification = modification.id 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'))
) AND ((modification_quantity.quantity > 0
OR variation_quantity.quantity > 0
OR offer_quantity.quantity > 0)) ORDER BY value;
|
15.15 ms
(3.35%)
|
1 |
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_offer_quantity offer_quantity ON offer_quantity.offer = offer.id 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_variation_quantity variation_quantity ON variation_quantity.variation = variation.id LEFT JOIN product_category_modification category_modification ON category_modification.id = modification.category_modification LEFT JOIN product_modification_quantity modification_quantity ON modification_quantity.modification = modification.id WHERE (
(category_offers.reference = ? AND offer.value IN (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)) OR
(category_variation.reference = ? AND variation.value IN (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)) OR
(category_modification.reference = ? AND modification.value IN (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?))
) AND ((modification_quantity.quantity > 0
OR variation_quantity.quantity > 0
OR offer_quantity.quantity > 0)) 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_offer_quantity offer_quantity ON offer_quantity.offer = offer.id
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_variation_quantity variation_quantity ON variation_quantity.variation = variation.id
LEFT JOIN product_category_modification category_modification ON category_modification.id = modification.category_modification
LEFT JOIN product_modification_quantity modification_quantity ON modification_quantity.modification = modification.id
WHERE
(
(
category_offers.reference = ?
AND offer.value IN (
?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?,
?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?
)
)
OR (
category_variation.reference = ?
AND variation.value IN (
?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?,
?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?
)
)
OR (
category_modification.reference = ?
AND modification.value IN (
?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?,
?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?
)
)
)
AND (
(
modification_quantity.quantity > 0
OR variation_quantity.quantity > 0
OR offer_quantity.quantity > 0
)
)
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_offer_quantity offer_quantity ON offer_quantity.offer = offer.id 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_variation_quantity variation_quantity ON variation_quantity.variation = variation.id LEFT JOIN product_category_modification category_modification ON category_modification.id = modification.category_modification LEFT JOIN product_modification_quantity modification_quantity ON modification_quantity.modification = modification.id 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))
) AND ((modification_quantity.quantity > 0
OR variation_quantity.quantity > 0
OR offer_quantity.quantity > 0)) ORDER BY value;
|
12.66 ms
(2.80%)
|
1 |
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_offer_quantity offer_quantity ON offer_quantity.offer = offer.id 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_variation_quantity variation_quantity ON variation_quantity.variation = variation.id LEFT JOIN product_category_modification category_modification ON category_modification.id = modification.category_modification LEFT JOIN product_modification_quantity modification_quantity ON modification_quantity.modification = modification.id WHERE (
(category_offers.reference = ? AND offer.value IN (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)) OR
(category_variation.reference = ? AND variation.value IN (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)) OR
(category_modification.reference = ? AND modification.value IN (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?))
) AND ((modification_quantity.quantity > 0
OR variation_quantity.quantity > 0
OR offer_quantity.quantity > 0)) 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_offer_quantity offer_quantity ON offer_quantity.offer = offer.id
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_variation_quantity variation_quantity ON variation_quantity.variation = variation.id
LEFT JOIN product_category_modification category_modification ON category_modification.id = modification.category_modification
LEFT JOIN product_modification_quantity modification_quantity ON modification_quantity.modification = modification.id
WHERE
(
(
category_offers.reference = ?
AND offer.value IN (
?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?
)
)
OR (
category_variation.reference = ?
AND variation.value IN (
?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?
)
)
OR (
category_modification.reference = ?
AND modification.value IN (
?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?
)
)
)
AND (
(
modification_quantity.quantity > 0
OR variation_quantity.quantity > 0
OR offer_quantity.quantity > 0
)
)
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_offer_quantity offer_quantity ON offer_quantity.offer = offer.id 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_variation_quantity variation_quantity ON variation_quantity.variation = variation.id LEFT JOIN product_category_modification category_modification ON category_modification.id = modification.category_modification LEFT JOIN product_modification_quantity modification_quantity ON modification_quantity.modification = modification.id 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))
) AND ((modification_quantity.quantity > 0
OR variation_quantity.quantity > 0
OR offer_quantity.quantity > 0)) ORDER BY value;
|
4.06 ms
(0.90%)
|
1 |
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';
|
1.63 ms
(0.36%)
|
1 |
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';
|
1.22 ms
(0.27%)
|
1 |
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';
|