Commerce: What field to join line_item in order to get product price in custom query?

Question

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

snip->

Thanks for your help!

Answer 1

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->)
  ->execute();

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.

Answer 2

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.