Optimizing nested connectionWhere queries

[Andy2003]

Given the following schema:

type Movie {
    title: String!
    actors: [Actor!]! @relationship(type: "ACTED_IN", properties: "ActedIn", direction: IN)
}

type Actor {
    name: String!
    movies: [Movie!]! @relationship(type: "ACTED_IN", properties: "ActedIn", direction: OUT)
}

interface ActedIn {
    screenTime: Int!
}

and the request:

query {
    movies(where: { actorsConnection_SOME: { node: { name: "Hugo Weaving", moviesConnection_NONE: { node : { title: "No" }  }} }  }) {
        title
    }
}

The Following cypher is generated:

MATCH (this:Movie)
  WHERE EXISTS((this)<-[:ACTED_IN]-(:Actor))
  AND ANY(this_actorsConnection_SOME_Actor_map IN [(this)<-[this_actorsConnection_SOME_Actor_MovieActorsRelationship:ACTED_IN]-(this_actorsConnection_SOME_Actor:Actor)  | {
    node: this_actorsConnection_SOME_Actor,
    relationship: this_actorsConnection_SOME_Actor_MovieActorsRelationship
  }]
    WHERE this_actorsConnection_SOME_Actor_map.node.name = $this_movies.where.actorsConnection_SOME.node.name
    AND apoc.cypher.runFirstColumn("RETURN EXISTS((this_actorsConnection_SOME_Actor_map_node)-[:ACTED_IN]->(:Movie)) AND NONE(this_actorsConnection_SOME_Actor_map_node_Movie_map IN [(this_actorsConnection_SOME_Actor_map_node)-[this_actorsConnection_SOME_Actor_map_node_Movie_ActorMoviesRelationship:ACTED_IN]->(this_actorsConnection_SOME_Actor_map_node_Movie:Movie) | { node: this_actorsConnection_SOME_Actor_map_node_Movie, relationship: this_actorsConnection_SOME_Actor_map_node_Movie_ActorMoviesRelationship } ] WHERE this_actorsConnection_SOME_Actor_map_node_Movie_map.node.title = $this_movies.where.actorsConnection_SOME.node.moviesConnection.node.title)", { this_actorsConnection_SOME_Actor_map_node: this_actorsConnection_SOME_Actor_map.node, this_movies: $this_movies }))
RETURN this { .title } as this

In general for each level of the connections where, a pattern comprehension like this is used:

...
WHERE ANY(map IN [(this)<-[r:ACTED_IN]-(nestedNode:Actor)  | { node: nestedNode, relationship: r }] WHERE map.node.name = $param)
...

This can be simplified by moving the where condition into the pattern comprehension like:

...
WHERE ANY(cond IN [(this)<-[r:ACTED_IN]-(nestedNode:Actor)  | node.name = $param] WHERE cond)
...

Now we can use nested predicates without the call to apoc.cypher.runFirstColumn:

MATCH (this:Movie)
  WHERE EXISTS((this)<-[:ACTED_IN]-(:Actor))
  AND ANY(cond IN [(this)<-[this_actorsConnection_SOME_Actor_MovieActorsRelationship:ACTED_IN]-(this_actorsConnection_SOME_Actor:Actor)  |
      this_actorsConnection_SOME_Actor.name = $this_movies.where.actorsConnection_SOME.node.name
      AND EXISTS((this_actorsConnection_SOME_Actor)-[:ACTED_IN]->(:Movie))
      AND NONE(cond IN [(this_actorsConnection_SOME_Actor)-[this_actorsConnection_SOME_Actor_map_node_Movie_ActorMoviesRelationship:ACTED_IN]->(this_actorsConnection_SOME_Actor_map_node_Movie:Movie) |
        this_actorsConnection_SOME_Actor_map_node_Movie.title = $this_movies.where.actorsConnection_SOME.node.moviesConnection.node.title
      ] where cond)
    ] WHERE cond)
RETURN this { .title } as this;

[angrykoala]

This problems has been partially solved, with the currently generated cypher being simpler and more performant, not using runFirstColumn anymore. There is still further work to be done by extracting this out of a list comprehension and into a subquery.

For reference, this is the currently generated cypher for the query mentioned above:

MATCH (this:`Movie`)
WHERE size([(this1:`Actor`)-[this0:ACTED_IN]->(this) WHERE (this1.name = $param0 AND size([(this1)-[this2:ACTED_IN]->(this3:`Movie`) WHERE this3.title = $param1 | 1]) = 0) | 1]) > 0
RETURN this { .title } as this

[darrellwarde]

This Cypher has changed so much now, and we have performance benchmarks to keep an eye on this kind of problem.