neo4jcypherneo4j-apoc

Cypher query to find nodes where all relationships have a given property? ( deleted_at )

I choose to soft delete relationships on my graph, but I expect my nodes to have a single "active" relationship of a particular type. I ran into a scenario where some of my nodes ended up having all their relationships pick up a deleted_at property, and I ran into trouble isolating them for investigation.

Said more simply, I am looking for nodes that have a specific relationship, but ALL of them have a timestamp on deleted_at, and none of them are active (lack a deleted_at property)

I was able to solve my problem, but not in a way I am proud of:

MATCH (a:Actor)-[r:CURRENT_FILMING]->(m:Movie)
WITH collect(r.deleted_at) AS deleted, a, r
RETURN a.id, count(DISTINCT r) AS movie_count, size(collect(r.deleted_at)) AS deleted_count ORDER BY movie_count - deleted_count ASC;

For some reason, this query required me to keep collect(r.deleted_at) AS deleted even though I didn't use it to return all of the relationships, and I don't understand why.

I tried to use predicate functions to solve this, but it didn't give me what I was looking for:

MATCH (a:Actor)
MATCH (a)-[r:CURRENT_FILMING]->(m:Movie)
WITH collect(r) AS projects
WHERE none(x in projects WHERE x.deleted_at IS NULL)
RETURN *;

This also didn't work:

MATCH (a:Actor)-[r:CURRENT_FILMING]->(m:Movie)
WITH collect(r.deleted_at) AS deleted, collect(r.created_at) AS created, c, r
RETURN c, size(created) AS project_count, size(deleted) AS deleted_count ORDER BY (project_count - deleted_count) ASC;

I also ran into to trouble trying to collect the IDs of the actors, and ended up having to manually download the response as a CSV and copy the id column and text edit it into an array so I could then write a query to fix them.

MATCH (a:Actor)-[r:CURRENT_FILMING]->(m:Movie)
WHERE a.id IN ["actor_1", "actor_2"]
REMOVE r.deleted_at
RETURN *

I'd love to learn from this, and appreciate you reading this far.

Solution

  • To just see the ids of all the actors who have no undeleted relationships (including actors with no relationships):

    MATCH (a:Actor)
WHERE NOT EXISTS {
    MATCH (a)-[r:CURRENT_FILMING]->()
    WHERE r.deleted_at IS NOT NULL
}
RETURN a.id AS id