Tags/topics: 
Order
Views
1
Answers
Vote up!
0
Vote down!

In a product view relate order lines to the product

Hello,

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!

EDIT:
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!
0
Vote down!

I ended up doing this in a custom module:

<?php
 
// 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
    $query->orderBy('ttd.tid');
    $query->orderBy('fdfd.field_displaygroup_value');
    $query->orderBy('prod.nid');
    // build the query
    $result = $query->execute();
    // Initialize variables
?>

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

<?php
$totalquery = 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