Skip to content

Apoc runFirstColumn usage

Jump to bottom
angrykoala edited this page Jan 30, 2023 · 3 revisions

This page documents current usage of apoc and any known alternative

Location Procedure File Notes
Cypher Projection runFirstColumn src/translate/create-projection-and-params.ts Solved in #2769
Cypher Resolver runFirstColumn src/schema/resolvers/field/cypher.ts Solved in #2769
Aggregate Where runFirstColumn src/translate/create-aggregate-where-and-params.ts Solved
String aggregations runFirstColumn src/translate/translate-aggregate.ts Solved in #1679
Field Aggregations Count runFirstColumn src/translate/field-aggregations/create-field-aggregation.ts Solved
Field Aggregations Projection runFirstColumn src/translate/field-aggregations/create-field-aggregation.ts Solved in #1810
Connection Projection runFirstColumn src/translate/create-projection-and-params.ts Fixed in #2053
Query Points runFirstColumn src/translate/projection/elements/create-point-element.ts Fixed in #1926
Create Element Where runFirstColumn src/translate/where/create-element-where-and-params.ts Fixed in #1770 and #2030

Run First Column

apoc.runFirstColumn is widely used as a means to run independent sub queries. This solution is not ideal in most cases due to the following issues:

  • Hard to maintain and compose queries
  • Bad performance due to secondary processes being spawned for the queries
  • apoc methods cannot be properly planned by Neo4j database, leading to worse performance.

Cypher Projection

Used for @cypher directive.

Example

Schema

type Movie {
  id: ID
  title: String
  topActor: Actor
    @cypher(
      statement: """
          MATCH (a:Person)
          RETURN a
      """
    )
}

type Actor {
  name: String
}

Query

{
  movies {
    title
    topActor {
      name
    }
  }
}

Cypher

