5

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.

  • Have you tried using "JOIN" query? – Chintan Kaneriya Sep 25 '18 at 3:55
  • Have you got the solution ? If not the i have it. – Aaditya Sep 27 '18 at 5:55
  • No not yet. I think a little PHP logic could be needed to distinguish simple products from configurable+simple products. It's like configurable snd group are parents of the sinple items. But price weight etc is a bit spread over them. One holds this, the other holds that. – tim Sep 28 '18 at 23:17
0

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

  • Price is already present in sales_flat_order_item.price so I would be asking for trouble if I grab that from the products table. The only thing I can't find in sales_flat_order_item are dimensions. – tim Sep 25 '18 at 14:03
  • yes you will get trouble if your product included in catalog promotions or customer using voucher discount, it will cause actual price in the sales_flat_order_item will be different with catalog_product_index_price – mrfizh Sep 26 '18 at 3:42
0

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'
);
  • Thank for your efforts trying. How will this distinguish what is a simple product or simple+configurable? Where it occurs multiple times in the table. – tim Sep 28 '18 at 23:12
  • @tim these should all be specific to the actual item used, but yes, the configurable and simple product will both be listed. If you want the child-product's sku and dimensions as configured simply add AND oi.product_type <> 'configurable' to any of these. If you prefer to see the parent-product, add AND oi.parent_item_id is null. If this solves it for you, let me know which one and I will update my answer. – mtr.web Oct 1 '18 at 19:07
0
 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.

  • I think you confused Item Name with customer name. This doesn't sort out the problem where a simple product also is present in a group or configurable product, so there will be duplicate rows for the same item. I just need to acess sales_flar_order_item and extract the items without dupes. – tim Sep 28 '18 at 23:06
0

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

++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++ enter image description here

  • And how will they be grouped when the price is in configurable but the quantity in simple. See the product_type column. – tim Sep 28 '18 at 23:08
  • price and qty always be in configurable. if 2qty selected for any of child product. 2 will also the configurable quantity., and present in above query. look at above image attached. Let me know if you still have any questions – Hassan Ali Shahzad Sep 29 '18 at 21:31
  • I case of simple product added as stand alone product this will work properly as well. in that case price & qty will be for that simple product. – Hassan Ali Shahzad Sep 30 '18 at 17:05

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.