Which table couples the orders and the product attributes of the related orders such as size, color and price?

For example, if my order contains a shirt, I need to get the color and size of the shirt through a query.

magento version 1.6.11

  • You have mentioned Magento 1.x and 2.x both. Please specify exact version to get better and specific answers. Commented Oct 6, 2017 at 8:53

1 Answer 1


After submitting the a solution for serialized data, I did some digging, and came up with a query that (while containing a lot of scary-looking joins) should give you the information that you are looking for:

    o.entity_id 'order_id', 
    qio.code 'option_id', 
    coptv.sku 'option_value'
from sales_flat_order o
join sales_flat_quote_item qi
    on o.quote_id = qi.quote_id
join sales_flat_quote_item_option qio
    on qi.item_id = qio.item_id
join catalog_product_option_type_value coptv
    on qio.value = coptv.option_type_id
where qio.code like 'option_%';

This may not be the most efficient way of selecting, and you may want to look at the other columns in the catalog_product_option_title table, as well as the four included above, but the output should be something like this:

order_id    product_id    option_id    option_value
1234        456          'option_78'   'WildCherry-Finish'
  • I need the result in unserialized format. Is there a any other table that I can refer to, to get these information about a product of a given order?
    – dilani
    Commented Oct 9, 2017 at 4:05
  • I updated the answer above. Let me know if this works for you
    – mtr.web
    Commented Oct 9, 2017 at 18:40

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.