Neo4j Cypher query and complex sorting

Question

I have a following Cypher query:

MATCH (t:Tenant) WHERE ID(t) in {tenantIds} 
 OR t.isPublic 
WITH COLLECT(t) as tenants 
MATCH (parentD)-[:CONTAINS]->(childD:Decision)-[ru:CREATED_BY]->(u:User) 
WHERE id(parentD) = {decisionId} 
 AND (not (parentD)-[:BELONGS_TO]-(:Tenant) 
   OR any(t in tenants WHERE (parentD)-[:BELONGS_TO]-(t))) 
 AND (not (childD)-[:BELONGS_TO]-(:Tenant) 
   OR any(t in tenants WHERE (childD)-[:BELONGS_TO]-(t)))  
MATCH (childD)<-[:SET_FOR]-(filterValue630:Value)-[:SET_ON]->(filterCharacteristic630:Characteristic) 
WHERE id(filterCharacteristic630) = 630 
WITH filterValue630, childD, ru, u 
WHERE  (filterValue630.value <= 799621200000)  
OPTIONAL MATCH (childD)<-[:SET_FOR]->(sortValue631:Value)-[:SET_ON]->(sortCharacteristic631:Characteristic) 
WHERE id(sortCharacteristic631) = 631 
RETURN ru, u, childD AS decision, 
[ (parentD)<-[:DEFINED_BY]-(entity)<-[:COMMENTED_ON]-(comg:CommentGroup)-[:COMMENTED_FOR]->(childD) 
 | {entityId: id(entity),  types: labels(entity), totalComments: toInt(comg.totalComments)} ] AS commentGroups, 
[ (parentD)<-[:DEFINED_BY]-(c1:Criterion)<-[:VOTED_ON]-(vg1:VoteGroup)-[:VOTED_FOR]->(childD) 
 | {criterionId: id(c1),  weight: vg1.avgVotesWeight, totalVotes: toInt(vg1.totalVotes)} ] AS weightedCriteria, 
[ (parentD)<-[:DEFINED_BY]-(ch1:Characteristic)<-[:SET_ON]-(v1:Value)-[:SET_FOR]->(childD) 
 | {characteristicId: id(ch1),  value: v1.value, valueType: ch1.valueType, visualMode: ch1.visualMode} ] AS valuedCharacteristics 
ORDER BY sortValue631.value ASC, childD.createDate DESC 
SKIP 0 LIMIT 100

as a result of this query execution I receive 15 records where each of them correctly contains populated commentGroups, weightedCriteria and valuedCharacteristics collections.

