2

I wanted to get all orders (B2B) that are stored in MAGENTO database (using SQL) and obtain all the information about them (Items, price, transport information, current status, user information and so on)

I was trying something like this but this is not what i really want: 

SELECT ord_items.name, ord_items.sku, ord_items.qty_ordered, ord_items.weight, ord_items.price
FROM sales_flat_order_item as ord_items
WHERE ord_items.product_type = 'configurable' OR ord_items.product_type = 'bundle';

Can help me with this?

This is the MAGENTO database diagram:

image

Improve this question

1 Answer 1

Reset to default
2

Information about the order you will get from the table sales_flat_order while information at order line level you find in sales_flat_order_item. To get informations at order level and at order line item you have to join both tables.

select o.*, oi.* from sales_flat_order o
join sales_flat_order_item oi on o.entity_id = oi.order_id where oi.parent_item_id is null and ....

With this statement you will get all information from both tables please adapt it however you need it. The condition parent_item_id is null is to avoid double listing configurale products, which are stored twice (one line for parent and one for the child). Please note: you will get for each order as many rows as there are order lines.

If you need one row per order you have to add group by oi.order_id to your statement and sum before the numeric order_item columns you request in the statement.

Update example statement with carrier information:

Assuming you want to get the real order id (i.e. increment id), the status, ordered quantity and the total amount (incl. tax and shipping) for an order AND the skus and prices incl. tax for each order line AND a carrier_code (if the order has been shipped and the tracking information has been updated), you may use the following statement:

select o.increment_id, o.status, o.base_grand_total, 
       o.total_qty_ordered, oi.sku, oi.price_incl_tax, t.carrier_code 
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 -- left join here if you want also orders which are not yet shipped
where oi.parent_item_id is null

Please check the database table for other appropriate columns you need according to your requirement.

Improve this answer
17
  • You rock!.Dude, for example: how would you change your code if you wanted to obtain the transport information (the carrier)?
    – programming_amazing
    Commented Feb 17, 2019 at 15:36
  • Also, i dont fully understand how should i modify your code if i want to obtain the final price for each order BUT i want to see the prices of the different items for an specific order (Also, i want to see the order number). Do you get me?
    – programming_amazing
    Commented Feb 17, 2019 at 15:42
  • 1
    If you need information for each item separately you will either have a row per order line in your statement and the order information (for example the order increment id or the order status) will appear in each of that order lines or you will have to run more than one statement - this depends on your requierement. I'll update my answer with an example which also contain the carrier information which is stored in another table sales_flat_shipment_track
    – HelgeB
    Commented Feb 17, 2019 at 16:41
  • Dude, what would you do if you wanted to obtain the date of each order? PS: i tried to do the group by oi.order_id but i got an error :(
    – programming_amazing
    Commented Feb 17, 2019 at 17:49
  • Also, i am getting NULL results in th field "carrier_code"
    – programming_amazing
    Commented Feb 17, 2019 at 17:50

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.