SQL to get order items straight from the database?

Question

How do we get a list of order items and their data (name, sku, quantity, price, weight, and dimensions)? Without using Magento's own framework components.

The problem is if the order contains 5 products I would get maybe 7 items from querying the table. This is caused by the different product types (simple, group, voucher, configurable, etc.) and different product ids so cannot be combined.

$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.

Answer 1

If you have the configuration setting for Catalog->Frontend->Use Flat Catalog Product set to ON, you can join the sales_flat_order_item table to catalog_product_flat_[#]. If you only have one (catalog_product_flat_1), that is fairly easy, but it can still be done with more if you use a Union

For one flat product table:

SELECT 
    oi.name, 
    oi.sku, 
    oi.qty_ordered, 
    oi.price, 
    oi.weight, 
    flat.width, 
    flat.height, 
    flat.length
FROM sales_flat_order_item oi
join catalog_product_flat_1 flat
on oi.product_id = flat.entity_id
WHERE oi.order_id = <order_id>;

For multiple flat product tables:

SELECT 
    oi.name, 
    oi.sku, 
    oi.qty_ordered, 
    oi.price, 
    oi.weight, 
    flat.width, 
    flat.height, 
    flat.length
FROM sales_flat_order_item oi
JOIN (
    select entity_id, width, height, length from catalog_product_flat_1
UNION DISTINCT
    select entity_id, width, height, length from catalog_product_flat_2
/*
REPEAT 2 LINES ABOVE FOR catalog_product_flat_3, catalog_product_flat_4, etc
*/
) flat
on oi.product_id = flat.entity_id
WHERE oi.order_id = <order_id>;

EDIT

This will accomplish the same goal regardless of whether you are using flat product tables:

SELECT 
    oi.name, 
    oi.sku, 
    oi.qty_ordered, 
    oi.price, 
    oi.weight,
    w.value 'width',
    h.value 'height',
    l.value 'length'
FROM sales_flat_order_item oi
JOIN catalog_product_entity_decimal w
ON oi.product_id = w.entity_id
JOIN catalog_product_entity_decimal h
ON oi.product_id = h.entity_id
JOIN catalog_product_entity_decimal l
ON oi.product_id = l.entity_id
WHERE oi.order_id = <order_id>
AND w.attribute_id = (
    select attribute_id from eav_attribute 
    where entity_type_id = 4 
    and attribute_code = 'width'
)
AND h.attribute_id = (
    select attribute_id from eav_attribute 
    where entity_type_id = 4 
    and attribute_code = 'height'
)
AND l.attribute_id = (
    select attribute_id from eav_attribute 
    where entity_type_id = 4 
    and attribute_code = 'length'
);

Answer 2

how about with this query, I try joined your query with catalog_product_entity and catalog_product_index_price tables, so you will get all products with contain price :

SELECT DISTINCT oi.name, oi.sku, oi.qty_ordered, oi.weight,  
`idx_price`.`price`
FROM sales_flat_order_item AS oi 
INNER JOIN `catalog_product_entity` AS `cpe` ON oi.sku = cpe.sku   
INNER JOIN `catalog_product_index_price` AS `idx_price` ON idx_price.entity_id = cpe.entity_id 
 WHERE oi.order_id = <order_id>

then you can add the product_type filtering on the end query like this :

AND oi.product_type = 'configurable'

or

AND oi.product_type = 'simple'

hope this can help

Answer 3

 SELECT  
        CONCAT(address.firstname,' ',
        address.lastname) AS Name,
        address.email AS Email,
        items.created_at AS Date,
        items.name AS Description,
        items.store_id AS Logon,
        items.name AS Category,
        items.store_id AS FeedbackDate,              
        items.sku AS ProductSearchcode,
        items.order_id AS Orderref  
 FROM sales_flat_order AS orders
  JOIN sales_flat_order_item AS items 
  ON items.order_id = orders.entity_id 
  LEFT JOIN sales_flat_order_address AS address
  ON orders.entity_id = address.parent_id
WHERE    
  items .created_at BETWEEN  '2014-04-15 00:00:00' AND '2014-07-30 
  00:00:00' AND orders.status = 'complete'

Also you can add AND orders.status in ('processing', 'complete') depending upon your requirement.

Answer 4

its simple, use group by will give you your results:

    "SELECT oi.name, oi.sku, oi.qty_ordered, oi.price, oi.weight
  FROM sales_flat_order_item oi
  WHERE oi.order_id = ". (int)$order_entity_id . " group by sku ;"

++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++