1

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

enter image description here

  • Have you tried do the view the other way round, basing it on products and not on order? How are the entities related as you are using Drupal 6? – J. Reynolds Aug 23 '14 at 20:09
  • @J.Reynolds This is drupal 6 and ubercart 2. Yes I tried but it is not possible to connect a node (product) to order and then to invoice using relationships. – loparr Aug 23 '14 at 20:15
  • Have a look at this post. I am not versed in Drupal 6 but I can see the problem being solved if you can start with a view based on product then work your way towards the order. Good luck! – J. Reynolds Aug 23 '14 at 20:31
  • @J.Reynolds Thank you for help. I tried it (to use view of type order product) but it is basically the same as view I use now (of type ubercart order). View type order product means that I get to work with products that were ordered + if I filter it based on order number or invoice number, I only get ordered products again. However, in results I want to see also products that were not ordered. – loparr Aug 23 '14 at 20:56
0

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.

  • Hi, Interesting idea. I can follow some tutorials to create a module from exported view but what code should I edit to remove above mentioned filter? Thank you. – loparr Sep 1 '14 at 1:29
  • Hi, are you sure about this solution? I think it wont be possible because the table uc_order_products contains ONLY ordered products. Thus minimum ordered amount is 1 with or without views_handler_filter_numeric. – loparr Sep 2 '14 at 0:02
0

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.

  • Hi, I updated my question with view export and generated query. – loparr Sep 1 '14 at 1:12
  • I see the problem in the query. It starts build a query from the uc_orders. There should be a right join Some orders do not exist. The query should start from uc_order_products. It is description of the problem query. Try use another aggregation field. You also able to alter the view query and update the Join type. Try another views settings, send a sql query. – viktor.likin Sep 1 '14 at 7:30
  • Try to run the SQL code:gist.github.com/vlikin/9e7a44ffb0139c760d53 – viktor.likin Sep 1 '14 at 7:44
  • Hi, this code does not output all products. Only ordered ones again. – loparr Sep 1 '14 at 10:06
  • I think that altering existing view query will not work because uc_order_products contains only ordered products. – loparr Sep 1 '14 at 10:13
0

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.

  • can you please be more specific? I do not exactly follow your idea. Thank you. – loparr Sep 8 '14 at 16:10
  • Read the IDs of the ordered products, from your view or from your order, then stored these values in $ordered_ones, separated by a comma. Then create a view of products (name it 'unordered_ones') with a contextual filter of product ID. In the contextual filter options check multiple and also check to exclude the argument. Save. Finally run view 'unordered_ones' in the footer of your first view like this: views_embed_view ('unorderes_ones','default',$ordered_ones); It should work. If you have more answers just ask. – Beto Aveiga Sep 9 '14 at 3:58

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.