🌧 Create Relationship Between 3 tables

cypher
relationship

(Moji Tavoni) #1

Is it possible to create relationships between table1 with table3 by using table2?
for example in:
--Table1 we have id and momName
--Table2 we have id and table1ID and Table3ID
--Table3 we have id and dadName
we need to create relationship between mom and dad directly by just one type of relationship family without showing the table2 nodes.


(Ameyasoft) #2

Hi,
Assuming

T1.csv:
ID,MomName
1,Mom1

CREATE (m:Mom {id: 1, name:"Mom1"})

T3.csv:
ID,DadName
1,Dad1

CREATE (m:Dad {id: 1, name:"Dad1"})

T2.csv:
ID,T1ID,T2ID
1,1,1
2,2,2

LOAD T2.csv....
MATCH (m:Mom) WHERE m.id = T1ID
MATCH (d:Dad) WHERE m.id = T1ID
MERGE (m)-[:FAMILY]->(d)

If the tables are in SQL Server, then you can use this query to generate one file for import.

select a.ID, b.ID as MomID, b.MomName, c.ID as DadID, c.DadName
from Table2 a
inner join Table1 b on b.ID = a.MomID
inner join Table2 c on c.ID = a.DadID

This results in:
shahak

Import the data to create nodes and relationships:
CREATE (m:Mom {id: 1, name:"Mom1"})
CREATE (m:Dad {id: 1, name:"Dad1"})
MERGE (m)-[:FAMILY]->(d)

-Kamal


(Ameyasoft) #3

Hi,

Sorry for the typo:

LOAD T2,csv should read

LOAD T2.csv....
MATCH (m:Mom) WHERE m.id = T1ID
MATCH (d:Dad) WHERE m.id = T2ID
MERGE (m)-[:FAMILY]->gt;(d)

-Kamal


(Moji Tavoni) #4

thanks for the idea but what about millions of records.
I mean if we have a dad who has 40 mom(wife) relationships. by this theory, our unique table will be infinity and each query need lot's of time even if our computer can handle that.

what do you think about this one:

MATCH (t2:Table2)
MATCH (t1:Table1) WHERE t1.id in t2.momID
MATCH (t3:Table3) WHERE t3.id in t2.dadID
MERGE (p)<-[:FAMILY]->(a)

(Ameyasoft) #5

Yes, this should work.
MERGE (t1)<-[:FAMILY]->(t3)

-Kamal


(Ameyasoft) #6

I stand corrected. This approach may create some wrong relationships between mom and dad and may not follow the relations defined in Table2.

-Kamal


(Ameyasoft) #7

I did some testing with sample data.

Here is the Cypher script:
CREATE (m:Mom {id:1, name:"Mom1"})
CREATE (m1:Mom {id:2, name:"Mom2"})
CREATE (m2:Mom {id:3, name:"Mom3"})
CREATE (m3:Mom {id:4, name:"Mom4"})

CREATE (m4:Dad {id:1, name:"Dad1"})
CREATE (m5:Dad {id:2, name:"Dad2"})
CREATE (m6:Dad {id:3, name:"Dad3"})
CREATE (m7:Dad {id:4, name:"Dad4"})

CREATE (r:Rel {id:1, momID:1, dadID:1})
CREATE (r1:Rel {id:2, momID:2, dadID:2})
CREATE (r2:Rel {id:3, momID:3, dadID:3})
CREATE (r3:Rel {id:4, momID:4, dadID:4})

CREATE (r4:Rel {id:5, momID:2, dadID:1})
CREATE (r5:Rel {id:6, momID:2, dadID:3})
CREATE (r6:Rel {id:7, momID:1, dadID:3});

Ran the following query to create the relationships:
MATCH (r:Rel)
MATCH (m:Mom) WHERE m.id in r.momID
MATCH (d:Dad) WHERE d.id in r.dadID
MERGE (m)-[:FAMILY]->(d)
RETURN m, d;

and the result is:
mojo1

Looks like it's working as expected. Hope this will work for you.
-Kamal


(Michael Hunger) #8

With millions of rows:

make sure you have:

  1. indexes / constraints for :Person(id)
  2. use: USING PERIODIC COMMIT to batch writes.