cypher union group by sum

Question

I would like to boost edges for a user depending of some rules base on graph traversal. Basically in mysql I would do that :

select id, sum(weight) as total
from
(
     select id, 10 as weight
     from user
     inner join userRel1 ON user.id = userRel1.userId
     where userRel1.attr1 in (1, 2)
  union
     select id, 5 as weight
     from user
     inner join userRel2 ON user.id = userRel2.userId
     inner join userRel3 ON user.id = userRel3.userId
     where userRel2.attr2 = 'a' and userRel3.attr2 = 'z'
  union
     ...
)
group by id 
order by total desc

Also, I have already writed this query with some help in gremlin 3 but I would like to compare performance with cypher. But I read in this post that group by on union are not possible yet, it mean that cypher is less powerful than gremlin ? Would I have to set weight as properties on edges to achieve it ?

Thanks

Answer 1

While it is true that post-UNION processing is still an open feature request, you do not need to use UNION to perform your use case.

This query should do the equivalent of your SQL (ignoring the incomplete part):

WITH [] AS res

OPTIONAL MATCH (user1:User), (userRel1:UserRel1) 
WHERE user1.id = userRel1.userId AND userRel1.attr1 IN [1, 2] 
WITH res, (CASE WHEN userRel1 IS NOT NULL THEN COLLECT({id: user1.id, weight: 10}) ELSE [] END) AS data 
WITH res + data AS res

OPTIONAL MATCH (user2:User), (userRel2:UserRel2)
WHERE user2.id = userRel2.userId AND userRel2.attr2 = 'a'
OPTIONAL MATCH (userRel3:UserRel3)
WHERE user2.id = userRel3.userId AND userRel3.attr2 = 'z' 
WITH res, (CASE WHEN userRel3 IS NOT NULL THEN COLLECT({id: user2.id, weight: 5}) ELSE [] END) AS data 
WITH res + data AS res 

UNWIND res AS result
RETURN result.id, SUM(result.weight) AS weight;

I visually broke up this query into separate blocks of Cypher, to make it easier to read.

The query keeps extending (and replacing) the res collection with appropriate id/weight pairs, and then aggregates at the end.

The block with 2 OPTIONAL MATCH clauses could have been written using a single OPTIONAL MATCH, but I thought it was more performant do the same work piecemeal, and to allow the failure of one OPTIONAL MATCH to potentially inform Cypher to not even bother with the other one. The block's second WHERE clause relies on the user2 node that is found by the first OPTIONAL MATCH. user2 would have the value NULL if the first OPTIONAL MATCH failed, and such a NULL value would also cause the second WHERE clause to fail (which would in turn make userRel3 NULL).