SQL Query to Get All Products With Custom Options in Magento 2

Offering customization options is one of the most common ways to meet your customers’ requirements and increase your sales.

By default, Magento 2 offers the functionality of custom options to store owners that enables product customization up to a significant extent. It is highly useful for merchants selling products that come in different variants or need inputs from the customer’s end. Additionally, Use SQL Query for Case Sensitive Data in Magento 2 is vital, especially when dealing with database operations that require sensitivity to letter casing.

Customizable options in Magento 2 can be added and configured for the products from the product edit section. You can find the different custom options and their values in the same section. But, what if you want to:

• Get the list of all products with custom options in Magento 2 using SQL?
• Find values of different custom options in Magento 2 using SQL?

Earlier, we posted a complete method to create custom options programmatically in Magento 2.

In this Magento 2 solution post, I will share the SQL query to get all products with custom options in Magento 2.

Let’s get started!

Method to Find All Products With Custom Options Using MySQL in Magento 2

In order to get all the products with custom options in Magento 2 through SQL, we need run the following query:

SELECT * FROM catalog_product_entity WHERE type_id NOT LIKE 'configurable' AND type_id NOT LIKE 'bundle' AND has_options =1

The query returns a list of all the products that are neither configurable nor bundled, and have options enabled.

Now, you have a list of all the products that have custom options enabled in your store.

Wanna dig deeper?

Use the following SQL query to find custom options and their values of individual products in Magento 2:

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;

That’s it!

Run the above query and you will get all the custom options-enabled products in Magento 2 along with detailed information about the values of each of the options.

Also, do not forget to share this helpful Magento 2 tutorial with your friends via social media.

Thanks for reading!