CODEWITHSUNDEEP
neo4jcypher
JasonG
JasonG

Reputation: 922

In cypher, return only the nodes with most recent relationship

Related to this other question, but approaching from the other side of the relationship.

Here is the scenario. I am modeling a person that lives or has lived at one or more different locations. Included in the relationship is the start date (represented as ms since epoch) when they moved in.

    (:Person{name:'bill'}) -[:livesAt {since:1111000}]->(:Place{name:'apartmentA'})    
    (:Person{name:'bill'}) -[:livesAt {since:2222000}]->(:Place{name:'apartmentB'})
    (:Person{name:'john'}) -[:livesAt {since:3333000}]->(:Place{name:'apartmentA'})
    (:Person{name:'chris'}) -[:livesAt {since:1100000}]->(:Place{name:'apartmentC'})
    (:Person{name:'chris'}) -[:livesAt {since:1122000}]->(:Place{name:'apartmentA'})

I want to write a query that returns the person nodes that are still living at a given location. They are still living at a location if the livesAt relationship has the largest "since" of all relationships from that person.

I was trying something like this:

MATCH (:Place {name: 'apartmentA'})<-[r]-(p:Person)
WITH max(r.since) as most_recent, p.name as pname
MATCH (t:Person {name:pname}) -[e]->(l:Place)
WITH t,l
ORDER BY e.since DESC
return t,l

If my query worked with the example above, given place 'apartmentA' I would expect to get john and chris.

Upvotes: 0

Views: 250

Answers (1)

InverseFalcon
InverseFalcon

Reputation: 30397

To find what you want, you have to make sure that you filter to people who don't have a :livesAt relationship with a larger since property (indicating that they now live elsewhere) to a different location. That's important because it's possible that they lived at that location, moved elsewhere, then moved back later.

We can use existential subqueries from Neo4j 4.x to give us finer control for describing the pattern that we don't want to exist.

MATCH (loc:Place {name: 'apartmentA'})<-[r:livesAt]-(p:Person)
WITH loc, max(r.since) as most_recent, p
WHERE NOT EXISTS {
    MATCH (p) -[r:livesAt]->(other)
    WHERE r.since > most_recent AND other <> loc
}
RETURN p.name

You might also consider remodeling this, keeping a :currentResidence relationship to their current residence, updating that (deleting the old, creating the new) when they move. That's in addition to the :livesAt relationships you already have (I assume you use those for other queries). That lets you very quickly perform checks and matches based on current residence without needing to do any additional filtering at all.

EDIT:

If you don't want to use an existential subquery, we can use an OPTIONAL MATCH of the pattern instead, and only filter to the results where the other node is null, meaning that no such pattern exists:

MATCH (loc:Place {name: 'apartmentA'})<-[r:livesAt]-(p:Person)
WITH loc, max(r.since) as most_recent, p
OPTIONAL MATCH (p) -[r:livesAt]->(other)
WHERE r.since > most_recent AND other <> loc
WITH p, other
WHERE other IS NULL
RETURN p.name

Upvotes: 1

Related Questions