0

I have two content types - contact and payment. Payment have field_contact that is an reference to contact and also field_payment_amount which is decimal.

In views I'm showing list of contacts (with filter by type = contact) but also would like to show SUM of payments for given contact. I added relationship: Entity Reference: Referencing entity to entities that are referencing my contact by field field_contact, turned on aggregation, and added to view field_payment_amount aggregated as SUM, and using my relationship. In this configuration all contacts are shown but field that should show my sum is empty.

Next I added another filter (by type), now using my relationship and for this relationship filtering by type = payment. In this configuration I can see my sums - great! But! If contact have no payments - it is excluded from table completely.

How can I show contacts without payments with "0" as sum of payments and calculate proper sums for contacts with payments? I feel like I'm missing something simple, or maybe not?

Added:

Ok, I reduced problem to most simple case, and analyzed query. Now interesting thing is that looks like the same query executed manually by me, and by view - gives different results O_o .

Query:

SELECT node.nid AS nid, MIN(field_contact_node.nid) AS field_contact_node_nid, 'node' AS field_data_field_payment_amount_node_entity_type, SUM(field_contact_node__field_data_field_payment_amount.field_payment_amount_value) AS field_contact_node__field_data_field_payment_amount_field_pa
FROM 
{node} node
LEFT JOIN {field_data_field_contact} field_data_field_contact ON node.nid = field_data_field_contact.field_contact_target_id AND (field_data_field_contact.entity_type = 'node' AND field_data_field_contact.deleted = '0')
LEFT JOIN {node} field_contact_node ON field_data_field_contact.entity_id = field_contact_node.nid
LEFT JOIN {field_data_field_payment_amount} field_contact_node__field_data_field_payment_amount ON field_contact_node.nid = field_contact_node__field_data_field_payment_amount.entity_id AND (field_contact_node__field_data_field_payment_amount.entity_type = 'node' AND field_contact_node__field_data_field_payment_amount.deleted = '0')
WHERE (( (node.status = '1') AND (node.type IN  ('contact')) ))
GROUP BY nid, field_data_field_payment_amount_node_entity_type
LIMIT 12 OFFSET 0

Views output:

Views

Query manual execution result:

enter image description here

Does anyone have possible explanation of this interesting behavior?

0

I just got this working by doing basically what you did:

Relationship: Content referencing Content from field_contact, do not tick 'Require this relationship'

Aggregation: Yes

Fields:

  1. Title: no relationship

  2. payment amount: set relationship to Content referencing Content from field_contact. Set no results behavior to '0'. Set aggregation to 'SUM', Group column to 'Value'

Works fine.

Exported View

$view = new view();
$view->name = 'test';
$view->description = '';
$view->tag = 'default';
$view->base_table = 'node';
$view->human_name = 'test';
$view->core = 7;
$view->api_version = '3.0';
$view->disabled = FALSE; /* Edit this to true to make a default view disabled initially */

