Magento – SQL Query for gettin all products with custom options

magento-1.9querysql

Need help making a query which returns the sku, name, custom option name and Custom option values for all products with custom options.

Example:

sku | name | Custom option name | Custom option values

1 | Product 1 | Width | M, L

            Height | Red, Blue

2 | Product 2 | Width | M, L

            Height | Red, Yellow

Best Answer

With Price

SELECT e.sku
      ,t.title as 'name' 
      ,cv.value as 'Custom option name'
      ,GROUP_CONCAT(ot.title) as 'Custom option values'
      ,GROUP_CONCAT(price.price) as 'price'
FROM catalog_product_option o 
JOIN catalog_product_entity e ON e.entity_id=o.product_id 
JOIN catalog_product_option_title t ON t.option_id=o.option_id 

JOIN catalog_product_entity_varchar cv ON 
                   cv.entity_id=e.entity_id AND 
                   cv.attribute_id=(SELECT attribute_id 
                                    FROM eav_attribute ea 
                                    JOIN eav_entity_type et 
                                    ON  
                                         et.entity_type_code='catalog_product' 
                                         AND et.entity_type_id=ea.entity_type_id 
                                    WHERE ea.attribute_code='name')
LEFT JOIN catalog_product_option_type_value ov ON
           ov.option_id=o.option_id 
LEFT JOIN catalog_product_option_type_title ot ON 
           ot.option_type_id=ov.option_type_id 
left join catalog_product_option_type_price price on price.option_type_id = ot.option_type_id
GROUP BY e.sku, ov.option_id;
Related Topic