In my Neo4j, SDN4 project I have a following entities:
Decision, Criterion, Vote
Every Decision can have a Vote on a different Criteria.
Right now I use a following Cypher query in order to sort Decisions by avg. vote weights of a selected Criteria ({criteriaIds} parameter represents Set<Long> of Criterion IDs):
MATCH (parentD)-[:CONTAINS]->(childD:Decision)
WHERE id(parentD) = {decisionId}
OPTIONAL MATCH (childD)-[vg:HAS_VOTE_ON]->(c)
WHERE id(c) IN {criteriaIds}
WITH c, childD, (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
WITH * MATCH (childD)-[ru:CREATED_BY]->(u:User)
RETURN ru, u, childD AS decision, toFloat(sum(weight)) as weight, toInt(sum(totalVotes)) as totalVotes
ORDER BY weight DESC, childD.name ASC
SKIP 0 LIMIT 10
Right now I'm not satisfied with a performance of this query so I'd like to reduce a data cardinality during the query execution.
In order to do this, instead of storing every avgVotesWeight as a property of relationship between Decision and Criterion I plan to store this value as a property of Decision node itself.
For example I have a following nodes:
Decision1(uid = 1)
Criterion1(uid = 1)
Criterion2(uid = 2)
Criterion3(uid = 3)
Decision1 have a following avgVotesWeight on the criteria:
Decision1 for Criterion1 = 4.3
Decision2 for Criterion1 = 2.1
Decision3 for Criterion1 = 1.8
so I'm going to create a following properties on Decision1 node:
Decision1(CAVW1=4.3, CAVW2=2.1, CAVW3=1.8)
where CAVW is a plain String prefix in order to distinguish these properties from the rest of the Decision properties in my SDN4 Custom Type Converter an 1 or 2 or 3 suffixes are Criterion uid(I'm passing these uid to my Cypher query in {criteriaIds} parameter)
So my questions are - Is it a good idea in general? If so, could you please help me to rewrite the mentioned query above in order to use these properties instead of the following old Cypher query:
WHERE id(c) IN {criteriaIds}
WITH c, childD, (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
Also, I don't know right now where to keep vg.totalVotes values that are currently also stored at the same relationship between Decision and Criterion. Please advise. Maybe it should be also stored with the same approach like I want to implement for avgVotesWeight ? For example I can introduce addition Decision properties like TV1=34. Is it a good idea ?
To whoever may be interested in a similar solution, this is my new working query:
MATCH (parentD)-[:CONTAINS]->(childD:Decision)
WHERE id(parentD) = {decisionId}
UNWIND {criteriaIds} AS cid
WITH childD, (properties(childD)['CAVW' + cid] * (CASE WHEN cid IS NOT NULL THEN coalesce({criteriaCoefficients}[toString(cid)], 1.0) ELSE 1.0 END)) AS weight, properties(childD)['CTV' + cid] AS totalVotes
WITH * MATCH (childD)-[ru:CREATED_BY]->(u:User)
RETURN ru, u, childD AS decision, toFloat(sum(weight)) as weight, toInt(sum(totalVotes)) as totalVotes
ORDER BY weight
DESC SKIP 0 LIMIT 2