Neo4j Cypher pattern comprehension with optional match

Question

I have the following pattern comprehension:

[ (parentD)<-[:DEFINED_BY]-(c1)<-[vg1:HAS_VOTE_ON]-(childD) 
| {criterionId: toInt(c1.id),  weight: vg1.avgVotesWeight, totalVotes: toInt(vg1.totalVotes)} ] AS weightedCriteria

Right now I need to add additional optional matching to this query.. something like this one:

[ (parentD)<-[:DEFINED_BY]-(c1)<-[vg1:HAS_VOTE_ON]-(childD) 
   OPTIONAL MATCH (childD)<-[:VOTED_FOR]-(v1:Vote)-[:VOTED_ON]->(c1) 
| {criterionId: toInt(c1.id),  weight: vg1.avgVotesWeight, totalVotes: toInt(vg1.totalVotes)} ] AS weightedCriteria

but it doesn't work with a Cypher error - org.neo4j.driver.v1.exceptions.ClientException: Invalid input 'P': expected 'r/R'

Please show how to correctly add this optional match.

UPDATED

I need something like this one:

[ (parentD)<-[:DEFINED_BY]-(c1)<-[vg1:HAS_VOTE_ON]-(childD)<-[:VOTED_FOR*0..1]-(v1:Vote)-[:VOTED_ON]->(c1) 
| {criterionId: toInt(c1.id),  weight: vg1.avgVotesWeight, totalVotes: toInt(vg1.totalVotes), userVotes: v1} ] AS weightedCriteria

in other words, I need to make the list of v1 as the sublist of weightedCriteria.userVotes(SDN @QueryResult) but right now my test fails on this new query with the assertion - it expects 3 records but returns 13...

This is a Neo4j sandbox:

https://10-0-1-12-35256.neo4jsandbox.com/browser/

Username: neo4j Password: probe-jumps-lick

this my old query:

MATCH (parentD)-[:CONTAINS]->(childD:Decision) 
WHERE parentD.id = 1 
WITH childD , parentD  
ORDER BY childD.createDate DESC 
SKIP 0 LIMIT 100 
WITH * 
MATCH (childD)-[ru:CREATED_BY]->(u:User) 
OPTIONAL MATCH (childD)-[rup:UPDATED_BY]->(up:User)  
RETURN ru, u, rup, up, childD AS decision, 
[ (parentD)<-[:DEFINED_BY]-(entity)<-[:COMMENTED_ON]-(comg:CommentGroup)-[:COMMENTED_FOR]->(childD) 
    | {entityId: toInt(entity.id),  types: labels(entity), totalComments: toInt(comg.totalComments)} ] AS commentGroups, 
[ (parentD)<-[:DEFINED_BY]-(c1)<-[vg1:HAS_VOTE_ON]-(childD) 
    | {criterionId: toInt(c1.id),  weight: vg1.avgVotesWeight, totalVotes: toInt(vg1.totalVotes)} ] AS weightedCriteria, 
[ (parentD)<-[:DEFINED_BY]-(ch1:Characteristic)<-[v1:HAS_VALUE_ON]-(childD)  WHERE  NOT ((ch1)<-[:DEPENDS_ON]-())  
    | {characteristicId: toInt(ch1.id),  value: v1.value, available: v1.available, totalHistoryValues: v1.totalHistoryValues, totalFlags: v1.totalFlags, description: v1.description, valueType: ch1.valueType, visualMode: ch1.visualMode} ] AS valuedCharacteristics

this is a new Cypher query:

MATCH (parentD)-[:CONTAINS]->(childD:Decision) 
WHERE parentD.id = 1 
WITH childD , parentD  
ORDER BY childD.createDate DESC 
SKIP 0 LIMIT 100 
WITH * 
MATCH (childD)-[ru:CREATED_BY]->(u:User) 
OPTIONAL MATCH (childD)-[rup:UPDATED_BY]->(up:User)  
RETURN ru, u, rup, up, childD AS decision, 
[ (parentD)<-[:DEFINED_BY]-(entity)<-[:COMMENTED_ON]-(comg:CommentGroup)-[:COMMENTED_FOR]->(childD) 
    | {entityId: toInt(entity.id),  types: labels(entity), totalComments: toInt(comg.totalComments)} ] AS commentGroups, 
[ (parentD)<-[:DEFINED_BY]-(c1)<-[vg1:HAS_VOTE_ON]-(childD)<-[:VOTED_FOR*0..1]-(v1:Vote)-[:VOTED_ON]->(c1) 
    | {criterionId: toInt(c1.id),  weight: vg1.avgVotesWeight, totalVotes: toInt(vg1.totalVotes), userVotes: v1} ] AS weightedCriteria, 
[ (parentD)<-[:DEFINED_BY]-(ch1:Characteristic)<-[v1:HAS_VALUE_ON]-(childD)  WHERE  NOT ((ch1)<-[:DEPENDS_ON]-())  
    | {characteristicId: toInt(ch1.id),  value: v1.value, available: v1.available, totalHistoryValues: v1.totalHistoryValues, totalFlags: v1.totalFlags, description: v1.description, valueType: ch1.valueType, visualMode: ch1.visualMode} ] AS valuedCharacteristics

I believe this query is working fine and produces JOIN between Criteria and Votes.. this is why I see 13 records instead of 3 from my previous query...

It will be really great if you can show me a solution how to return the 3 rows (with Criterion info on the root level and nested info(sublist) of Votes) instead of 13 different records...

I need the solution that will produce 3 original records(as the first query) with a nested info and not JOINS... I need this because I do the custom projection of query result into my object model and need to have a sublist of Votes as a property of weightedCriteria.

Also inside of this pattern comprehension I have to filter Votes by User - something like this: (v1)-[ru:CREATED_BY]->(u:User) WHERE u.id = {userId}

is it possible to implement?

Bruno Peres Bruno Peres · Accepted Answer · 2017-10-13T11:17:19

Instead of OPTIONAL MATCH you can do it using a variable-length pattern matching in the :VOTED_FOR relationship. A variable-length from zero to one will be equivalent to an OPTIONAL MATCH:

[ (parentD)<-[:DEFINED_BY]-(c1)<-[vg1:HAS_VOTE_ON]-(childD)<-[:VOTED_FOR*0..1]-(v1:Vote)-[:VOTED_ON]->(c1) 
| {criterionId: toInt(c1.id),  weight: vg1.avgVotesWeight, totalVotes: toInt(vg1.totalVotes)} ] AS weightedCriteria

[:VOTED_FOR*0..1] makes this relation optional in the pattern.

Neo4j Cypher pattern comprehension with optional match

1 Answers