Create relationship between two nodes of the same type if they share connections with two nodes of different types

[simone]

Hello,

EDITED after the first comment:

I am trying to make a projection in my graph. Right now I have something like:

(Place)<-[LIVES_AT]-(B)-[LIKES]->(Food)

I'd like to connect all the (B)s that LIVES_AT the same (Place) and LIKES the same (Food).

Nodes of type (Food) and (Place) have a property called "Name" that specifies the actual Food or the address of the Place.

How to express this idea in Cypher?

[alejandropuerto]

Hi, @simone!

You can use WHERE to filter only those who share the name property in both A and C nodes. Then you can create a relationship to connect both nodes.

It could be something like this:

MATCH (A)<-[LOCATION]-(B)-[PREFERENCE]->(C)
WHERE A.name = C.name
WITH A, C
MERGE (A)-[NEW_RELATIONSHIP]->(C)

[simone]

Hi @alejandropuerto, sorry I have not been more clear, but A.name will never be equal to C.name

C.name = pizza
A.name = madison avenue

What I need is to connect (B) to another (B) if they both are connected to (A) with the same name AND if they are connected to the same (C) - i.e. they both live in madison avenue and they both love pizza...

I have edited my question to reflect this clarification, HTH

[andrew_bowman]

Depends on how you want to connect them...directly in pairs, or by creating some node that represents a place and a food, and connecting them to people who meet the criteria for both.

In any case, here's one approach, using subqueries (from 4.x) to constrain the aggregations per place, for more efficient memory usage. :

MATCH (pl:Place)
CALL {
 WITH pl
 MATCH (pl)<-[:LIVES_AT]-(person)-[:LIKES]->(food:Food)
 WITH food, collect(person) as persons
 // now you have, at a given place, per distinct food, a list of persons who like it
 // you could create your :FoodPlace node and connect people, who UNWIND your persons twice and MERGE relationships if you want to connect them in pairs
 RETURN count(*) as count // just a placeholder, as right now subqueries require a return
}
RETURN count(*) as total // just a placeholder, as right now we can't end with a CALL

[simone]

that's very interesting @andrew.bowman , thank you so much!

Yes the idea would be to connect them in dyads...

But I think that your:

"UNWIND your persons twice and MERGE relationships if you want to connect them in pairs"

is exactly what I need to finish this... could you please elaborate on how to accomplish this? If I UNWIND twice I just get a list of nodes... what am I missing?

[andrew_bowman · Accepted solution]

So in order to connect up all persons in your collections (per food/place), you need all combinations of the persons in your collection, then merging of the relationships. There are two primary ways to do this.

First, the slightly more complex way, which you can do with just Cypher: generate a cartesian product of the elements of the collection, then filter to ensure you're dealing with just combinations (filtering out person x themselves as well as the same pairings, but in swapped order).

The cartesian product we can get with doing UNWIND twice (since UNWIND generates a row per list element). The filtering we can get with a comparison of node ids.

MATCH (pl:Place)
CALL {
 WITH pl
 MATCH (pl)<-[:LIVES_AT]-(person)-[:LIKES]->(food:Food)
 WITH food, collect(person) as persons
 UNWIND persons as person1
 UNWIND persons as person2
 WITH food, person1, person2
 WHERE id(person1) < id(person2)
 MERGE (person1)-[:FOOD_PLACE_SIMILARITY]-(person2)
 RETURN count(*) as count
}
RETURN count(*) as total

The other way requires APOC Procedures to generate combinations:

MATCH (pl:Place)
CALL {
 WITH pl
 MATCH (pl)<-[:LIVES_AT]-(person)-[:LIKES]->(food:Food)
 WITH food, collect(person) as persons
 WITH food, apoc.coll.combinations(persons, 2) as combos
 UNWIND combos as pair
 WITH food, pair[0] as person1, pair[1] as person2
 MERGE (person1)-[:FOOD_PLACE_SIMILARITY]-(person2)
 RETURN count(*) as count
}
RETURN count(*) as total

And if you plan to run this as is, making it a graph-wide query creating all these relationships, it's best to use apoc.periodic.iterate() to batch your writes so you don't blow your heap trying to merge all these relationships in a single transaction for a huge graph.