I try to accomplish following task:
I have a view with all products that were ordered during certain period of time (based on invoice creation time). (view is of type of order) I am using sql agregation and table display.
I have 3 products: apple, orange, banana
Problem: If orange was not ordered, it is not shown in results like this (and I understand that is because I work with ubercart order which contains only ordered products)
apple 3 banana 2 What I need, is to show 0 value for products that were not ordered as well:
apple 3 banana 2 orange 0 How is this possible to solve? Maybe add values to view result? Thank you for help.
EDIT: it is not possible to create the view using relationships other way around (create view type of node and use relationship to order) as suggested by @ J. Reynolds, because I use invoice date for filtering ordered products and it is not possible to get to invoice data (probably because invoice data are in different table)
I also tried combining 2 results together using solution here https://www.drupal.org/node/1013774#comment-4547362 but it seems that it is working only if both views are same of same type. (I just simply can not create a view of type ubercart order product or ubercart order and display products that were not ordered.)
EDIT: After reviewing 2 answers (by @routh and @viktor.likin) I think both are wrong. The problem is that the table I use to get ordered products amounts (uc_order_products) simply does NOT contain products that were not ordered. It contains ONLY orders that were ordered. Logically I am not able to show all products in my table.
Generated queryfollowed by exported view
SELECT uc_orders.order_id AS order_id, uc_order_products.model AS uc_order_products_model, uc_orders.uid AS uc_orders_uid, uc_order_products.title AS uc_order_products_title, SUM(uc_order_products.qty) AS uc_order_products_qty FROM uc_orders uc_orders LEFT JOIN uc_invoice uc_invoice ON uc_orders.order_id = uc_invoice.order_id LEFT JOIN uc_order_products uc_order_products ON uc_orders.order_id = uc_order_products.order_id WHERE (uc_invoice.created >= 1396303200) AND (uc_invoice.created <= 1398895199) AND (uc_orders.order_status not in ('canceled', 'canceled1')) GROUP BY uc_order_products_title ORDER BY uc_order_products_model ASC Exported view:
$view = new view; $view->name = 'testsumuuu'; $view->description = ''; $view->tag = ''; $view->base_table = 'uc_orders'; $view->core = 6; $view->api_version = '2'; $view->disabled = FALSE; /* Edit this to true to make a default view disabled initially */ $handler = $view->new_display('default', 'Defaults', 'default'); $handler->override_option('fields', array( 'model' => array( 'label' => 'SKU', 'alter' => array( 'alter_text' => 0, 'text' => '', 'make_link' => 0, 'path' => '', 'absolute' => 0, 'link_class' => '', 'alt' => '', 'rel' => '', 'prefix' => '', 'suffix' => '', 'target' => '', 'help' => '', 'trim' => 0, 'max_length' => '', 'word_boundary' => 1, 'ellipsis' => 1, 'html' => 0, 'strip_tags' => 0, ), 'empty' => '', 'hide_empty' => 0, 'empty_zero' => 0, 'hide_alter_empty' => 1, 'exclude' => 0, 'id' => 'model', 'table' => 'uc_order_products', 'field' => 'model', 'relationship' => 'none', ), 'order_id' => array( 'label' => 'Order ID', 'alter' => array( 'alter_text' => 0, 'text' => '', 'make_link' => 0, 'path' => '', 'absolute' => 0, 'link_class' => '', 'alt' => '', 'rel' => '', 'prefix' => '', 'suffix' => '', 'target' => '', 'help' => '', 'trim' => 0, 'max_length' => '', 'word_boundary' => 1, 'ellipsis' => 1, 'html' => 0, 'strip_tags' => 0, ), 'empty' => '', 'hide_empty' => 0, 'empty_zero' => 0, 'hide_alter_empty' => 1, 'link_to_order' => 0, 'exclude' => 1, 'id' => 'order_id', 'table' => 'uc_orders', 'field' => 'order_id', 'relationship' => 'none', ), 'title' => array( 'label' => 'Title', 'alter' => array( 'alter_text' => 0, 'text' => '', 'make_link' => 0, 'path' => '', 'absolute' => 0, 'link_class' => '', 'alt' => '', 'rel' => '', 'prefix' => '', 'suffix' => '', 'target' => '', 'help' => '', 'trim' => 0, 'max_length' => '', 'word_boundary' => 1, 'ellipsis' => 1, 'html' => 0, 'strip_tags' => 0, ), 'empty' => '', 'hide_empty' => 0, 'empty_zero' => 0, 'hide_alter_empty' => 1, 'exclude' => 0, 'id' => 'title', 'table' => 'uc_order_products', 'field' => 'title', 'relationship' => 'none', ), 'qty' => array( 'label' => '', 'alter' => array( 'alter_text' => 0, 'text' => '', 'make_link' => 0, 'path' => '', 'absolute' => 0, 'link_class' => '', 'alt' => '', 'rel' => '', 'prefix' => '', 'suffix' => '', 'target' => '', 'help' => '', 'trim' => 0, 'max_length' => '', 'word_boundary' => 1, 'ellipsis' => 1, 'html' => 0, 'strip_tags' => 0, ), 'empty' => '', 'hide_empty' => 0, 'empty_zero' => 0, 'hide_alter_empty' => 1, 'exclude' => 0, 'id' => 'qty', 'table' => 'uc_order_products', 'field' => 'qty', 'relationship' => 'none', ), 'views_sql_groupedfields' => array( 'label' => 'Group By Fields', 'alter' => array( 'alter_text' => FALSE, 'text' => '', 'make_link' => FALSE, 'path' => '', 'absolute' => '', 'alt' => '', 'rel' => '', 'link_class' => '', 'prefix' => '', 'suffix' => '', 'target' => '', 'trim' => FALSE, 'max_length' => '', 'word_boundary' => TRUE, 'ellipsis' => TRUE, 'strip_tags' => FALSE, 'html' => FALSE, ), 'empty' => '', 'hide_empty' => 0, 'empty_zero' => 0, 'hide_alter_empty' => 1, 'exclude' => '1', 'id' => 'views_sql_groupedfields', 'table' => 'views_groupby', 'field' => 'views_sql_groupedfields', 'relationship' => 'none', 'views_groupby_fields_to_group' => array( 'title' => 'title', ), 'views_groupby_sql_function' => 'sum', 'views_groupby_fields_to_aggregate' => array( 'qty' => 'qty', ), 'views_groupby_field_sortby' => 'model', 'views_groupby_sortby_direction' => 'asc', ), )); $handler->override_option('filters', array( 'created' => array( 'operator' => 'between', 'value' => array( 'type' => 'date', 'value' => '', 'min' => '2014-04-01 00:00:00', 'max' => '2014-04-30 23:59:59', ), 'group' => '0', 'exposed' => TRUE, 'expose' => array( 'use_operator' => 0, 'operator' => 'created_op', 'identifier' => 'created', 'label' => 'Ubercart invoice number: Invoice date', 'optional' => 1, 'remember' => 0, ), 'id' => 'created', 'table' => 'uc_invoice', 'field' => 'created', 'relationship' => 'none', 'override' => array( 'button' => 'Override', ), ), 'order_status' => array( 'operator' => 'not in', 'value' => array( 'canceled' => 'canceled', 'canceled1' => 'canceled1', ), 'group' => '0', 'exposed' => FALSE, 'expose' => array( 'operator' => FALSE, 'label' => '', ), 'id' => 'order_status', 'table' => 'uc_orders', 'field' => 'order_status', 'relationship' => 'none', ), )); $handler->override_option('access', array( 'type' => 'none', )); $handler->override_option('cache', array( 'type' => 'none', )); $handler->override_option('use_ajax', TRUE); $handler->override_option('items_per_page', 0); $handler->override_option('style_plugin', 'sheet'); $handler->override_option('style_options', array( 'grouping' => '', 'expansion' => '0', 'template' => 0, 'sheetsave' => 'socialcalc:version:1.0 MIME-Version: 1.0 Content-Type: multipart/mixed; boundary=SocialCalcSpreadsheetControlSave --SocialCalcSpreadsheetControlSave Content-type: text/plain; charset=UTF-8 # SocialCalc Spreadsheet Control Save version:1.0 part:sheet part:edit part:audit --SocialCalcSpreadsheetControlSave Content-type: text/plain; charset=UTF-8 version:1.5 sheet:c:1:r:1 --SocialCalcSpreadsheetControlSave Content-type: text/plain; charset=UTF-8 version:1.0 rowpane:0:1:36 colpane:0:1:8 ecell:A1 sort::-1:up:::: --SocialCalcSpreadsheetControlSave Content-type: text/plain; charset=UTF-8 --SocialCalcSpreadsheetControlSave-- ', )); $handler->override_option('exposed_block', TRUE); $handler = $view->new_display('block', 'Block', 'block_1'); $handler->override_option('block_description', ''); $handler->override_option('block_caching', -1); 
