Search code examples
neo4jcyphergraph-databases

Filtering out optional matches or results in cypher query


I have a list of User emails and I need to get all of them with their Team membership. Both Users with Teams and those who are not members of any Team. I have a query for that which is:

  MATCH (u:User)
  WHERE (u.email in $userEmails)
  OPTIONAL MATCH (u)-[r:MEMBER_OF]-(t:Team)
  RETURN r, u, t;

The problem I have is that I would also want to exclude a list of Teams in my query. When I tried this:

  MATCH (u:User)
  WHERE (u.email in $userEmails)
  OPTIONAL MATCH (u)-[r:MEMBER_OF]-(t:Team)
  WHERE NOT (t.id in $excludedTeamIds)
  RETURN r, u, t;

The result was the same list of Users, but their relation to the Team was just missing. I would want to remove both - the User and the Team from the result.


Solution

  • You should filter the users and teams, using an additional WITH clause. Like this:

    MATCH (u:User)
    WHERE (u.email in $userEmails)
    OPTIONAL MATCH (u)-[r:MEMBER_OF]-(t:Team)
    WITH u, r, t WHERE t IS NULL OR NOT (t.id IN $excludedTeamIds)
    RETURN r, u, t;