Iterativly create relations with conditions

[oli]

Hi, I wish to create relation for the event and match I pre-defined time period. For example, my event may be very long and contains several time periods and I wish to connect all of them, or may be very short event within a sigle time period. I wrote the following cypher query to do so

match (c:Car {carID:Car_ID})-[:takePlace]->(e:Event)
with collect(e) as evts 
unwind evts as evt
match (p1:Period) where p1.start_time <= evt.start_time <= p1.end_time
match (p2:Period) where p2.start_time <= evt.end_time <= p2.end_time
case p1, p2
when p1.start_time = p2.start_time and p1.end_time=p2.end_time
then 
    create (evt)-[:onPeriod]->(p1)
else 
    match (p:Period) where p1.start_time <= p.start_time and p.end_time <=p2.end_time
    create (evt)-[:onPeriod]->(p)
end

but the I got an error which confused me a lot, hope to get some ideas here,

Thanks,
oli

[oli]

UPDATE: found a way of doing so, but only the first FOREACH clause is working, for the second one, there is an error say "MATCH cannot be used inside FOREACH"

Any ideas? Thanks in advance

match (c:Car {carID:car_ID})-[:takePlace]->(e:Event)
with collect(e) as evts 
unwind evts as evt
match (p1:Period) where p1.start_time <= toInteger(evt.start_time) <= p1.end_time
match (p2:Period) where p2.start_time <= toInteger(evt.end_time) <= p2.end_time

FOREACH (idx in CASE WHEN (p1.start_time = p2.start_time and p1.end_time = p2.end_time) THEN [1] ELSE [] END |
    create (evt)-[:onPeriod]->(p1)
)

FOREACH (idx in CASE WHEN not (p1.start_time = p2.start_time and p1.end_time = p2.end_time) THEN [1] ELSE [] END |
    match (p:Period) where p1.start_time <= p.start_time and p.end_time <=p2.end_time
    create (evt)-[:onPeriod]->(p)
)

[oli]

Don't know why there is no one in the community could answer this question. Maybe I did not make it clear, here is my purpose

match (c:Car {carID:car_ID})-[:takePlace]->(e:Event)
with collect(e) as evts 
unwind evts as evt
match (p1:Period) where p1.start_time <= toInteger(evt.start_time) <= p1.end_time
match (p2:Period) where p2.start_time <= toInteger(evt.end_time) <= p2.end_time

//IF P1 == P2 : create relation on the node either P1 and P2
    create (evt)-[:onPeriod]->(p1)

//IF P1 != P2 : create relations on all the nodes between P1 or P2
    match (p:Period) where p1.start_time <= p.start_time and p.end_time <=p2.end_time
    create (evt)-[:onPeriod]->(p)

Hope I could get some insights, thanks

[Cobra]

Hello @oli

Did you have a look at APOC Conditional Cypher Execution?

Regards,
Cobra

[oli]

Good Morning Cobra,
I checked and did not make much sense on them. I really got confused from the documentation

[Reiner]

Could you explain, how that Period node is structured? Are these months or quarters?
Would be easier to understand which case this else-branch is used for and what kinds of overlapping of p1 and p2 should be solved.

[ronny_de_winter]

Can you explain your Period node? Why are you introducing it? Is it for example for aggregating data in time periods, or for supporting specific queries?
Perhaps give some examples of Events and Periods you want to relate.

[oli]

Thanks for the reply @ronny.de.winter @Reiner . Period is actually the partition of time series and I woud like to put every event to each period then to find the co-evolving event (sub-option of my another post Relation extraction in time hierarchy which no reply on it). For this issue, now I found it could be solved without if condition (case when). But the logic here I still wish a small tutorial from you professiona about how to use case when clause in such situation. Thanks

oli

[ameyasoft]

Try this:
match (c:Car {carID:car_ID})-[:takePlace]->(e:Event)
with collect(e) as evts 
unwind evts as evt
match (p1:Period) where p1.start_time <= toInteger(evt.start_time) <= p1.end_time
match (p2:Period) where p2.start_time <= toInteger(evt.end_time) <= p2.end_time
match (p:Period) where p1.start_time <= p.start_time and p.end_time <=p2.end_time

with evt, p1, p2, p

FOREACH(ignoreMe IN CASE WHEN p1 = p2 pTHEN [1] ELSE [] END|
			
		create (evt)-[:onPeriod]->(p1)			
)

FOREACH(ignoreMe IN CASE WHEN p1 <> p2 pTHEN [1] ELSE [] END|
			
		create (evt)-[:onPeriod]->(p)			
)

[ronny_de_winter]

I can recommend the Graph Data Modeling for neo4j course. More specifically the part on Common Graph Structures has a section on "Timeline Trees" that could be of help.

[Bennu]

Hi @oli ,

Maybe

match(c:Car {carID:car_ID})-[:takePlace]->(e:Event)
with collect(e) as evts 
unwind evts as evt
match (p1:Period) 
WHERE p1.start_time <= toInteger(evt.start_time) <= p1.end_time
MATCH (p2:Period) 
WHERE p2.start_time <= toInteger(evt.end_time) <= p2.end_time
CALL apoc.when(
  p1 = p2,
  'create (evt)-[r:onPeriod]->(p1) return r as newRelation',
  'match (p:Period) where p1.start_time <= p.start_time and p.end_time <=p2.end_time
   create (evt)-[r:onPeriod]->(p) return r as newRelation',
  {p1:p1, p2:p2, evt:evt})
yield value
return value.newRelation as result