Skip to content

Instantly share code, notes, and snippets.

@htuscher
Created September 3, 2018 09:20
Show Gist options
  • Save htuscher/c6b4d98d5dcb1528a3792479fb9dcfc5 to your computer and use it in GitHub Desktop.
Save htuscher/c6b4d98d5dcb1528a3792479fb9dcfc5 to your computer and use it in GitHub Desktop.
Shopware DB query constructed by ProductListingVariantLoader fetchPrices
SELECT DISTINCT availableVariant.articleID,
relations.article_id as variant_id,
prices.price as price,
relations.option_id,
options.group_id
FROM s_articles_details availableVariant
INNER JOIN s_articles product ON availableVariant.articleId = product.id
INNER JOIN (SELECT IFNULL(prices.articleID, onsalePriceList.articleID) as articleID,
IFNULL(prices.articledetailsID, onsalePriceList.articledetailsID) as articledetailsID,
IFNULL(prices.price, onsalePriceList.price) as price
FROM s_articles_details details
LEFT JOIN (SELECT prices.`articledetailsID` as articledetailsID,
prices.`articleID` as articleID,
ROUND(prices.price * ((100 - IFNULL(priceGroup.discount, 0)) / 100) *
(((CASE tax.id WHEN 1 THEN 19 WHEN 4 THEN 7 WHEN 5 THEN 20 END) + 100) /
100) * 1, 2)as price
FROM s_articles product
INNER JOIN s_articles_details availableVariant
ON availableVariant.articleId = product.id
INNER JOIN s_core_tax tax ON tax.id = product.taxID
LEFT JOIN s_core_pricegroups_discounts priceGroup
ON priceGroup.groupID = product.pricegroupID
AND priceGroup.discountstart = 1
AND priceGroup.customergroupID = :priceGroupCustomerGroup
AND product.pricegroupActive = 1
INNER JOIN (SELECT IFNULL(customerPrice.`id`, defaultPrice.`id`) as `id`,
IFNULL(customerPrice.`pricegroup`, defaultPrice.`pricegroup`) as `pricegroup`,
IFNULL(customerPrice.`from`, defaultPrice.`from`) as `from`,
IFNULL(customerPrice.`to`, defaultPrice.`to`) as `to`,
IFNULL(customerPrice.`articleID`, defaultPrice.`articleID`) as `articleID`,
IFNULL(customerPrice.`articledetailsID`,
defaultPrice.`articledetailsID`) as `articledetailsID`,
IFNULL(customerPrice.`price`, defaultPrice.`price`) as `price`,
IFNULL(customerPrice.`pseudoprice`, defaultPrice.`pseudoprice`) as `pseudoprice`,
IFNULL(customerPrice.`baseprice`, defaultPrice.`baseprice`) as `baseprice`,
IFNULL(customerPrice.`percent`, defaultPrice.`percent`) as `percent`
FROM s_articles_prices defaultPrice
LEFT JOIN s_articles_prices customerPrice
ON customerPrice.articledetailsID = defaultPrice.articledetailsID
AND
customerPrice.pricegroup = :currentCustomerGroup
WHERE (defaultPrice.pricegroup = :fallbackCustomerGroup)
AND (defaultPrice.articledetailsID IN (:variants))) prices
ON availableVariant.id = prices.articledetailsID
INNER JOIN s_article_configurator_option_relations relations
ON relations.article_id = prices.articledetailsID
INNER JOIN s_article_configurator_options options
ON relations.option_id = options.id
WHERE (prices.articleID IN (:products))
AND (prices.articledetailsID IN (:variants))
AND (prices.from = 1)
AND (availableVariant.laststock * availableVariant.instock >=
availableVariant.laststock * availableVariant.minpurchase)) prices
ON details.id = prices.articledetailsID
LEFT JOIN (SELECT prices.`articledetailsID` as articledetailsID,
prices.`articleID` as articleID,
ROUND(prices.price * ((100 - IFNULL(priceGroup.discount, 0)) / 100) *
(((CASE tax.id WHEN 1 THEN 19 WHEN 4 THEN 7 WHEN 5 THEN 20 END) + 100) /
100) * 1, 2)as price
FROM s_articles product
INNER JOIN s_articles_details availableVariant
ON availableVariant.articleId = product.id
INNER JOIN s_core_tax tax ON tax.id = product.taxID
LEFT JOIN s_core_pricegroups_discounts priceGroup
ON priceGroup.groupID = product.pricegroupID
AND priceGroup.discountstart = 1
AND priceGroup.customergroupID = :priceGroupCustomerGroup
AND product.pricegroupActive = 1
INNER JOIN (SELECT IFNULL(customerPrice.`id`, defaultPrice.`id`) as `id`,
IFNULL(customerPrice.`pricegroup`, defaultPrice.`pricegroup`) as `pricegroup`,
IFNULL(customerPrice.`from`, defaultPrice.`from`) as `from`,
IFNULL(customerPrice.`to`, defaultPrice.`to`) as `to`,
IFNULL(customerPrice.`articleID`, defaultPrice.`articleID`) as `articleID`,
IFNULL(customerPrice.`articledetailsID`,
defaultPrice.`articledetailsID`) as `articledetailsID`,
IFNULL(customerPrice.`price`, defaultPrice.`price`) as `price`,
IFNULL(customerPrice.`pseudoprice`, defaultPrice.`pseudoprice`) as `pseudoprice`,
IFNULL(customerPrice.`baseprice`, defaultPrice.`baseprice`) as `baseprice`,
IFNULL(customerPrice.`percent`, defaultPrice.`percent`) as `percent`
FROM s_articles_prices defaultPrice
LEFT JOIN s_articles_prices customerPrice
ON customerPrice.articledetailsID = defaultPrice.articledetailsID
AND
customerPrice.pricegroup = :currentCustomerGroup
WHERE (defaultPrice.pricegroup = :fallbackCustomerGroup)
AND (defaultPrice.articledetailsID IN (:variants))) prices
ON availableVariant.id = prices.articledetailsID
INNER JOIN s_article_configurator_option_relations relations
ON relations.article_id = prices.articledetailsID
INNER JOIN s_article_configurator_options options
ON relations.option_id = options.id
WHERE (prices.articleID IN (:products))
AND (prices.articledetailsID IN (:variants))
AND (prices.from = 1)) onsalePriceList
ON details.id = onsalePriceList.articledetailsID
WHERE details.id IN (:variants)) prices
ON availableVariant.id = prices.articledetailsID
INNER JOIN s_article_configurator_option_relations relations ON relations.article_id = prices.articledetailsID
INNER JOIN s_article_configurator_options options ON relations.option_id = options.id
WHERE (availableVariant.active = 1) AND availableVariant.id IN (:variants)
AND (prices.articleID IN (:products));
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment