SQL to get order items from Magento?

user_1135440 0
Viewed 3217

I need to get a list of ordered physical items to ship from the database. The list need to consist of name, sku, quantity, price, weight, and dimensions.

How can we fo this without using Magento's own framework components.

The problem is that if a product contains options it appears multiple times in the table by different product types (simple, bundle, configurable, etc.) and different product ids.

$items = array();

$sql = (
  "SELECT oi.name, oi.sku, oi.qty_ordered, oi.price, oi.weight
  FROM {$db->prefix}sales_flat_order_item oi
  WHERE oi.order_id = ". (int)$order_entity_id .";"
);

if ($result = $mysqli->query($sql)) {

  while ($row = $result->fetch_assoc()) {

    $items[] = [
      'name'                   => $row['name'],
      'sku'                    => $row['sku'],
      'quantity'               => (float)$row['qty_ordered'],
      'unit_price'             => (float)$row['price'],
      'unit_weight'            => (float)$row['weight'],
      //'unit_length'            => (float)$row['length'], // Not present?
      //'unit_width'             => (float)$row['width'], // Not present?
      //'unit_height'            => (float)$row['height'], // Not present?
    ];
  }

  $result->close();
}

If I pass a filter AND product_type = 'simple' to get only the simple products, they do not contain price.

magento2 magento-1.9 magento mysql php
2 Answers

If there is a configurable product which have multiple simple products with same price, then the price will be saved only in the configurable product. So you should not use the 'simple product' filter. Try with below query,

SELECT orders.increment_id AS increment_id,
       orders.status AS order_status,
       orders.customer_email AS customer_email,
       orders.coupon_code AS coupon_code,
       items.name AS item_name,
       items.item_id AS order_item_id,
       items.sku AS item_sku,
       items.base_price AS item_base_price,
       items.price_incl_tax AS item_price,
       items.qty_ordered AS qty_ordered,
       items.product_id AS product_id,
       items.weight AS Weight
    FROM sales_flat_order_item AS items
    LEFT JOIN sales_flat_order AS orders
    ON items.order_id = orders.entity_id 
  WHERE 
    orders.entity_id = 18229

This gives you the details of both Simple and Configurable product and you can filer the contents using conditions (if / else).

user_16190390

Try this:

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';

Note that for Magento the SKU is an identifier and in sales_flat tables this the attribute used to link a bundle or configurable products to its simple. The parent product (configurable or bundle) has all the info that you need in your query.

*With this query to are assuming that in this shop you only sell bundle or configurable products.

user_102537650
Related