But when I changing my query to the following one(I'm adding sort condition by criteria weight):

MATCH (t:Tenant) 
WHERE ID(t) in {tenantIds} 
 OR t.isPublic 
WITH COLLECT(t) as tenants 
MATCH (parentD)-[:CONTAINS]->(childD:Decision)-[ru:CREATED_BY]->(u:User) 
WHERE id(parentD) = {decisionId} 
 AND (not (parentD)-[:BELONGS_TO]-(:Tenant) 
  OR any(t in tenants WHERE (parentD)-[:BELONGS_TO]-(t))) 
 AND (not (childD)-[:BELONGS_TO]-(:Tenant) 
  OR any(t in tenants WHERE (childD)-[:BELONGS_TO]-(t)))  
MATCH (childD)<-[:SET_FOR]-(filterValue630:Value)-[:SET_ON]->(filterCharacteristic630:Characteristic) 
WHERE id(filterCharacteristic630) = 630 
WITH filterValue630, childD, ru, u 
WHERE  (filterValue630.value <= 799621200000)  
OPTIONAL MATCH (childD)<-[:VOTED_FOR]-(vg:VoteGroup)-[:VOTED_ON]->(c:Criterion) 
WHERE id(c) IN {criteriaIds} 
WITH c, childD, ru, u, (vg.avgVotesWeight * (CASE WHEN c IS NOT NULL THEN coalesce({criteriaCoefficients}[toString(id(c))], 1.0) ELSE 1.0 END)) as weight, vg.totalVotes as totalVotes 
OPTIONAL MATCH (childD)<-[:SET_FOR]->(sortValue631:Value)-[:SET_ON]->(sortCharacteristic631:Characteristic) 
WHERE id(sortCharacteristic631) = 631 
RETURN ru, u, childD AS decision, toFloat(sum(weight)) as weight, toInt(sum(totalVotes)) as totalVotes, sortValue631, 
[ (parentD)<-[:DEFINED_BY]-(entity)<-[:COMMENTED_ON]-(comg:CommentGroup)-[:COMMENTED_FOR]->(childD) 
 | {entityId: id(entity),  types: labels(entity), totalComments: toInt(comg.totalComments)} ] AS commentGroups, 
[ (parentD)<-[:DEFINED_BY]-(c1:Criterion)<-[:VOTED_ON]-(vg1:VoteGroup)-[:VOTED_FOR]->(childD) 
 | {criterionId: id(c1),  weight: vg1.avgVotesWeight, totalVotes: toInt(vg1.totalVotes)} ] AS weightedCriteria, 
[ (parentD)<-[:DEFINED_BY]-(ch1:Characteristic)<-[:SET_ON]-(v1:Value)-[:SET_FOR]->(childD) 
 | {characteristicId: id(ch1),  value: v1.value, valueType: ch1.valueType, visualMode: ch1.visualMode} ] AS valuedCharacteristics 
ORDER BY  weight DESC,  totalVotes ASC, sortValue631.value ASC, childD.createDate DESC 
SKIP 0 LIMIT 100

the query works without errors and returns the same result set of 15 records but commentGroups, weightedCriteria and valuedCharacteristics collections are only populated where weight > 0 The rest of them are null

This is wrong and not as expected. The commentGroups, weightedCriteria and valuedCharacteristics collections should be populated for all records in my result set as it was after the first query execution.

Right now I don't understand why the following part of new Cypher query prevents correct population of the mentioned collections:

OPTIONAL MATCH (childD)<-[:VOTED_FOR]-(vg:VoteGroup)-[:VOTED_ON]->(c:Criterion) 
WHERE id(c) IN {criteriaIds} 
WITH c, childD, ru, u, (vg.avgVotesWeight * (CASE WHEN c IS NOT NULL THEN coalesce({criteriaCoefficients}[toString(id(c))], 1.0) ELSE 1.0 END)) as weight, vg.totalVotes as totalVotes 

What am I doing wrong within a new query and how to fix it?

UPDATED

This is the query which produces the issue:

MATCH (t:Tenant) WHERE ID(t) in [] 
  OR t.isPublic 
  WITH COLLECT(t) as tenants 
  MATCH (parentD)-[:CONTAINS]->(childD:Decision)-[ru:CREATED_BY]->(u:User) 
  WHERE id(parentD) = 60565 
    AND (not (parentD)-[:BELONGS_TO]-(:Tenant) 
      OR any(t in tenants WHERE (parentD)-[:BELONGS_TO]-(t))) 
    AND (not (childD)-[:BELONGS_TO]-(:Tenant) 
      OR any(t in tenants WHERE (childD)-[:BELONGS_TO]-(t)))  
  MATCH (childD)<-[:SET_FOR]-(filterValue60639:Value)-[:SET_ON]->(filterCharacteristic60639:Characteristic) 
  WHERE id(filterCharacteristic60639) = 60639 
  WITH filterValue60639, childD, ru, u 
  WHERE  (filterValue60639.value <= 799621200000)  
  OPTIONAL MATCH (childD)<-[:VOTED_FOR]-(vg:VoteGroup)-[:VOTED_ON]->(c:Criterion) 
  WHERE id(c) IN [60581, 60575] 
  WITH childD, ru, u, vg.avgVotesWeight as weight, vg.totalVotes as totalVotes 
  OPTIONAL MATCH (childD)<-[:SET_FOR]->(sortValue60640:Value)-[:SET_ON]->(sortCharacteristic60640:Characteristic) 
  WHERE id(sortCharacteristic60640) = 60640 
  RETURN ru, u, childD AS decision, toFloat(sum(weight)) as weight, toInt(sum(totalVotes)) as totalVotes, sortValue60640, 
  [ (parentD)<-[:DEFINED_BY]-(entity)<-[:COMMENTED_ON]-(comg:CommentGroup)-[:COMMENTED_FOR]->(childD) 
    | {entityId: id(entity),  types: labels(entity), totalComments: toInt(comg.totalComments)} ] AS commentGroups, 
  [ (parentD)<-[:DEFINED_BY]-(c1:Criterion)<-[:VOTED_ON]-(vg1:VoteGroup)-[:VOTED_FOR]->(childD) 
    | {criterionId: id(c1),  weight: vg1.avgVotesWeight, totalVotes: toInt(vg1.totalVotes)} ] AS weightedCriteria, 
  [ (parentD)<-[:DEFINED_BY]-(ch1:Characteristic)<-[:SET_ON]-(v1:Value)-[:SET_FOR]->(childD) 
    | {characteristicId: id(ch1),  value: v1.value, valueType: ch1.valueType, visualMode: ch1.visualMode} ] AS valuedCharacteristics 
  ORDER BY  weight DESC,  totalVotes ASC, sortValue60640.value ASC, childD.createDate DESC 
  SKIP 0 LIMIT 100

for a some reason

  OPTIONAL MATCH (childD)<-[:VOTED_FOR]-(vg:VoteGroup)-[:VOTED_ON]->(c:Criterion) 
  WHERE id(c) IN [60581, 60575] 

prevents commentGroups, weightedCriteria and valuedCharacteristics collection population for all childD that do not match this expression.. How to fix this ?

Answer 1

Okay, this is a rather odd thing. I found something that should work, though at the moment I can't tell why it's working, just that it involves calculating weight and totalVotes before your return.

Take the first line of your RETURN, and replace it with this, which includes a WITH clause first, which will calculate the weight and totalVotes, then perform the RETURN:

WITH ru, u, childD, toFloat(sum(weight)) as weight, toInt(sum(totalVotes)) as totalVotes, sortValue60640
RETURN ru, u, childD AS decision, weight, totalVotes, sortValue60640,

One other thing to note, you can save some unnecessary operations by performing your ORDER BY, SKIP, and LIMIT operations before you perform your pattern comprehensions:

WITH ru, u, childD, toFloat(sum(weight)) as weight, toInt(sum(totalVotes)) as totalVotes, sortValue60640
ORDER BY  weight DESC,  totalVotes ASC, sortValue60640.value ASC, childD.createDate DESC 
  SKIP 0 LIMIT 100
RETURN ru, u, childD AS decision, weight, totalVotes, sortValue60640,
  [ (parentD)<-[:DEFINED_BY]-(entity)<-[:COMMENTED_ON]-(comg:CommentGroup)-[:COMMENTED_FOR]->(childD) 
    | {entityId: id(entity),  types: labels(entity), totalComments: toInt(comg.totalComments)} ] AS commentGroups, 
  [ (parentD)<-[:DEFINED_BY]-(c1:Criterion)<-[:VOTED_ON]-(vg1:VoteGroup)-[:VOTED_FOR]->(childD) 
    | {criterionId: id(c1),  weight: vg1.avgVotesWeight, totalVotes: toInt(vg1.totalVotes)} ] AS weightedCriteria, 
  [ (parentD)<-[:DEFINED_BY]-(ch1:Characteristic)<-[:SET_ON]-(v1:Value)-[:SET_FOR]->(childD) 
    | {characteristicId: id(ch1),  value: v1.value, valueType: ch1.valueType, visualMode: ch1.visualMode} ] AS valuedCharacteristics