Temporal relationship properties

cypher
(Mikehancock72) #1

I am trying to solve a problem where I want to find all the combinations based on temporal relationships.

If I have graphed the following scenario:

A Person commutes to their job
by Car from 4/1 - 7/15
by bike from 7/16 - 8/2

Person has Job1 from 4/1 - 5/15
Person has Job2 from 5/16 - 7/2
Person has Job3 from 7/3 - 8/2

Person Lives at Residence1 4/1 - 5/1
Person Lives at Residence2 5/2 - 8/2

We can view the graph

image
image.jpg826x670 65.9 KB

With a simple where clause, I can see the combination for any given date

match (p:Person{firstName:'Mike'})-[r]-(m)
where r.startDate < date('2018-05-30') < r.endDate
return p,r,m

image
image.jpg730x518 30.3 KB

I want to find the complete list of different combinations so that I could determine that

For example:
4/1 - 5/1 - Car, Residence1, Job1
5/2 - 5/15 - Car, Residence2, Job1
5/16 - 7/2 Car, Residence2, Job2
7/3 - 7/15 Car, Residence2, Job3
...

My first approach would be to loop through each day and determine if the nodes that matched were the same as the previous day but am looking for any suggestions on how I could do this.

(Mike R Black) #2

What do you want to happen if there is no data on given date, do you want still have a row returned for that data with nulls? If that is the case, you will want to generate a list of dates then "join" to that list to return back the modes of transportation used on that day.

If you're ok with a sparse time line, then you don't need to filter on the date, but you'll want to "group by/order by" the date in the relationships. Then collect the nodes found on each given day.

If this was SQL it would look similar to

SELECT date, LISTAGG(node_data, ',') AS aggregation
FROM table
GROUP BY date
(Mikehancock72) #3

i am fine with a sparse timeline. Similar to what you are saying, I collect all of the dates

match (p:Person{firstName:'Mike'})-[r]-(m)
return  collect (distinct r.startDate)

and

match (p:Person{firstName:'Mike'})-[r]-(m)
return  collect (distinct r.endDate+duration({days:1}))

those dates then signify all of the timeline "levels".

Then, for each of those levels, I can find the relevant nodes by checking one date that is in each of the levels.

match (p:Person{firstName:'Mike'})-[r]-(m)
where r.startDate < date('2018-07-16') < r.endDate
return p,r,m