CYPHER storing count()-value for further usage

Question

I want to create a relationship between nodes that have one or multiple things in common and want to set the count of the common things as a property inside the relationship.

For example: in the movie-tutorial-graph I want to create a relationship between actors that have acted in the same movie(s) together and the set count of the movies they played in together as a property in the relationship.

For the basic counting, the tutorial provides a query:

MATCH (n)-[:ACTED_IN]->(m)<-[:ACTED_IN]-(coActors)
RETURN n.name, coActors.name, count(*) AS Strength ORDER BY Strength DESC

This gives me a list of 2 names and the amount of times they played in movies together (f.e. "Keanu Reeves", "Carrie-Anne Moss", Strength: 3 -> as there are the 3 Matrix movies inside the graph.)

Now I want to create a relationship (ACTED_WITH) between these pairs and set the strength-value as a property inside it. I can create a relationship like this:

MATCH (a)-[:ACTED_IN]->(p)<-[:ACTED_IN]-(b) MERGE (a)-[r:ACTED_WITH]->(b)

MERGE ensures that there is only one relationship created, but I just can't get the counting-stuff to work with the creation.

Answer 1

I'm not sure to understand what you want but maybe something like that:

  MATCH (a)-[:ACTED_IN]->(m)<-[:ACTED_IN]-(b) 
   WITH  a,b, collect(m) AS movies ORDER BY size(movies)  DESC
   MERGE (a)-[r:ACTED_WITH]-(b)
   ON CREATE SET r.Strength=SIZE(movies)

Answer 2

You can use the SET Cypher clause to set properties on a matched node. If you try to set it in the MERGE clause, than merge will treat the key(s) as a unique identifier, and will create a new relationship if one doesn't exist with that specific value yet.

MATCH (a)-[:ACTED_IN]->(p)<-[:ACTED_IN]-(b)
MERGE (a)-[r:ACTED_WITH]->(b)
// reduce matched set to one row of data
WITH DISTINCT a, b, r, COUNT(p) as strength, COLLECT(p) as movies
// set r
SET r.strength = strength
// Return everything to verify above results
RETURN *

SET will overwrite any previous value. If you want to only set it if you created the relationship, you can use ON CREATE or ON MATCH.