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,
    cv.value AS product_name,
    t.title AS custom_option_title,
    GROUP_CONCAT(ot.title ORDER BY ov.sort_order SEPARATOR ', ') AS custom_option_values,
    GROUP_CONCAT(price.price ORDER BY ov.sort_order SEPARATOR ', ') AS option_prices
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
    (
        SELECT
            ea.attribute_id
        FROM
            eav_attribute ea
        JOIN
            eav_entity_type et
            ON et.entity_type_id = ea.entity_type_id
        WHERE
            et.entity_type_code = 'catalog_product'
            AND ea.attribute_code = 'name'
    ) AS name_attribute
JOIN
    catalog_product_entity_varchar cv
    ON cv.entity_id = e.entity_id
    AND cv.attribute_id = name_attribute.attribute_id
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,
    t.title,
    cv.value
ORDER BY
    e.sku,
    t.title;

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!