3

I need to query the Magento MySQL database to get an image path/name for a file.

What I have:

  • Order ID
  • Order Number

What I need:

  • I need to get the image name/path from this table sales_flat_quote_item_option

From what I can see so far following the foreign keys that lead to this table. I seem none of them use the order ID or order number as they all descend from the sales_flat_quote table instead of the sales_flat_order table.


Does anyone know of a fancy query I could use to get a value from the sales_flat_quote_item_option table by using what I have, order ID and number?

If I can simply get the Quote ID from my Order ID/Number then I can doing a join SQL query using the Quote ID to get my record from the sales_flat_quote_item_option table.

I have to do this with SQL instead of Magento Models as it will be ran outside of Magento in a separate application.

  • You should be able to use the quote_id from the sales_flat_order table to get the Quote ID you need – CCBlackburn Dec 1 '15 at 3:06
  • @CCBlackburn thanks I overlooked that existing! – JasonDavis Dec 1 '15 at 3:10
3

There are only one relation between Magento quote item tables & sales order tables and that is sales_flat_order.quote_id=sales_flat_quote_item.quote_id.

In magento,sales_flat_quote_item_option does not have any direct relation with sales_flat_order.It have an indirection relation

sales_flat_order.quote_id=sales_flat_quote.quote_id=> sales_flat_quote_item.item_id=sales_flat_quote_item_option.item_id

Query seem like:

SELECT * FROM sales_flat_quote_item_option where item_id in (
        select item_id from  sales_flat_quote_item  where quote_id in (
            SELECT quote_id FROM sales_flat_order  where entity_id =ORDER_ID

        )

)
  • Thanks I cant remeber how I got past my issue as it was about a month ago however this may help me in the future or help someone else. I just ran the SQL to do quick test and it seems to work! SO thanks for sharing and hope it helps others too – JasonDavis Jan 16 '16 at 0:29
  • PS) my own question confuses me as im not working in Magento for a month so amazing work! – JasonDavis Jan 16 '16 at 0:31

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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