0

I've been trying without success to obtain the line level information about a specific order but i am getting the header information.

Take a look at this picture:

example

This is my query:

select o.increment_id, o.status, oi.price_incl_tax, t.carrier_code, o.created_at, oi.price_pvp, oi.created_at, o.shipping_instruction, o.payment_term
from sales_flat_order o
join sales_flat_order_item oi 
   on o.entity_id = oi.order_id 
left join sales_flat_shipment_track t 
   on o.entity_id = t.order_id 
where oi.parent_item_id is null

What's wrong? Can you please help me to solve this?

EDIT:

This is what i found in MYSQL:

image

The problem here is that i do not understand how should i look into this in order to obtain what i need :/.

Improve this question
7
  • shipping_instruction, payment_term are not fields of standard Magento in the sales_flat_order table. Therefore you should look into your customization if you would expect data in that fields - the SQL returns NULL because there is no data in that fields for the orders you have selected. Regarding the carrier_code: The tracking data is usually assigned to an order when something has been shipped, which might not be the case in your status waiting_approval (which again is not standard Magento but some customization). IMHO the SQL is correct and returns the data you have in database.
    – HelgeB
    Commented Apr 1, 2019 at 7:17
  • @HelgeB Thank you for replying, dude. I've edited my question. Have you got any idea about it? What should i do?
    – programming_amazing
    Commented Apr 1, 2019 at 12:52
  • You have the data of order lines ( sales_flat_order_item table) in your SQL joined to the order head (sales_flat_order table) data. For example the SKU comes from there. So I don't really understand what your problem is - it's not clear what you are looking for and what is not working. Again, your SQL is correct and returns oder lines joined to order head data.
    – HelgeB
    Commented Apr 1, 2019 at 13:05
  • @HelgeB I think my query it is supposed to show the line information for every order, right? Everytime that i execute it, it shows NULL in some fields:, i.e: shipping_information, payment methods and so on. Why? A guy told me that if it is returning NULL its because i am returning the HEADER information,NOT the LINE information :/
    – programming_amazing
    Commented Apr 2, 2019 at 14:47
  • 1
    I can just repeat again what I have said already: you have order line and order head information joined. And your shipping_information and payment_methods (what ever that fields should be, they are not standard Magento) are order head information because you take them from sales_flat_order table. I can't tell you what's wrong with your custom data I just can say you can't get more data than you have in the database with SQL :-)
    – HelgeB
    Commented Apr 2, 2019 at 15:17

0

Reset to default

Your Answer

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

Browse other questions tagged or ask your own question.