1

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
2

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;
2
  • How can i get all products sku by store wise using sql query. – zus Mar 9 '18 at 8:58
  • With the price, wonderful. I was just about to include price and found yours. For me to works nicely on Magento 2.2.2 – Mohammed Joraid Mar 16 '18 at 4:24
0
SELECT e.sku
      ,t.title as 'name' 
      ,cv.value as 'Custom option name'
      ,GROUP_CONCAT(ot.title) as 'Custom option values'
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 
GROUP BY e.sku, ov.option_id;

tested on magento 1.9.2.3 MySQL 5.6.21

don't forget to add table prefix if you have one - check app/etc/local.xml (table_prefix tag)

sample output

+---------+-----------------------+----------------------------------------+----------------------+
| sku     | name                  | Custom option name                              | Custom option values |
+---------+-----------------------+------------------------------------  ----+----------------------+
| abl000  | Monogramming          | Isla Crossbody Handbag                      | NULL                 |
| mem000  | Membership start date | Madison Island VIP Membership - 1 Year | NULL                 |
| mtk004c | monogram              | Chelsea Tee                            | NULL                 |
| mtk004c | Test Custom Options   | Chelsea Tee                             | model 2,model 1      |
+---------+-----------------------+----------------------------------------+----------------------+

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.