/* Display: Master */
$handler = $view->new_display('default', 'Master', 'default');
$handler->display->display_options['title'] = 'test';
$handler->display->display_options['use_more_always'] = FALSE;
$handler->display->display_options['group_by'] = TRUE;
$handler->display->display_options['access']['type'] = 'perm';
$handler->display->display_options['cache']['type'] = 'none';
$handler->display->display_options['query']['type'] = 'views_query';
$handler->display->display_options['exposed_form']['type'] = 'basic';
$handler->display->display_options['pager']['type'] = 'some';
$handler->display->display_options['pager']['options']['items_per_page'] = '';
$handler->display->display_options['pager']['options']['offset'] = '0';
$handler->display->display_options['style_plugin'] = 'default';
$handler->display->display_options['row_plugin'] = 'fields';
/* Relationship: Entity Reference: Referencing entity */
$handler->display->display_options['relationships']['reverse_field_contact_node']['id'] = 'reverse_field_contact_node';
$handler->display->display_options['relationships']['reverse_field_contact_node']['table'] = 'node';
$handler->display->display_options['relationships']['reverse_field_contact_node']['field'] = 'reverse_field_contact_node';
/* Field: Content: Title */
$handler->display->display_options['fields']['title']['id'] = 'title';
$handler->display->display_options['fields']['title']['table'] = 'node';
$handler->display->display_options['fields']['title']['field'] = 'title';
/* Field: SUM(Content: payment_amount) */
$handler->display->display_options['fields']['field_payment_amount']['id'] = 'field_payment_amount';
$handler->display->display_options['fields']['field_payment_amount']['table'] = 'field_data_field_payment_amount';
$handler->display->display_options['fields']['field_payment_amount']['field'] = 'field_payment_amount';
$handler->display->display_options['fields']['field_payment_amount']['relationship'] = 'reverse_field_contact_node';
$handler->display->display_options['fields']['field_payment_amount']['group_type'] = 'sum';
$handler->display->display_options['fields']['field_payment_amount']['empty'] = '0';
$handler->display->display_options['fields']['field_payment_amount']['settings'] = array(
  'thousand_separator' => ' ',
  'decimal_separator' => '.',
  'scale' => '2',
  'prefix_suffix' => 1,
);
/* Sort criterion: Content: Post date */
$handler->display->display_options['sorts']['created']['id'] = 'created';
$handler->display->display_options['sorts']['created']['table'] = 'node';
$handler->display->display_options['sorts']['created']['field'] = 'created';
$handler->display->display_options['sorts']['created']['order'] = 'DESC';
/* Filter criterion: Content: Published */
$handler->display->display_options['filters']['status']['id'] = 'status';
$handler->display->display_options['filters']['status']['table'] = 'node';
$handler->display->display_options['filters']['status']['field'] = 'status';
$handler->display->display_options['filters']['status']['value'] = 1;
$handler->display->display_options['filters']['status']['group'] = 1;
$handler->display->display_options['filters']['status']['expose']['operator'] = FALSE;
/* Filter criterion: Content: Type */
$handler->display->display_options['filters']['type']['id'] = 'type';
$handler->display->display_options['filters']['type']['table'] = 'node';
$handler->display->display_options['filters']['type']['field'] = 'type';
$handler->display->display_options['filters']['type']['value'] = array(
  'contact' => 'contact',
);

/* Display: Page */
$handler = $view->new_display('page', 'Page', 'page');
$handler->display->display_options['path'] = 'test';
  • Can you please export your view and paste it here? – Łukasz Zaroda Feb 14 '15 at 19:57
  • Thanks, but I meant view and not query :) . – Łukasz Zaroda Feb 14 '15 at 20:31
  • I suggest you do a new test install of Drupal and recreate it there for yourself. – J. Reynolds Feb 14 '15 at 20:39
  • Just an afterthought, did you see step 2 re. Set no results behavior to '0' – J. Reynolds Feb 14 '15 at 20:56
  • Yes, unfortunately I was at the point of your view before, and the problem is, that it is not aggregating payments properly. I added payment, and still have all "0" everywhere. I'm going to try this out on other installment of Drupal. – Łukasz Zaroda Feb 14 '15 at 21:08
1

I was debugging like crazy to figure this out and then after checking some values in views_handler_field_field I realized what was wrong, and for sure it weren't Views! I forgot, or rather though of it as irrelevant, that there was third content type - a comment, that had a field "field_contact". The problem occurred because Views when summing up, was also using existing "comment" nodes, but because they didn't have "field_payment_amount" at all - Views got confused. So the real answer is that we need to filter out any additional types that are using "field_contact" field in our relationship.

I guess Views could act a bit better on this, and not get confused but use only available fields from proper nodes to create a sum, but it isn't much problem as long as we can filter out useless (for SUM) types that are using our reference field.

This explains why I got random/wrong results without type filtering on relationship, but when I added filter for "payment" - I didn't get "0" results, because then view assumed that relationship is required. The proper solution is then EXCLUDING wrong types that are in our relationship.

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.