CODEWITHSUNDEEP
neo4jcypher
alexanoid
alexanoid

Reputation: 25852

Neo4j Cypher query and index of element in the collection

I'm trying to find index number of Decision by {decisionGroupId}, {decisionId} and {criteriaIds}

This is my current Cypher query:

MATCH (dg:DecisionGroup)-[:CONTAINS]->(childD:Decision) 
WHERE dg.id = {decisionGroupId} 
OPTIONAL MATCH (childD)-[vg:HAS_VOTE_ON]->(c:Criterion) 
WHERE c.id IN {criteriaIds} 
WITH childD, vg.avgVotesWeight as weight, vg.totalVotes as totalVotes 
ORDER BY weight DESC, totalVotes DESC 
WITH COLLECT(childD) AS ps 
RETURN REDUCE(ix = -1, i IN RANGE(0, SIZE(ps)-1) 
 | CASE ps[i].id WHEN {decisionId} THEN i ELSE ix END) AS ix

I have only 3 Decision in the database but this query returns the following indices:

2
3
4

while I expecting something like(starting from 0 and -1 if not found)

0
1
2

What is wrong with my query and how to fix it?

UPDATED

This query is working fine with COLLECT(DISTINCT childD) AS ps:

MATCH (dg:DecisionGroup)-[:CONTAINS]->(childD:Decision) 
WHERE dg.id = {decisionGroupId} 
OPTIONAL MATCH (childD)-[vg:HAS_VOTE_ON]->(c:Criterion) 
WHERE c.id IN {criteriaIds} 
WITH childD, vg.avgVotesWeight as weight, vg.totalVotes as totalVotes 
ORDER BY weight DESC, totalVotes DESC 
WITH COLLECT(DISTINCT childD) AS ps 
RETURN REDUCE(ix = -1, i IN RANGE(0, SIZE(ps)-1) 
 | CASE ps[i].id WHEN {decisionId} THEN i ELSE ix END) AS ix

Please help me to refactor this query and get rid of heavy REDUCE.

Upvotes: 2

Views: 1769

Answers (2)

Michael Hunger
Michael Hunger

Reputation: 41706

If you have APOC installed, you can also use the function:

return apoc.coll.indexOf([1,2,3],2)

Upvotes: 2

Gabor Szarnyas
Gabor Szarnyas

Reputation: 5057

Let's try to get the reduce part right with a simpler query:

WITH ['a', 'b', 'c'] AS ps
RETURN
  reduce(ix = -1, i IN RANGE(0, SIZE(ps)-1) |
    CASE ps[i] WHEN 'b' THEN i ELSE ix END) AS ix
  )

As I stated in the comments, it is usually better to avoid reduce if possible. So, to express the same using a list comprehension, use WHERE for filtering.

WITH ['a', 'b', 'c'] AS ps
RETURN [i IN RANGE(0, SIZE(ps)-1) WHERE ps[i] = 'b'][0]

The list comprehension results in a list with a single element, and we will use the [0] indexer to select that element.

After adapting this to your query, we'll get something like this:

MATCH (dg:DecisionGroup)-[:CONTAINS]->(childD:Decision) 
WHERE dg.id = {decisionGroupId} 
OPTIONAL MATCH (childD)-[vg:HAS_VOTE_ON]->(c:Criterion) 
WHERE c.id IN {criteriaIds} 
WITH childD, vg.avgVotesWeight as weight, vg.totalVotes as totalVotes 
ORDER BY weight DESC, totalVotes DESC 
WITH COLLECT(DISTINCT childD) AS ps 
RETURN [i IN RANGE(0, SIZE(ps)-1) WHERE ps[i].id = {decisionId}][0]

Upvotes: 2

Related Questions