为什么在Java N1QL中的ORDER BY没有按预期工作

huangapple go评论93阅读模式
英文:

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&lt;Project&gt; getAllProjects(String requestedOrder, Integer page, Integer pageOffset) {
        String statement = &quot;SELECT projects.* FROM projects ORDER BY $requestedOrder DESC OFFSET $offset LIMIT $pageOffset&quot;;
        QueryOptions queryOptions = queryOptions().parameters(
                JsonObject.create().put(&quot;offset&quot;, (page - 1) * pageOffset)
                        .put(&quot;pageOffset&quot;, pageOffset)
                        .put(&quot;requestedOrder&quot;, requestedOrder));
        QueryResult queryResult = couchbaseCluster.query(statement, queryOptions);
        return queryResult.rowsAs(Project.class);
    }

Web UI result

[
  {
    &quot;projects&quot;: {
      &quot;createdAt&quot;: 1603804921950,
      &quot;name&quot;: &quot;Third Project&quot;,
      ...
    }
  },
  {
    &quot;projects&quot;: {
      &quot;createdAt&quot;: 1603804915827,
      &quot;name&quot;: &quot;Second Project&quot;,
      ...
    }
  },
  {
    &quot;projects&quot;: {
      &quot;createdAt&quot;: 1603804909410,
      &quot;name&quot;: &quot;First Project&quot;
      ...
    }
  }
]

Java SDK Result

[
  {
    &quot;name&quot;: &quot;Second Project&quot;,
    &quot;createdAt&quot;: &quot;2020-10-27T13:21:55.827+00:00&quot;,
    ...
  },
  {
    &quot;name&quot;: &quot;Third Project&quot;,
    &quot;createdAt&quot;: &quot;2020-10-27T13:22:01.950+00:00&quot;,
    ...
  },
  {
    &quot;name&quot;: &quot;First Project&quot;,
    &quot;createdAt&quot;: &quot;2020-10-27T13:21:49.410+00:00&quot;,
    ...
  }
]

When i change my statement to this it orders correctly but why it's not ordering correctly using put method.

String statement = &quot;SELECT projects.* FROM projects ORDER BY &quot; + requestedOrder + &quot; DESC OFFSET $offset LIMIT $pageOffset&quot;;

答案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 = &quot;SELECT projects.* FROM projects ORDER BY $requestedOrder DESC OFFSET $offset LIMIT $pageOffset&quot;;

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 = &quot;SELECT p.* FROM projects AS p ORDER BY p.[$requestedOrder] DESC OFFSET $offset LIMIT $pageOffset&quot;;

huangapple
  • 本文由 发表于 2020年10月27日 21:37:40
  • 转载请务必保留本文链接:https://go.coder-hub.com/64555692.html
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定