I have small query to getting products based on variants (colors, materials), like:
SELECT ANY_VALUE(id) AS id, parent_id, COUNT(id), color, material, MIN(price) AS priceMin FROM ( SELECT products.id, products.parent_id, products.price, colors.variant_option_id AS color, materials.variant_option_id AS material FROM products LEFT JOIN products_variant_options AS colors ON products.id = colors.product_id AND colors.variant_id = 1 # Colors LEFT JOIN products_variant_options AS materials ON products.id = materials.product_id AND materials.variant_id = 2 # Materials WHERE products.status = 1 ) AS product_variants GROUP BY parent_id, color, material ORDER BY priceMin
Everything you can see on https://www.db-fiddle.com/f/vfAfRWoo2vHKB6S1phE2dt/1
You see I need get
id of product.
When I using
ANY_VALUE for this, you see it row with selected price not returned correct ID - look product with
id=2 instead of
I know the function
MIN and awful
ANY_VALUE isn't good idea. I tried
OVER() but without effects.
How to sorting by prices from joined tables and getting ID during using group?