英文:
Why ORDER BY in Java N1QL not working as intended
问题
以下是翻译好的内容:
当我在Couchbase Web UI中运行SELECT * FROM projects ORDER BY createdAt desc
查询时,结果按照应该的顺序进行了排序,但在Java中却没有。
Java代码
@Override
public List<Project> getAllProjects(String requestedOrder, Integer page, Integer pageOffset) {
String statement = "SELECT projects.* FROM projects ORDER BY $requestedOrder DESC OFFSET $offset LIMIT $pageOffset";
QueryOptions queryOptions = queryOptions().parameters(
JsonObject.create().put("offset", (page - 1) * pageOffset)
.put("pageOffset", pageOffset)
.put("requestedOrder", requestedOrder));
QueryResult queryResult = couchbaseCluster.query(statement, queryOptions);
return queryResult.rowsAs(Project.class);
}
Web UI结果
[
{
"projects": {
"createdAt": 1603804921950,
"name": "第三个项目",
...
}
},
{
"projects": {
"createdAt": 1603804915827,
"name": "第二个项目",
...
}
},
{
"projects": {
"createdAt": 1603804909410,
"name": "第一个项目"
...
}
}
]
Java SDK结果
[
{
"name": "第二个项目",
"createdAt": "2020-10-27T13:21:55.827+00:00",
...
},
{
"name": "第三个项目",
"createdAt": "2020-10-27T13:22:01.950+00:00",
...
},
{
"name": "第一个项目",
"createdAt": "2020-10-27T13:21:49.410+00:00",
...
}
]
当我将语句更改为以下内容时,它可以正确排序,但为什么使用put
方法无法正确排序。
String statement = "SELECT projects.* FROM projects ORDER BY " + requestedOrder + " DESC OFFSET $offset LIMIT $pageOffset";
英文:
When I run SELECT * FROM projects ORDER BY createdAt desc
this query in Couchbase Web UI result ordered by as it should be but not in java.
Java Code
@Override
public List<Project> getAllProjects(String requestedOrder, Integer page, Integer pageOffset) {
String statement = "SELECT projects.* FROM projects ORDER BY $requestedOrder DESC OFFSET $offset LIMIT $pageOffset";
QueryOptions queryOptions = queryOptions().parameters(
JsonObject.create().put("offset", (page - 1) * pageOffset)
.put("pageOffset", pageOffset)
.put("requestedOrder", requestedOrder));
QueryResult queryResult = couchbaseCluster.query(statement, queryOptions);
return queryResult.rowsAs(Project.class);
}
Web UI result
[
{
"projects": {
"createdAt": 1603804921950,
"name": "Third Project",
...
}
},
{
"projects": {
"createdAt": 1603804915827,
"name": "Second Project",
...
}
},
{
"projects": {
"createdAt": 1603804909410,
"name": "First Project"
...
}
}
]
Java SDK Result
[
{
"name": "Second Project",
"createdAt": "2020-10-27T13:21:55.827+00:00",
...
},
{
"name": "Third Project",
"createdAt": "2020-10-27T13:22:01.950+00:00",
...
},
{
"name": "First Project",
"createdAt": "2020-10-27T13:21:49.410+00:00",
...
}
]
When i change my statement to this it orders correctly but why it's not ordering correctly using put
method.
String statement = "SELECT projects.* FROM projects ORDER BY " + requestedOrder + " DESC OFFSET $offset LIMIT $pageOffset";
答案1
得分: 6
String statement = "SELECT projects.* FROM projects ORDER BY $requestedOrder DESC OFFSET $offset LIMIT $pageOffset";
在上述查询中,您正在将ORDER BY表达式作为命名参数传递,这是一个值。该值将对查询中的所有文档保持恒定且相同。在相同值上进行排序是NOOP,即它可能不会对任何内容进行排序。
ORDER BY表达式必须取决于文档的字段。每次如果您需要不同的字段,则必须使用不同的查询。
注意:JSON没有模式,字段没有物理位置。ORDER BY数字将被视为常量,而某些关系数据库系统使用投影字段位置作为排序依据。
如果$requestedOrder不是嵌套字段,请尝试将查询字符串替换为以下内容。$requestedOrder必须求值为字符串,并且必须是文档的字段(不是嵌套字段)。例如,$requestedOrder = "createdAt",在排序执行期间会评估为p.createdAt(与ORDER BY p.createdAt相同)。
String statement = "SELECT p.* FROM projects AS p ORDER BY p.[$requestedOrder] DESC OFFSET $offset LIMIT $pageOffset";
英文:
String statement = "SELECT projects.* FROM projects ORDER BY $requestedOrder DESC OFFSET $offset LIMIT $pageOffset";
In above query you are passing The ORDER BY expressions as named parameter, which is value. The value will be constant and same for all the documents in query. Sort on same value is NOOP i.e. it may not sort anything.
ORDER BY expressions must depend on the field of the documents. Each time if you need different field you must use different queries.
NOTE: JSON is schema less and no physical position of field. ORDER BY number will treat as constant vs some RDBS database uses projection field position as sort.
If $requestedOrder is not a nested field try replace the query string with the following. $requestedOrder must evaluate to string and must be the field of the document (not nested). Example $requestedOrder = "createdAt" The During execution of sort evaluates p.createdAt (i.e same as ORDER BY p.createdAt)
String statement = "SELECT p.* FROM projects AS p ORDER BY p.[$requestedOrder] DESC OFFSET $offset LIMIT $pageOffset";
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库,让每个人都能够通过互相帮助和分享经验来进步。
评论