MATCH (this:Movie)
RETURN this { 
    .title, 
    topActor: head([this_topActor IN apoc.cypher.runFirstColumn("MATCH (a:Person)
            RETURN a", {this: this}, false) | this_topActor { .name }]) 
} as this

Proposed Cypher

(Will not work)

MATCH(m:Movie)
CALL {
    WITH m
    WITH m as this
    MATCH (a:Person)
    RETURN a
}
WITH m, collect(a) as projection
RETURN m {.title, topActor: head([this_topActor IN projection | this_topActor { .name }]) }

Considerations

  • Return multiple items in an array vs a single by using head in the projection.
  • We need to keep track of subqueries for projection.

Cypher Resolver

Schema

type Person {
    actorId: ID!
    name: String
}

type Query {
    allPeople: [Person]
        @cypher(
            statement: """
            MATCH (a:Person)
            RETURN a
            """
        )
}

Query

{
	allPeople {
	    name
	}
}

Cypher

WITH apoc.cypher.runFirstColumn("
	MATCH (a:Person)
	RETURN a
", {}, true) as x
UNWIND x as this
WITH this
RETURN this { .name } AS this

Proposed Cypher

TODO

Considerations

  • Some unexpected behaviour may happen when returning lists due to the extra UNWIND.
  • Returned values from user may have multiple names

Aggregate Where

Use for aggregation where (e.g. count)

Example

Schema

type User {
	name: String!
}

type Post {
	content: String!
	likes: [User!]! @relationship(type: "LIKES", direction: IN)
}

Query

{
	posts(where: { likesAggregate: { count: 10 } }) {
		content
	}
}

Cypher

MATCH (this:Post)
WHERE apoc.cypher.runFirstColumn(" MATCH (this)<-[this_likesAggregate_edge:LIKES]-(this_likesAggregate_node:User)
RETURN count(this_likesAggregate_node) = $this_likesAggregate_count
", { this: this, this_likesAggregate_count: $this_likesAggregate_count }, false )
RETURN this { .content } as this

Proposed Cypher

MATCH(this:Post)
CALL {
    WITH this
    MATCH(this)<-[:LIKES]-(u:User)
    RETURN count(u) as agg_count
}
WITH *
WHERE agg_count=10
RETURN m {.title} as this

Considerations

  • Keep track of subqueries for where statement.

String aggregations

Aggregations use first column for string returns in aggregation

Example

Schema

type Movie {
    title: String!
}

Query

{
moviesAggregate {
    title {
	shortest
        }
    }
}

Cypher

MATCH (this:Movie)
RETURN { title: { shortest:
reduce(shortest = collect(this.title)[0], current IN collect(this.title) | apoc.cypher.runFirstColumn("
	RETURN
	CASE
	WHEN size(current) < size(shortest) THEN current
	ELSE shortest
	END AS result
", { current: current, shortest: shortest }, false))
  }
}

Proposed Cypher

MATCH (this:Movie)
RETURN { title: { shortest:
reduce(shortest = collect(this.title)[0], current IN collect(this.title) | 
	CASE
	WHEN size(current) < size(shortest) THEN current
	ELSE shortest
	END
	)
}} as this

Field Aggregations Count

Field aggregations use runInColumn for count values

Example

Schema

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

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

Query

query {
	movies {
		title
		actorsAggregate {
			count
		}
	}
}

Cypher

MATCH (this:Movie)
RETURN this { .title, 
	actorsAggregate: { 
		count: head(apoc.cypher.runFirstColumn("MATCH (this)<-[r:ACTED_IN]-(n:Person)      RETURN COUNT(n)", { this: this })) 
		} 
	} as this

Proposed Cypher

MATCH (this:Movie)
CALL {
    WITH this
    MATCH (this)<-[r:ACTED_IN]-(n:Person)
    RETURN COUNT(n) as person_count
}
WITH *
RETURN this { .title, 
	actorsAggregate: { 
		count: person_count
		} 
} as this

Field Aggregations Projection

Field aggregation subquery is projected through runFirstColumn

Example

Schema

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

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

Query

query {
  movies {
    actorsAggregate {
      node {
        age {
          min
          max
          average
          sum
        }
      }
    }
  }
}

Cypher

MATCH (this:Movie)
RETURN this { actorsAggregate: { node: { age: head(apoc.cypher.runFirstColumn("MATCH (this)<-[r:ACTED_IN]-(n:Person)
    RETURN {min: min(n.age), max: max(n.age), average: avg(n.age), sum: sum(n.age)}", { this: this })) } } } as this

Proposed Cypher

MATCH (this:Movie)
CALL {
    WITH this
	MATCH(this)<-[r:ACTED_IN]-(n:Person)
	RETURN {min: min(n.age), max: max(n.age), average: avg(n.age), sum: sum(n.age)} AS agg_data
}
WITH *
RETURN this { actorsAggregate: { node: agg_data } } as this

Connection Projection

Example

Schema

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

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

Query

mutation {
  createActors(
    input: {
      name: "Dan"
      movies: { connect: { where: { node: { title: "The Matrix" } } } }
    }
  ) {
    actors {
      name
      movies {
        actorsConnection(where: { node: { name: "Dan" } }) {
          totalCount
          edges {
            node {
              name
            }
          }
        }
      }
    }
  }
}

Cypher

CALL {
CREATE (this0:Actor)
SET this0.name = "Dan"
WITH this0
CALL {
	WITH this0
	OPTIONAL MATCH (this0_movies_connect0_node:Movie)
	WHERE this0_movies_connect0_node.title = "The Matrix"
	FOREACH(_ IN CASE WHEN this0 IS NULL THEN [] ELSE [1] END | 
		FOREACH(_ IN CASE WHEN this0_movies_connect0_node IS NULL THEN [] ELSE [1] END | 
			MERGE (this0)-[:ACTED_IN]->(this0_movies_connect0_node)
		)
	)
	RETURN count(*) AS _
}
RETURN this0
}
RETURN [
this0 { .name, movies: [ (this0)-[:ACTED_IN]->(this0_movies:Movie)   | this0_movies { actorsConnection: apoc.cypher.runFirstColumn("CALL {
WITH this0_movies
MATCH (this0_movies)<-[this0_movies_acted_in_relationship:ACTED_IN]-(this0_movies_actor:Actor)
WHERE this0_movies_actor.name = \"Dan\"
WITH collect({ node: { name: this0_movies_actor.name } }) AS edges
UNWIND edges as edge
RETURN { edges: collect(edge), totalCount: size(edges) } AS actorsConnection
} RETURN actorsConnection", { this0_movies: this0_movies }, false) } ] }] AS data

Proposed Cypher

CALL {
	CREATE (this0:Actor)
	SET this0.name = "Dan"
	WITH this0
	CALL {
		WITH this0
		OPTIONAL MATCH (this0_movies_connect0_node:Movie)
		WHERE this0_movies_connect0_node.title = "The Matrix"
		FOREACH(_ IN CASE WHEN this0 IS NULL THEN [] ELSE [1] END | 
			FOREACH(_ IN CASE WHEN this0_movies_connect0_node IS NULL THEN [] ELSE [1] END | 
				MERGE (this0)-[:ACTED_IN]->(this0_movies_connect0_node)
			)
		)
		RETURN count(*) AS _
	}
	RETURN this0
}
CALL {
	WITH this0
	MATCH (this0)-[:ACTED_IN]->(this0_movies:Movie)
	WITH this0_movies
	MATCH (this0_movies)<-[this0_movies_acted_in_relationship:ACTED_IN]-(this0_movies_actor:Actor)
	WHERE this0_movies_actor.name = "Dan"
	WITH collect({ node: { name: this0_movies_actor.name } }) AS edges
	UNWIND edges as edge
	RETURN { edges: collect(edge), totalCount: size(edges) } AS actorsConnection
}
												
RETURN [
this0 { .name, movies: [{actorsConnection:  actorsConnection }] AS data

Considerations

  • Keep track of projection results from subqueries
  • Doing relationship projections outside of RETURN clause is a prerequisite for this work

Query Points

Example

Schema

type PointContainer {
  point: Point
}

Query

{
  pointContainers(where: { point: { longitude: 1.0, latitude: 2.0 } }) {
    point {
      longitude
      latitude
      crs
    }
  }
}

Cypher

MATCH (this:PointContainer)
WHERE this.point = point($this_point)
RETURN this { point: apoc.cypher.runFirstColumn('RETURN
            CASE
            	WHEN this.point IS NOT NULL THEN { point: this.point, crs: this.point.crs }
            	ELSE NULL
            END AS result',{ this: this },false) 
} as this

Proposed Cypher

MATCH(this:PointContainer)
WITH this,
CASE
  WHEN this.point IS NOT NULL THEN {point: this.point, crs: this.point.crs}
  ELSE NULL
END AS this_point
RETURN this {.name, point: this_point} as this

Considerations

  • We need to keep track of subqueries for projection.

Create Element Where

Example

Schema

interface Production {
    id: ID
    title: String
}
type Movie implements Production {
    id: ID
    title: String
    actorCount: Int
    genres: [Genre!]! @relationship(type: "IN_GENRE", direction: OUT)
}

type Genre {
  name: String
  movies: [Production!]! @relationship(type: "IN_GENRE", direction: IN)
}

Query

query Genres {
  genres {
    moviesConnection(where: {
    "node": {
      "_on": {
        "Movie": {
          "genresConnection_ALL": {
            "node": {
              "name": "Sci-fi"
            }
          }
        }
      }
    }
  }) {
      totalCount
    }
  }
}

Cypher

MATCH (this:Genre)
CALL {
    WITH this
    CALL {
        WITH this
        MATCH (this)<-[this_in_genre_relationship:IN_GENRE]-(this_Movie:Movie)
        WHERE apoc.cypher.runFirstColumn("RETURN exists((this_Movie)-[:IN_GENRE]->(:Genre))
        AND all(this_Movie_Genre_map IN [(this_Movie)-[this_Movie_Genre_MovieGenresRelationship:IN_GENRE]->(this_Movie_Genre:Genre) | { node: this_Movie_Genre, relationship: this_Movie_Genre_MovieGenresRelationship } ] WHERE 
        this_Movie_Genre_map.node.name = $this_moviesConnection.args.where.node._on.Movie.genresConnection.node.name
        )", { this_Movie: this_Movie, this_moviesConnection: $this_moviesConnection })
        WITH { node: { __resolveType: "Movie" } } AS edge
        RETURN edge
    }
    WITH collect(edge) as edges
    RETURN { totalCount: size(edges) } AS moviesConnection
}
RETURN this { moviesConnection } as this

Proposed Cypher

MATCH (this:Genre)
CALL {
    WITH this
    CALL {
        WITH this
        MATCH (this)<-[this_in_genre_relationship:IN_GENRE]-(this_Movie:Movie)
        WHERE exists((this_Movie)-[:IN_GENRE]->(:Genre)) and 
            all(x IN 
                [
                    (this_Movie)-[this_Movie_Genre_MovieGenresRelationship:IN_GENRE]->(this_Movie_Genre:Genre) 
                    | 
                    { node: this_Movie_Genre, relationship: this_Movie_Genre_MovieGenresRelationship } 
                ] WHERE x.node.name = $this_moviesConnection.name
            )
        WITH { node: { __resolveType: "Movie" } } AS edge
        RETURN edge
    }
    WITH collect(edge) as edges
    RETURN { totalCount: size(edges) } AS moviesConnection
}
RETURN this { moviesConnection } as this

Considerations

  • There original query generated contains a bug over the parameter, the parameter object hold the property genresConnection_ALL while the query is looking for the property genresConnection
Clone this wiki locally