Search code examples
spring-datacypherkotlinspring-data-neo4j-4

Spring Data Neo4j - ORDER BY {order} fails

I have a query where the result should be ordered depending on the passed parameter:

@Query("""MATCH (u:User {userId:{uid}})-[:KNOWS]-(:User)-[h:HAS_STUFF]->(s:Stuff)
    WITH s, count(h) as count ORDER BY count {order}
    RETURN o, count SKIP {skip} LIMIT {limit}""")
        fun findFromOthersByUserIdAndSortByAmountOfStuff(
         @Param("uid") userId: String,
         @Param("skip") skip: Int,
         @Param("limit") limit: Int,
         @Param("order) order: String): List<StuffWithCountResult>

For the order parameter I use the following enum and its sole method:

enum class SortOrder {
    ASC,
    DESC;
    fun toNeo4JSortOrder(): String {
        when(this) {
            ASC -> return ""
            DESC -> return "DESC"
        }
    } 
 }

It seems that SDN does not handle the {order} parameter properly? On execution, I get an exception telling that

 Caused by: org.neo4j.kernel.impl.query.QueryExecutionKernelException: Invalid input 'R': expected whitespace, comment or a relationship pattern (line 3, column 5 (offset: 244))
 "    RETURN o, count SKIP {skip} LIMIT {limit}"
      ^

If I remove the parameter from the Cypher statement or replace it with a hardcoded DESC the method succeeds. I believe it's not because of the enum since I use (other) enums in other repository methods and all these methods succeed. I already tried a different parameter naming like sortOrder, but this did not help.

What am I missing here?

Solution

  • This is the wrong model for changing sorting and paging information. You can skip to the answer below for using those options, or continue reading for an explanation of what is wrong in your code as it stands.

    You cannot bind where things aren't allowed to be bound:

    You cannot bind a parameter into a syntax element of the query that is not setup for "parameter binding". Parameter binding doesn't do simple string substitutions (because you would be open for injection attacks) but rather uses binding API's to bind parameters. You are treating the query annotation like it is performing string substitution instead, and that is not what is happening.

    The parameter binding docs for Neo4J and the Java manual for Query Parameters show exactly where you can bind, the only places allowed are:

    • in place of String Literals
    • in place of Regular Expressions
    • String Pattern Matching
    • Create node with properties, as the properties
    • Create multiple nodes with properties, as the properties
    • Setting all properties of a node
    • numeric values for SKIP and LIMIT
    • as the Node ID
    • as multiple Node IDs
    • Index Value
    • Index Query

    There is nothing that says what you are trying is allowed, binding in the ORDER BY clause.

    That isn't to say that the authors of Spring Data couldn't work around this and allow binding in other places, but it doesn't appear they have done more than what Neo4J Java API allows.

    You can instead use the Sort class:

    (the fix to allow this is marked for version 4.2.0.M1 which is a pre-release as of Sept 8, 2016, see below for using milestone builds)

    Spring Data has a Sort class, if your @Query annotated method has a parameter of this type, it should apply sorting and allow that to dynamically modify the query.

    I assume the code would look something like (untested):

    @Query("MATCH (movie:Movie {title={0}})<-[:ACTS_IN]-(actor) RETURN actor")
List<Actor> getActorsThatActInMovieFromTitle(String movieTitle, Sort sort);

    Or you can use the PageRequest class / Pageable interface:

    (the fix to allow this is marked for version 4.2.0.M1 which is a pre-release as of Sept 8, 2016, see below for using milestone builds)

    In current Spring Data + Neo4j docs you see examples using paging:

    @Query("MATCH (movie:Movie {title={0}})<-[:ACTS_IN]-(actor) RETURN actor")
Page<Actor> getActorsThatActInMovieFromTitle(String movieTitle, PageRequest page);

    (sample from Cypher Examples in the Spring Data + Neo4j docs)

    And this PageRequest class also allows sorting parameterization. Anything that implements Pageable will do the same. Using Pageable instead is probably more proper:

    @Query("MATCH (movie:Movie {title={0}})<-[:ACTS_IN]-(actor) RETURN actor")
Page<Actor> getActorsThatActInMovieFromTitle(String movieTitle, Pageable page);

    You might be able to use SpEL in earlier versions:

    As an alternative, you can look at using SpEL expressions to do substitutions in other areas of the query. I am not familiar with it but it says:

    Since this mechanism exposes special parameter types like Sort or Pageable as well, we’re now able to use pagination in native queries.

    But the official docs seem to say it is more limited.

    And you should know this other information:

    Here is someone reporting your exact same problem in a GitHub issue. Which then leads to DATAGRAPH-653 issue which was marked as fixed in version 4.2.0.M1. This references other SO questions here which are outdated so you should ignore those like Paging and sorting in Spring Data Neo4j 4 which are no longer correct.

    Finding Spring Data Neo4j Milestone Builds:

    You can view the dependencies information for any release on the project page. And for the 4.2.0.M1 build the information for Gradle (you can infer Maven) is:

    dependencies {
    compile 'org.springframework.data:spring-data-neo4j:4.2.0.M1'
}

repositories {
    maven {
        url 'https://repo.spring.io/libs-milestone'
    }
}

    Any newer final release should be used instead.