Commerce get product id's from order

Question

My question is simple: How do I get the product id's from a commerce order with Drupal code? I have something like this at the moment:

  $orders = commerce_order_load_multiple(array(), array('status' => 'pending'), TRUE);
  foreach($orders as $order) {
    foreach ($order->commerce_line_items['und'] as $line) {
        $line_id = $line['line_item_id'];
        // ... product id, where are you?
    }

Hopefully somebody is able to answer this question :)

Answer 1

I can't remember the exact structure of the commerce product reference field, but you need to do something like this.

Warning this code style won't work on many orders as the internal cache for the line item entities will use too much memory. This will be a problem if you have thousands of orders.

$orders = commerce_order_load_multiple(array(), array('status' => 'pending'), TRUE);
foreach($orders as $order) {
  foreach ($order->commerce_line_items['und'] as $line) {
    $line_item = commerce_line_item_load($line['line_item_id']);
    $product_id = $line_item->commerce_product['und']...
  }
}

Answer 2

Using the entity metadata wrapper, you could also do:

foreach (commerce_order_load_multiple(array(), array('status' => 'pending'), TRUE) as $order) {
  $product_ids = array();
  foreach (entity_metadata_wrapper('commerce_order', $order)->commerce_line_items as $delta => $line_item_wrapper) {
    if (in_array($line_item_wrapper->type->value(), commerce_product_line_item_types())) {
      $product_ids[] = $line_item_wrapper->commerce_product->raw();
    }
  }
}

The important part here is checking the type of the line item, so you don't end up including shipping line items or other types of line items in your list of product IDs. Additionally, with the wrapper notice that I used the "raw" value of the commerce_product field on the line item. This is because the "value" would be the fully loaded referenced product, while the "raw" value is simply the product ID.

Answer 3

If you don't need to load whole objects (line items, order and commerce products) You could run query like this:

 $args = array(
    ':order_id' => $order_id,

);
$product_ids = db_query("SELECT p.commerce_product_product_id 
   FROM {commerce_line_item} li 
   JOIN {field_data_commerce_product} p ON (p.entity_id = li.line_item_id) 
   WHERE li.order_id = :order_id AND li.type = 'product'", $args)->fetchCol();
return $product_ids;