In order to get all the products with custom options in Magento 2 through SQL, we need run the following query:<\/p>\n\n\n\n
SELECT\n *\nFROM\n catalog_product_entity\nWHERE\n type_id NOT LIKE 'configurable'\n AND type_id NOT LIKE 'bundle'\n AND has_options = 1;<\/pre>\n\n\n\nThe query returns a list of all the products that are neither configurable nor bundled, and have options enabled.<\/p>\n\n\n\n
Now, you have a list of all the products that have custom options enabled in your store.<\/p>\n\n\n\n
Wanna dig deeper?<\/p>\n\n\n\n
Use the following SQL query to find custom options and their values of individual products in Magento 2:<\/p>\n\n\n\n
SELECT\n e.sku,\n cv.value AS product_name,\n t.title AS custom_option_title,\n GROUP_CONCAT(ot.title ORDER BY ov.sort_order SEPARATOR ', ') AS custom_option_values,\n GROUP_CONCAT(price.price ORDER BY ov.sort_order SEPARATOR ', ') AS option_prices\nFROM\n catalog_product_option o\nJOIN\n catalog_product_entity e\n ON e.entity_id = o.product_id\nJOIN\n catalog_product_option_title t\n ON t.option_id = o.option_id\nJOIN\n (\n SELECT\n ea.attribute_id\n FROM\n eav_attribute ea\n JOIN\n eav_entity_type et\n ON et.entity_type_id = ea.entity_type_id\n WHERE\n et.entity_type_code = 'catalog_product'\n AND ea.attribute_code = 'name'\n ) AS name_attribute\nJOIN\n catalog_product_entity_varchar cv\n ON cv.entity_id = e.entity_id\n AND cv.attribute_id = name_attribute.attribute_id\nLEFT JOIN\n catalog_product_option_type_value ov\n ON ov.option_id = o.option_id\nLEFT JOIN\n catalog_product_option_type_title ot\n ON ot.option_type_id = ov.option_type_id\nLEFT JOIN\n catalog_product_option_type_price price\n ON price.option_type_id = ot.option_type_id\nGROUP BY\n e.sku,\n t.title,\n cv.value\nORDER BY\n e.sku,\n t.title;<\/pre>\n\n\n\nThat’s it!<\/p>\n\n\n\n
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.<\/p>\n\n\n\n
Also, do not forget to share this helpful Magento 2 tutorial with your friends via social media. <\/p>\n\n\n\n
Thanks for reading! <\/p>\n","protected":false},"excerpt":{"rendered":"
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…<\/p>\n","protected":false},"author":38,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"footnotes":""},"categories":[34],"tags":[],"class_list":["post-2168","post","type-post","status-publish","format-standard","hentry","category-magento"],"acf":[],"_links":{"self":[{"href":"https:\/\/meetanshi.com\/blog\/wp-json\/wp\/v2\/posts\/2168","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/meetanshi.com\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/meetanshi.com\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/meetanshi.com\/blog\/wp-json\/wp\/v2\/users\/38"}],"replies":[{"embeddable":true,"href":"https:\/\/meetanshi.com\/blog\/wp-json\/wp\/v2\/comments?post=2168"}],"version-history":[{"count":5,"href":"https:\/\/meetanshi.com\/blog\/wp-json\/wp\/v2\/posts\/2168\/revisions"}],"predecessor-version":[{"id":23071,"href":"https:\/\/meetanshi.com\/blog\/wp-json\/wp\/v2\/posts\/2168\/revisions\/23071"}],"wp:attachment":[{"href":"https:\/\/meetanshi.com\/blog\/wp-json\/wp\/v2\/media?parent=2168"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/meetanshi.com\/blog\/wp-json\/wp\/v2\/categories?post=2168"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/meetanshi.com\/blog\/wp-json\/wp\/v2\/tags?post=2168"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}