How can we compare one relationship property with another relationship property and modify the relationship accordingly

Hi,
I am new to neo4j db and I will try to explain my issue. So I have a list of RFID tags and I am adding the start time end time and duration of these tags when contacted with each other. I would like to iterate through these relations and find whether this start time and end time of one relation coincides with the other relation and then do some modification to the relation. Below is my relation.

+------------------------------------------------------------------------------+
| m.tagId | n.tagId | rel                                                      |
+------------------------------------------------------------------------------+
| 1       | 2       | [:CONTACTED {StartTime: 6.3, Duration: 2.7, EndTime: 9}] |
| 1       | 2       | [:CONTACTED {StartTime: 6, Duration: 2.3, EndTime: 8.3}] |
+------------------------------------------------------------------------------+

What I need to do is, when StartTime of relation 1 is <= EndTime of relation 2 and StartTime of relation 2 is <= EndTime of relation 1 then modify the relation like this

+------------------------------------------------------------------------------+
| m.tagId | n.tagId | rel                                                      |
+------------------------------------------------------------------------------+
| 1       | 2       | [:CONTACTED {StartTime: 6, Duration: 3, EndTime: 9}] |

That is new relation's StartTime is MIN(6,6.3) and EndTime is MAX(8.3,9)

What I am looking for is a nested loop to iterate through all the relationships and check the condition then add/delete the relationship. Is there any possible way to do this?

We have a knowledge base article on time range overlap that might be useful:

If ordering by StartTime, one approach to compare adjacent relationships would be:

  1. Do your MATCH
  2. Order by the rel's StartTime property
  3. Collect() the relationships (they will be ordered)
  4. UNWIND apoc.coll.pairsMin() from APOC Procedures so each row consists of an adjacent pairing from the list
  5. Do your comparisons, filterings, and changes to the relationship as needed.

If there's a chance there may be more than just two in a row that overlap, you may need something more intricate.

1 Like

Thank you for your reply. While going ahead with your suggestion i came across with this error. The collection function is not registered. So I added

dbms.security.procedures.whitelist=apoc.coll.,apoc.load.,apoc.*

this to the conf file and restarted neo4j service but no luck.

Try with this:
dbms.security.procedures.unrestricted=apoc.*

1 Like

No luck.my neo4j version is 3.5.14 and apoc jar is apoc-3.5.0.11-all.jar. Is this ok?

Looks like apoc.coll.pairsMin() is available in version 4.0 and above.

1 Like

Okay thanks. I will try to install version 4.0

I purged old version and installed new version but still no luck.
Also when I tried to print the version I got a waning like this

APOC couln't set a URLStreamHandlerFactory since some other tool already did this (e.g. tomcat). This means you cannot use s3:// or hdfs:// style URLs in APOC. This is caused by a limitation of the JVM which we cannot fix.

Thank you, apoc issue is fixed. And I tried the the above logic and it worked fine for 2 entries. But how can we do this for n entries.? Or is there any other way to do this?

Glad to hear that!

If you can share some more info on 'n' entries. How are you searching these 'n' entries? Share your Cypher query so that I can help you with.

1 Like

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.
:expressionless:

Q: How do you select the above three blocks? 
I run this:
MATCH (m:Tag)-[rel:CONTACTED]->(n:Tag)
RETURN m, n

This shows the two nodes with seven relationships.  

For the first block here is the solution:
MATCH 
	(m:Tag)-[rel:CONTACTED]->(n:Tag) 
WHERE 
	((m.tagId=2266 AND n.tagId=2253) OR (m.tagId=2253 AND n.tagId=2266)) 

WITH collect(rel.StartTime) as rs, collect(rel.EndTime) as re, collect(rel) as r1
WITH r1, apoc.coll.min(rs) as mn, apoc.coll.max(re) as mx
WITH r1, mn, mx, (mx - mn) as dr
UNWIND r1[0] as r2    
SET r2.StartTime = mn, r2.Duration = dr, r2.EndTime = mx
//RETURN  r2

//deleting two relationships.............
delete r1[1]
delete r1[2]
return m, n

Hi,
Thank you for your reply.
To select I use like this

MATCH (m)-[rel:CONTACTED]->(n) RETURN m.tagId, n.tagId, rel;

And about the above solution I think it takes all the start time and end time and then take min and max respectively,but what we need is take max and min for only those relations whose ranges overlap.
When i executed the code only the Duration got changed nothing else.

+-----------------------------------------------------------------------------------------------+
| m.tagId | n.tagId | rel                                                                       |
+-----------------------------------------------------------------------------------------------+
| 2253    | 2266    | [:CONTACTED {StartTime: 1605284000, Duration: 1000, EndTime: 1605285000}] |
| 2253    | 2266    | [:CONTACTED {StartTime: 1605284100, Duration: 400, EndTime: 1605284500}]  |
| 2253    | 2266    | [:CONTACTED {StartTime: 1605285000, Duration: 100, EndTime: 1605285100}]  |
| 2253    | 2266    | [:CONTACTED {StartTime: 1605286000, Duration: 1000, EndTime: 1605287000}] |
| 2253    | 2266    | [:CONTACTED {StartTime: 1605285800, Duration: 1400, EndTime: 1605287200}] |
| 2253    | 2266    | [:CONTACTED {StartTime: 1605287500, Duration: 500, EndTime: 1605288000}]  |
| 2253    | 2266    | [:CONTACTED {StartTime: 1605287800, Duration: 400, EndTime: 1605288200}]  |
+-----------------------------------------------------------------------------------------------+

Please share your Cypher query that you use to group the results like 3, 2, 2 rows.

Oh. I don't have any particular query. What i did was manually repeated my shared queries twice or thrice till no overlapping

Hi,
Now it's partially working. Issue is

  1. I need to manually repeat the below code in order to get the final result. So is there anyway to iterate the below code n-1 times(where n is count of relations)
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  
UNWIND 
	apoc.coll.pairsMin(relations) AS pair 
WITH
	pair[0] AS first, pair[1] AS next
// RETURN first, next;
WHERE
	(first.StartTime<=next.EndTime AND next.StartTime<=first.EndTime)
WITH	
	[first.StartTime,next.StartTime] AS startarray, [first.EndTime,next.EndTime] AS endarray, first AS rel1,next 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 
	MATCH(m:Tag {tagId:2266}) MATCH(n:Tag {tagId:2253}) 
	MERGE(m)-[union_rel:CONTACTED {StartTime:minn,EndTime:maxx,Duration:ABS(minn-maxx)}]->(n)
	DELETE r1,r2;
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: