Spring Data Neo4j-ORDER BY {order}失败 [英] 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>
对于order参数，我使用以下enum及其唯一方法:
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"
}
}
}
似乎SDN无法正确处理{order}参数?执行后，我得到一个异常提示
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}"
^
如果我从Cypher语句中删除该参数或将其替换为硬编码的DESC，则该方法成功.我相信这不是因为enum，因为我在其他存储库方法中使用了(其他)枚举，并且所有这些方法都成功了.我已经尝试过像sortOrder这样的其他参数命名，但这无济于事.
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.
我在这里想念什么?
推荐答案
这是更改排序和分页信息的错误模型.您可以跳到下面的答案以使用这些选项，或者继续阅读以了解代码本身存在问题的原因.
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.
您不能将参数绑定到未为参数绑定"设置的查询的语法元素中.参数绑定不会执行简单的字符串替换(因为您可能会遭受注入攻击)，而是使用绑定API来绑定参数.您正在像对待查询注释一样执行字符串替换，而事实并非如此.
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.
Neo4J的参数绑定文档和查询参数的Java手册准确显示了您在哪里可以绑定，允许的唯一位置是
The parameter binding docs for Neo4J and the Java manual for Query Parameters show exactly where you can bind, the only places allowed are:
- 代替字符串文字
- 代替正则表达式
- 字符串模式匹配
- 使用属性作为属性创建节点
- 创建具有属性的多个节点，作为属性
- 设置节点的所有属性
- 跳过和限制的数字值
- 作为节点ID
- 作为多个节点ID
- 索引值
- 索引查询
- 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
在ORDER BY子句中，没有什么可以说出您正在尝试的内容.
There is nothing that says what you are trying is allowed, binding in the ORDER BY clause.
这并不是说Spring Data的作者无法解决此问题，并不允许在其他地方进行绑定，但是看来他们所做的工作并没有Neo4J Java API所允许的更多.
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.
(针对此问题的修复程序已标记为版本4.2.0.M1，该版本为2016年9月8日的预发布版本，有关使用里程碑版本的信息，请参见下文)
(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);
或者您可以使用PageRequest类/Pageable接口:
(针对此问题的修复程序已标记为版本4.2.0.M1，该版本为2016年9月8日的预发布版本，有关使用里程碑版本的信息，请参见下文)
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)
在当前的 Spring Data + Neo4j文档您会看到使用分页的示例:
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)
此 PageRequest类也允许对参数进行排序.任何实现 Pageable 将执行相同的操作.相反，使用Pageable可能更合适:
@Query("MATCH (movie:Movie {title={0}})<-[:ACTS_IN]-(actor) RETURN actor")
Page<Actor> getActorsThatActInMovieFromTitle(String movieTitle, Pageable page);
您可能可以在早期版本中使用SpEL:
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:
由于该机制还公开了特殊参数类型，例如
Sort或Pageable，因此我们现在可以在本机查询中使用分页.
Since this mechanism exposes special parameter types like
SortorPageableas well, we’re now able to use pagination in native queries.
但是官方文档似乎说它更加有限.
But the official docs seem to say it is more limited.
有人在 GitHub上报告您完全相同的问题问题.然后导致 DATAGRAPH-653 问题，该问题在版本4.2.0.M1中被标记为已修复.这里引用了其他过时的SO问题，因此您应该忽略 Spring Data Neo4j 4中的分页和排序已不再正确.
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.
您可以在以下任何位置查看任何版本的依赖项信息项目页面.对于4.2.0.M1构建，Gradle的信息(您可以推断Maven)为:
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.
这篇关于Spring Data Neo4j-ORDER BY {order}失败的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
