Aggregated SUM from "Referencing entity" relationship, how to include nodes without relationship 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:



Query manual execution result:



Does anyone have possible explanation of this interesting behavior?
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.