Project cypher fields before sorting on nested connections

[litewarp]

Same as #1364 but for nested connections. If you try and sort on the connection, the sort happens before the cypher field is projected, so the sort is not applied.

Cypher:
MATCH (this:Organization)
WHERE this.id = $this_affinityId
CALL {
WITH this
MATCH (this)<-[this_is_ltf_relationship:IS_LTF]-(this_legaltechopportunity:LegaltechOpportunity)
CALL apoc.util.validate(NOT(ANY(r IN ["ADMIN"] WHERE ANY(rr IN $auth.roles WHERE r = rr))), "@neo4j/graphql/FORBIDDEN", [0])
WITH this_is_ltf_relationship, this_legaltechopportunity
##  <--- The sort is applied on companyName 
ORDER BY this_legaltechopportunity.companyName DESC
## <--- But the companyName is not projected until after the sort is applied
WITH collect({ , node: { companyName:  apoc.cypher.runFirstColumn("MATCH (this)-[:IS_LTF_PORTFOLIO_COMPANY]->(org:Organization)
RETURN org.name", {this: this_legaltechopportunity, auth: $auth}, false), company: head([ (this_legaltechopportunity)-[:IS_LTF_PORTFOLIO_COMPANY]->(this_legaltechopportunity_company:Organization)   | this_legaltechopportunity_company { affinityId: this_legaltechopportunity_company.id, .name, .domain, .logoUrl, .shortUrl } ]), dbId: this_legaltechopportunity.id } }) AS edges
WITH size(edges) AS totalCount, edges[..10000000] AS limitedSelection
RETURN { edges: limitedSelection, totalCount: totalCount } AS ltfInvestmentsConnection
}
RETURN this { ltfInvestmentsConnection, affinityId: this.id } as this

The fix is to apply the same logic used for the rootConnections in #1365 to nested connections as well.

[neo4j-team-graphql]

Many thanks for raising this bug report @litewarp. :bug: We will now attempt to reproduce the bug based on the steps you have provided.

Please ensure that you've provided the necessary information for a minimal reproduction, including but not limited to:

• Type definitions
• Resolvers
• Query and/or Mutation (or multiple) needed to reproduce

If you have a support agreement with Neo4j, please link this GitHub issue to a new or existing Zendesk ticket.

Thanks again! :pray:

[neo4j-team-graphql]

We've been able to confirm this bug using the steps to reproduce that you provided - many thanks @litewarp! :pray: We will now prioritise the bug and address it appropriately.

[neo4j-team-graphql]

This bug report has been assigned high priority to fix. If you wish to contribute a fix, please branch from master and submit your PR with the base set to master. Thanks!

[angrykoala]

For reference, this issue can be confirmed with the following:

Typedefs.

type Movie {
    title: String!
    released:Int
    actors: [Person!]! @relationship(type: "ACTED_IN", direction: IN)
    actorsCount: Int! @cypher(statement: """
    MATCH (this)<-[:ACTED_IN]-(ac:Person)
    RETURN count(ac)
    """)
}

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

Query:

query Actors {
    people {
        moviesConnection(first: 20, sort: [{node: {actorsCount: DESC}}]) {
            edges {
            node {
                title
                actorsCount
            }
            }
        }
    }
}

And the resulting Cypher:

MATCH (this:Person)
CALL {
WITH this
MATCH (this)-[this_acted_in_relationship:ACTED_IN]->(this_movie:Movie)
WITH this_acted_in_relationship, this_movie
ORDER BY this_movie.actorsCount DESC
WITH collect({ node: { title: this_movie.title, actorsCount:  apoc.cypher.runFirstColumn("MATCH (this)<-[:ACTED_IN]-(ac:Person)
RETURN count(ac)", {this: this_movie, auth: $auth}, false) } }) AS edges
WITH size(edges) AS totalCount, edges[..20] AS limitedSelection
RETURN { edges: limitedSelection, totalCount: totalCount } AS moviesConnection
}
RETURN this { moviesConnection } as this

The problem in this cypher is as described above