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!