Shopware DB query constructed by ProductListingVariantLoader fetchPrices

ProductListingVariantLoader_fetchPrices.sql

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));