Add additional values to a view result

Question

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

Answer 1

You can handle this with a custom view installed with a custom site module under /sites/default/modules

After reviewing the Ubercart code, it seems that the views have a hard coded exclusion in them to remove items with NULL value orders. The views use the Drupal filter views_handler_filter_numeric to remove items with less than 1 order.

From uc_views.views.inc:

$data['uc_order_products']['order_product_id'] = array(
'title' => t('Order product ID'),
'help' => $order_products['fields']['order_product_id']['description'],
'field' => array(
  'handler' => 'views_handler_field',
  'click sortable' => TRUE,
),
'sort' => array(
  'handler' => 'views_handler_sort',
),
'filter' => array(
  'handler' => 'views_handler_filter_numeric',
),
'argument' => array(
  'handler' => 'views_handler_argument_numeric',
  'name field' => 'title',
  'numeric' => TRUE,
  'validate type' => 'order_product_id',
),
);

From the Drupal docs:

Simple filter to handle greater than/less than filters

https://api.drupal.org/api/views/handlers!views_handler_filter_numeric.inc/class/views_handler_filter_numeric/7

My approach would be to copy the existing view into a custom module, and alter this behavior programatically. Addtionally exporting your views to a custom module as .inc files is recommended, as it speeds up loading of views by PHP. It's a win/win.

To get you started, here's a tutorial on how to export your existing view to a .inc file:

http://websmiths.co/blog/drupal-views-tutorials-exporting-views-code

Once you've done that, just edit the code to make your changes.

Answer 2

It seems that while an aggregation process INNER JOIN is used so it misses if a order does not exist. The query should user OUTER JOIN left or right to not miss products if an order does not exist. I looked over settings I did not find. I guess it should use OUTER JOIN by default. I ask you to show us what query the views generates. Show us the aggregation settings.

Answer 3

IF what you want is only to show results you can solve it rewriting the view's template at some level.

My approach will be to read the IDs of the ordered products (from template), then launch at the end of the report a sub-view with the IDs of the ordered products excluded as argument.

Match elements theming and you are done.

Not elegant but effective.