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

Question

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.

cybersam · Accepted Answer · 2024-05-30 18:00:16Z

0

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

edited May 30, 2024 at 18:00

answered May 30, 2024 at 17:38

cybersam

67.3k6 gold badges59 silver badges81 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Sam Wynne Over a year ago

Thank you! Since I did not want to show "Actors" with no relationships I tweaked this a little bit, but this got me there.

Collectives™ on Stack Overflow

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

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related