SELECT elements.id, elements.canonicalId, elements.fieldLayoutId, elements.uid, elements.enabled, elements.archived, elements.dateLastMerged, elements.dateCreated, elements.dateUpdated, elements_sites.id AS siteSettingsId, elements_sites.siteId, elements_sites.title, elements_sites.slug, elements_sites.uri, elements_sites.content, elements_sites.enabled AS enabledForSite, commerce_variants.id, commerce_variants.primaryOwnerId, CASE WHEN commerce_variants.id = commerce_products.defaultVariantId THEN TRUE ELSE FALSE END AS isDefault, commerce_products_elements_sites.slug AS productSlug, commerce_producttypes.handle AS productTypeHandle, elements_owners.ownerId, elements_owners.sortOrder, commerce_purchasables.sku, commerce_purchasables.width, commerce_purchasables.height, commerce_purchasables.length, commerce_purchasables.weight, commerce_purchasables.taxCategoryId, purchasables_stores.availableForPurchase, purchasables_stores.basePrice, purchasables_stores.basePromotionalPrice, purchasables_stores.freeShipping, purchasables_stores.maxQty, purchasables_stores.minQty, purchasables_stores.inventoryTracked, purchasables_stores.promotable, purchasables_stores.shippingCategoryId, subquery.price, subquery.promotionalPrice AS promotionalPrice, subquery.salePrice AS salePrice, inventoryitems.id AS inventoryItemId
FROM (
    SELECT catalogprices.price, catalogprices.promotionalPrice, catalogprices.salePrice, elements.id AS elementsId, elements_sites.id AS siteSettingsId
    FROM elements elements
    INNER JOIN commerce_variants commerce_variants ON commerce_variants.id = elements.id
    INNER JOIN elements_owners elements_owners ON (elements_owners.elementId = elements.id) AND (elements_owners.ownerId=95250)
    LEFT JOIN commerce_products commerce_products ON elements_owners.ownerId = commerce_products.id
    LEFT JOIN commerce_producttypes commerce_producttypes ON commerce_products.typeId = commerce_producttypes.id
    INNER JOIN commerce_purchasables commerce_purchasables ON commerce_purchasables.id = elements.id
    INNER JOIN elements_sites elements_sites ON elements_sites.elementId = elements.id
    LEFT JOIN commerce_site_stores sitestores ON elements_sites.siteId = sitestores.siteId
    LEFT JOIN commerce_purchasables_stores purchasables_stores ON purchasables_stores.storeId = sitestores.storeId AND purchasables_stores.purchasableId = commerce_purchasables.id
    LEFT JOIN (
        SELECT MIN(CASE WHEN isPromotionalPrice = FALSE THEN price END) AS price, MIN(CASE WHEN isPromotionalPrice = TRUE THEN price END) AS promotionalPrice, MIN(price) AS salePrice, cp.purchasableId, cp.storeId
        FROM commerce_catalogpricing cp
        WHERE ((catalogPricingRuleId IS NULL) OR (
            catalogPricingRuleId IN (
                SELECT cpr.id AS cprid FROM commerce_catalogpricingrules cpr
                LEFT JOIN commerce_catalogpricingrules_users cpru ON cpr.id = cpru.catalogPricingRuleId
                WHERE cpru.id IS NULL
                GROUP BY cpr.id
                )
            ) OR (
            catalogPricingRuleId IN (
                SELECT cpr.id AS cprid
                FROM commerce_catalogpricingrules cpr
                LEFT JOIN commerce_catalogpricingrules_users cpru ON cpr.id = cpru.catalogPricingRuleId
                WHERE (cpru.userId=1) AND (NOT (cpru.id IS NULL))
                GROUP BY cpr.id
                )
            )
        )
        AND ((dateFrom IS NULL) OR (dateFrom <= '2024-10-03 16:00:44')) AND ((dateTo IS NULL) OR (dateTo >= '2024-10-03 16:00:44'))
        GROUP BY purchasableId, storeId
        ) catalogprices ON catalogprices.purchasableId = commerce_purchasables.id AND catalogprices.storeId = sitestores.storeId
    LEFT JOIN commerce_inventoryitems inventoryitems ON inventoryitems.purchasableId = commerce_purchasables.id
    WHERE (elements.archived=FALSE) AND (elements.dateDeleted IS NULL) AND (elements.draftId IS NULL) AND (elements.revisionId IS NULL)
    ORDER BY sortOrder
) subquery
INNER JOIN elements elements ON elements.id = subquery.elementsId
INNER JOIN elements_sites elements_sites ON elements_sites.id = subquery.siteSettingsId
INNER JOIN commerce_variants commerce_variants ON commerce_variants.id = subquery.elementsId
INNER JOIN elements_owners elements_owners ON (elements_owners.elementId = elements.id) AND (elements_owners.ownerId=95250)
LEFT JOIN commerce_products commerce_products ON elements_owners.ownerId = commerce_products.id
LEFT JOIN commerce_producttypes commerce_producttypes ON commerce_products.typeId = commerce_producttypes.id
LEFT JOIN elements_sites commerce_products_elements_sites ON elements_owners.ownerId = commerce_products_elements_sites.elementId and commerce_products_elements_sites.siteId =  elements_sites.siteId
INNER JOIN commerce_purchasables commerce_purchasables ON commerce_purchasables.id = subquery.elementsId
LEFT JOIN commerce_site_stores sitestores ON elements_sites.siteId = sitestores.siteId
LEFT JOIN commerce_purchasables_stores purchasables_stores ON purchasables_stores.storeId = sitestores.storeId AND purchasables_stores.purchasableId = commerce_purchasables.id
LEFT JOIN commerce_inventoryitems inventoryitems ON inventoryitems.purchasableId = commerce_purchasables.id
ORDER BY sortOrder