Sure.
Pardon the long explanation.
Let's say this is our n relations (here 7) relations
+-----------------------------------------------------------------------------------------------+
| m.tagId | n.tagId | rel |
+-----------------------------------------------------------------------------------------------+
| m.tagId | n.tagId | rel |
+-----------------------------------------------------------------------------------------------+
| 2253 | 2266 | [:CONTACTED {StartTime: 1605284000, Duration: 1500, EndTime: 1605285000}] |
| 2253 | 2266 | [:CONTACTED {StartTime: 1605284100, Duration: 1500, EndTime: 1605284500}] |
| 2253 | 2266 | [:CONTACTED {StartTime: 1605285000, Duration: 1500, EndTime: 1605285100}] |
| 2253 | 2266 | [:CONTACTED {StartTime: 1605286000, Duration: 1500, EndTime: 1605287000}] |
| 2253 | 2266 | [:CONTACTED {StartTime: 1605285800, Duration: 1500, EndTime: 1605287200}] |
| 2253 | 2266 | [:CONTACTED {StartTime: 1605287500, Duration: 1500, EndTime: 1605288000}] |
| 2253 | 2266 | [:CONTACTED {StartTime: 1605287800, Duration: 1500, EndTime: 1605288200}] |
+-----------------------------------------------------------------------------------------------+
For the first 3 relations the code will be this
MATCH
(m:Tag)-[rel:CONTACTED]->(n:Tag)
WHERE
((m.tagId=2266 AND n.tagId=2253) OR (m.tagId=2253 AND n.tagId=2266))
WITH
rel
ORDER BY
rel.StartTime
WITH
COLLECT(rel) AS relations
WITH
apoc.coll.pairsMin(relations) AS r
MATCH
(m:Tag)-[rel:CONTACTED]->(n:Tag)
WHERE
((m.tagId=2266 AND n.tagId=2253) OR (m.tagId=2253 AND n.tagId=2266) AND (r[0][0].StartTime<=r[0][1].EndTime AND r[0][1].StartTime<=r[0][0].EndTime))
WITH
[r[0][0].StartTime,r[0][1].StartTime] AS startarray, [r[0][0].EndTime,r[0][1].EndTime] AS endarray,r[0][0] AS rel1,r[0][1] AS rel2
UNWIND
startarray AS minstart
UNWIND
endarray AS maxend
WITH
rel1 AS r1, rel2 AS r2,MIN(minstart) AS minn, MAX(maxend) AS maxx
SET
r1.StartTime=minn,r1.EndTime=maxx,r1.Duration=ABS(minn-maxx) DELETE r2;
For the next two relations code will look like this
MATCH
(m:Tag)-[rel:CONTACTED]->(n:Tag)
WHERE
((m.tagId=2266 AND n.tagId=2253) OR (m.tagId=2253 AND n.tagId=2266))
WITH
rel
ORDER BY
rel.StartTime
WITH
COLLECT(rel) AS relations
WITH
apoc.coll.pairsMin(relations) AS r
MATCH
(m:Tag)-[rel:CONTACTED]->(n:Tag)
WHERE
((m.tagId=2266 AND n.tagId=2253) OR (m.tagId=2253 AND n.tagId=2266) AND (r[1][0].StartTime<=r[1][1].EndTime AND r[1][1].StartTime<=r[1][0].EndTime))
WITH
[r[1][0].StartTime,r[1][1].StartTime] AS startarray, [r[1][0].EndTime,r[1][1].EndTime] AS endarray,r[1][0] AS rel1,r[1][1] AS rel2
UNWIND
startarray AS minstart
UNWIND
endarray AS maxend
WITH
rel1 as r1, rel2 as r2,MIN(minstart) AS minn, MAX(maxend) AS maxx
SET
r1.StartTime=minn,r1.EndTime=maxx,r1.Duration=ABS(minn-maxx) DELETE r2;
And for the last two relations the code will look like this
MATCH
(m:Tag)-[rel:CONTACTED]->(n:Tag)
WHERE
((m.tagId=2266 AND n.tagId=2253) OR (m.tagId=2253 AND n.tagId=2266))
WITH
rel
ORDER BY
rel.StartTime
WITH
COLLECT(rel) AS relations
WITH
apoc.coll.pairsMin(relations) AS r
MATCH
(m:Tag)-[rel:CONTACTED]->(n:Tag)
WHERE
((m.tagId=2266 AND n.tagId=2253) OR (m.tagId=2253 AND n.tagId=2266) AND (r[2][0].StartTime<=r[2][1].EndTime AND r[2][1].StartTime<=r[2][0].EndTime))
WITH
[r[2][0].StartTime,r[2][1].StartTime] AS startarray, [r[2][0].EndTime,r[2][1].EndTime] AS endarray,r[2][0] AS rel1,r[2][1] AS rel2
UNWIND
startarray AS minstart
UNWIND
endarray AS maxend
WITH
rel1 as r1, rel2 as r2,MIN(minstart) AS minn, MAX(maxend) AS maxx
SET
r1.StartTime=minn,r1.EndTime=maxx,r1.Duration=ABS(minn-maxx) DELETE r2;
And above three codes should be iterated multiple times to get our final db(which is the below one)
+-----------------------------------------------------------------------------------------------+
| m.tagId | n.tagId | rel |
+-----------------------------------------------------------------------------------------------+
| 2253 | 2266 | [:CONTACTED {StartTime: 1605284000, Duration: 1100, EndTime: 1605285100}] |
| 2253 | 2266 | [:CONTACTED {StartTime: 1605285800, Duration: 1400, EndTime: 1605287200}] |
| 2253 | 2266 | [:CONTACTED {StartTime: 1605287500, Duration: 700, EndTime: 1605288200}] |
+-----------------------------------------------------------------------------------------------+
What I am looking for is a general way to do the above queries( like iterating n times )through these relations.
Here is my solution to your question:
MATCH (a:Tag)-[r]-(b:Tag)
WHERE b.tagId <> a.tagId
//get min, mx of r.StartTime.............
WITH min(r.StartTime) as mn, max(r.StartTime) as mx, (min(r.StartTime) + 1000) as nxt, count(distinct r.StartTime) as Cnt
//incrementing min value by 1000 seconds .......
WITH mn, mx, nxt, (nxt + 1000) as nxt2, (nxt + 2000) as nxt3, Cnt
//starting to compile the rels.................
//First: rels with start time between min and min+1000.......
MATCH (a1:Tag)-[r1]-(b1:Tag)
WHERE b1.tagId <> a1.tagId and mn<= r1.StartTime <= nxt
WITH collect(distinct r1) as r11, nxt, nxt2, nxt3, mx, (Cnt - count(distinct r1.StartTime)) as Cnt2, Cnt
//Second: rels between nxt and nxt+1000......
MATCH (a2:Tag)-[r2]-(b2:Tag)
where b2.tagId <> a2.tagId and nxt < r2.StartTime <= nxt2
with collect(distinct r2) as r22, r11, nxt2, nxt3, mx, nxt, Cnt, (Cnt-count(distinct(r2.StartTime))) as Cnt3
//Third: rels between nxt2 and mx......
MATCH (a3:Tag)-[r3]-(b3:Tag)
WHERE b3.tagId <> a3.tagId and nxt2 < r3.StartTime <= mx
WITH collect(distinct r3) as r33, r22, r11, nxt3, mx, nxt, nxt2
//Result.....
return r11, r22, r33
From here you need to perform the steps that I posted previously for the first block.
Result:
This is not a complete screenshot:
NODES 2022, Neo4j Online Education Summit
All the sessions of the conference are now available online
