Neo4j / Cypher: create relationship if certain condition is met

Question

I am doing a sum operation in my graph DB: I compare a couple of nodes, calculate a figure to represent how similar certain regions of the graph are and if that figure is big enough, I want to create a relationship between the nodes.

I've got a query which does all that, except for the check if the figure is big enough; it currently also creates similarity relationships whose similarity score is 0 - and I don't want that.

My full cypher query is somewhat longish, so I've simplified it for this post. Therefore I'm afraid I cannot provide a sample graph db in the neo4j console. My graph contains Center nodes, which have Affinity nodes and Searched nodes around them. If 2 Center nodes have similar Affinity or Searched nodes, the Center nodes shall get a relationship.

Here's the simplified statement with annotations:

MATCH (a:Center), (x:Center)
WHERE id(a) <> id(x)
OPTIONAL MATCH a-->(aff1:Affinity), x-->(aff2:Affinity)
WHERE aff1.affinityReference=aff2.affinityReference     // if the Affinity nodes have the same reference, then their Center nodes are similar
OPTIONAL MATCH a-->(search1:Search), x-->(search2:Search)
WHERE search1.searchTerm = search2.searchTerm   // if the Search nodes have the same searchTerm, then their Center nodes are similar
WITH a, x, 
SUM (CASE WHEN aff2.relative_weight IS NULL THEN 0 ELSE (aff2.relative_weight * 5) END) AS AffinityScore, // Affinity nodes have a relative weight, which shall be used in the similarity calculation.
(count(search2) * 5) AS SearchScore   // matching Search nodes shall just be counted and multiplied with 5.

OPTIONAL MATCH x-[r1:IS_SIMILAR_TO]->()  // Delete all similarity relationships for x
WITH a,x,r1,AffinityScore, SearchScore, (AffinityScore+SearchScore) AS TotalScore

DELETE r1   // delete relationship if it exists...
MERGE      // ... and create it anew.
  x-[:IS_SIMILAR_TO {
  SimilarityScore:Total,
  AffinityScore:AffinityScore,
 SearchScore:SearchScore
 }]->a

RETURN a, x, AffintyScore, SearchScore, TotalScore 
ORDER BY TotalScore DESC

I've tried introducing a CASE statement in various places, but apparently never in the right one. Where should it go?

Thanks for your help!

Stefan Armbruster Stefan Armbruster · Accepted Answer · 2014-10-25T21:23:41

There is a trick to do a conditional mutating operation: use a CASE statement to return a list of length 1 when the condition is true, otherwise an empty list. Afterwards a FORACH to iterate over that array to do the CREATE or MERGE

...
WITH a, x, AffintyScore, SearchScore, TotalScore, Total, 
  CASE AffinityScore WHEN 0 THEN [] ELSE [1] END as array
FOREACH (x in array | 
   MERGE
   x-[:IS_SIMILAR_TO {
   SimilarityScore:Total,
   AffinityScore:AffinityScore,
   SearchScore:SearchScore
   }]->a 
)
RETURN a, x, AffintyScore, SearchScore, TotalScore 
ORDER BY TotalScore DESC

Neo4j / Cypher: create relationship if certain condition is met

1 Answers