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:
|
1 |
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:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 |
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.
Got any doubts? Feel free to comment below. I’ll be happy to help you. 😊
Also, do not forget to share this helpful Magento 2 tutorial with your friends via social media. 😃
Thanks for reading! 🍀
Prev
Meetanshi Turns 5! – Sneak Peek at Meetanshiversary Celebration
10 Instagram Statistics and Facts You Must Know [2024]
Next