Vote up!
Vote down!

In a product view relate order lines to the product


This is what i want to do:

Create a list of products with a date filter.
On each product I want my total amount ordered within that specifice date.

So for example:

Prod 1 - Ordered 1
Prod 2 - Ordered 0
Prod 3 - Ordered 5

Currently I made a view with order lines, linked my products and use Aggregation.
But in this case, products that havent been ordered arent being displayed.

How can i manage this?

Thanks in advance!

What I need is something like this: SELECT * FROM commerce_order_lines WHERE product_id = productid

Asked by: easycombvba
on May 15, 2013

1 Answer

Vote up!
Vote down!

I ended up doing this in a custom module:

// Select nodes
$query = db_select('node', 'prod');
$query->condition('prod.type', 'product_display');
$query->fields('prod', array('nid', 'title'));
// Join product info link table
$query->join('field_data_field_product', 'fdcp', 'prod.nid = fdcp.entity_id' );
$query->fields('fdcp', array('field_product_product_id'));
// Join product info
$query->join('commerce_product', 'cp', 'fdcp.field_product_product_id = cp.product_id');
$query->fields('cp', array('sku'));
// Join category link table
$query->join('field_data_field_product_category', 'fdfpc', 'prod.nid = fdfpc.entity_id');
$query->fields('fdfpc', array('field_product_category_tid'));
// Join category data
$query->join('taxonomy_term_data', 'ttd', 'fdfpc.field_product_category_tid = ttd.tid');
$query->fields('ttd', array('name'));
// Join displaygroup data
$query->join('field_data_field_displaygroup', 'fdfd', 'prod.nid = fdfd.entity_id');
$query->fields('fdfd', array('field_displaygroup_value'));
// Grouping by the TERM id
// build the query
$result = $query->execute();
// Initialize variables

Then run trought my data with foreach.
Inside the loop:

= db_select('commerce_line_item', 'cli');
$totalquery->condition('cli.line_item_label', $record->sku);
$totalquery->addExpression('SUM(quantity)', 'node_quantity');
$totalresult = $totalquery->execute();

There will be a better solution with views I guess? But I could not figure it out!
Answer by: easycombvba
Posted: May 17, 2013