I'm starting with line 'commerce_line_item' as a base table, and I want to get prices for each of the line item products attached to a Drupal Commerce Order, rather than just the order total. I need a listing of each product sold with prices, and neither views nor Entity Field Query offer enough options for me (I'm creating a master order statistics spreadsheet with phpexcel).

The only promising field I have in the 'commerce_line_item' table is an 'order_id', which I can't find a way to relate back to 'commerce_product' and a 'line_item_label', which does not join on the field 'commerce_product.sku'.

So what would I join on to get pricing information?

I asked a similar question here, but this has me slightly more baffled.

Here's my code below for the query.

  $query = db_select('commerce_line_item', 'i');
  $query->fields('i', array('line_item_label', 'line_item_id', 'quantity', 'data'));
  $query->condition('i.type', 'product', '=');
  $query->leftjoin('commerce_product', 'cp', 'cp.sku = i.line_item_label');
  $query->leftjoin('field_data_commerce_price', 'fcp', 'fcp.entity_id = cp.product_id');
  $query->fields('fcp', array('commerce_price_amount'));
  $query->leftjoin('commerce_order', 'o', 'o.order_id = i.order_id');


Thanks for your help!

2 Answers 2


As you can see in the graph from the question you're linking, there's a field that relates the line item with the products, that's not a database field but a Drupal field. This means that there will be a database table for the field data and revisions and you don't want to deal with that in a db_query, you should be using Entity Field Query instead that will know the database tables and fields to use in each case.

I'm guessing you want to get products from the line items you already have, or the order.

I'd get the line items with EntityFieldQuery:

$query = new EntityFieldQuery();
$result = $query
  ->entityCondition('entity_type', 'commerce_line_item')
  ->propertyCondition('order_id', <-your-order-id->)

Now you've got the line items there, wrap them and get the product price:

if (!empty($result['commerce_line_item'])) {
  foreach ($result['commerce_line_item'] as $id => $value) {
    $line_item_wrapper = entity_metadata_wrapper('commerce_line_item', $id);
    $product_wrapper = $line_item_wrapper->commerce_product;
    $price = commerce_price_wrapper_value($product_wrapper, 'commerce_price', TRUE);

Also, all this data is exposed to views, so you can create a view instead.

  • Friend, thank you for answering the question. I am with you on wanting to use Entity Field Query or views. Can't use views because, I have to hand query results to phpexcel to build a complex spreadsheet. As for EntityFieldQuery, I looked at it yesterday and didn't think it was powerful enough. Maybe I'm wrong, I will look again and look at your code here. The thing is, I'd like to join arbitrary tables as well as those known to entity. Can EntityFieldQuery do that? I didn't think it could when I looked. Jul 11, 2014 at 16:47
  • Alright, I just thought of what should be obvious. If I use EntityFieldQuery, I can just do a db_select query later with the results. No need for a monster query. I am persuaded that after 8 joins a query should be drug out back and shot. I'll convert the query next week when I'm back on the project. Jul 11, 2014 at 16:55

This was totally the wrong way to go about this as pcambra points out.

Instead, I built a view based on the object Commerce Line Item with relationships to Commerce Order and through Commerce Order to Address Profile and then used views_get_view_result() to get an array of the results which I then handed to the function phpexcel_export() with a little bit of processing.

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.