1

I'm trying to get all orders with a specific option id to see what percentage of orders are ordered with measurement of centimeters.

Not very good with database queries and I can't seem to figure it out.

I've looked inside the sales_order_item table but it doesn't have an option_id column.

I need to find another way around this.

2 Answers 2

1

Using Database :-

Query :-

SELECT * FROM `sales_order_item` WHERE `product_options` LIKE '%2010%'

Replace 2010 with Your Option Id


In sales_order_item this table saved option id in product_options coumn with json Format.

You can decode this product_options And Apply Filter

json decode :-

json_decode($product_options)
10
  • I need to get the amount of order items with that specific option id, is there no way of doing that through the database?
    – Howliee
    Commented Sep 4, 2019 at 9:52
  • you mean filter in database ? Commented Sep 4, 2019 at 9:53
  • Yes, sorry I'm not very good with databases
    – Howliee
    Commented Sep 4, 2019 at 9:54
  • ok let me check. Commented Sep 4, 2019 at 9:55
  • check updated answer Commented Sep 4, 2019 at 9:58
0

The product options are saved as JSON.

With the MySQL version required by Magento 2, there seems to be no nice solution for this:

  1. You can filter using LIKE "%2010%" or some more sophisticated expression to emulate JSON processing

  2. As a second step you should json_decode the column within PHP to really check for the product_option in the JSON. That means 1. is just some kind of performance optimization - and not reliable.

Alternative:

If you MySQL version is a recent one, you can use JSON_EXTRACT.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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