Filtering path with variable length multiple relationships (People You May Know query)

Question

So let's say we have User nodes, Company nodes, Project nodes, School nodes and Event nodes. And there are the following relationships between these nodes

• (User)-[:WORKED_AT {start: timestamp, end:timestamp}]->(Company)
• (User)-[:COLLABORATED_ON]->(Project)
• (Company)-[:COLLABORATED_ON]->(Project)
• (User)-[:IS_ATTENDING]->(Event)
• (User)-[:STUDIED_AT]->(School)

I am trying to recommend users to any given user. My starting query looks like this

MATCH p=(u:User {id: {leftId}})-[r:COLLABORATED_ON|:AUTHORED|:WORKED_AT|:IS_ATTENDING|:STUDIED_AT*1..3]-(pymk:User)
RETURN p
LIMIT 24

Now this returns me all the pymk users within 1 to 3 relationships away, which is fine. But I want to filter the path according to the relationship attributes. Like remove the following path if the user and pymk work start date and end date is not overlapping.

(User)-[:WORKED_AT]->(Company)<-[:WORKED_AT]-(User)

I can do this with single query

MATCH (u:User)-[r1:WORKED_AT]->(Company)<-[r2:WORKED_AT]-(pymk:User)
WHERE 
    (r1.startedAt <  r2.endedAt) AND (r2.startedAt < r1.endedAt)
RETURN pymk

But couldn't get my head around doing it within a collection of paths. I don't even know if this is possible.

Any help is appreciated.

Answer 1

This should do the trick:

MATCH p=(:User {id: {leftId}})-[:COLLABORATED_ON|:AUTHORED|:WORKED_AT|:IS_ATTENDING|:STUDIED_AT*1..3]-(:User)
WITH p, [rel in relationships(p) WHERE type(rel) = 'WORKED_AT'] as worked
WHERE size(worked) <> 2 OR 
  apoc.coll.max([work in worked | work.startedAt]) < apoc.coll.min([work in worked | work.endedAt])
RETURN p
LIMIT 24

We're using APOC here to get the max and min of a collection (the max() and min() aggregation functions in just Cypher are aggregation functions across rows, and can't be used on lists).

This relies on distilling the logic of overlapping down to max([start times]) < min([end times]), which you can check out in this highly popular answer here