Couchbase N1QL Java SDK Pagination and Sorting issue

Question

I have started using couchbase recently. I am using Spring-Data couchbase for inserting Java POJOs in Couchbase. Since, Pagination and Sorting is not supported by Spring Data Couchbase project, I have tried to use couchbase java client 2.2.0-dp2.

I have inserted 8 users where ids ranging from 1 to 8.

I have wrote following code to apply pagination and sorting.

public void test() {
        int offset = 5 * (1 - 1);
        Statement statement = select("*").from("test").where(x("_class").eq(s("com.test.rest.entity.User"))).orderBy(Sort.asc("id")).limit(5).offset(offset);
        log.info(statement.toString());
        Iterator<QueryRow> result = bucket.query(Query.simple(statement)).rows();

        while(result.hasNext()) {
            QueryRow doc = result.next();
            log.info("Document:: " + doc.value());
        }
}

However, I am seeing result as below. It should be test1 to test5, though users being selected randomly. Can someone help me with that?

Document:: {“test":{"createdAt":1.443420400374E12,"firstname":"test5","_class":"com.test.rest.entity.User","type":"User","lastname":"test5"}} 
Document:: {“test":{"createdAt":1.443420708495E12,"firstname":"test8","_class":"com.test.rest.entity.User","type":"User","lastname":"test8"}} 
Document:: {“test:{"createdAt":1.443420386638E12,"firstname":"test2","_class":"com.test.rest.entity.User","type":"User","lastname":"test2"}} 
Document:: {“test":{"createdAt":1.443420704104E12,"firstname":"test7","_class":"com.test.rest.entity.User","type":"User","lastname":"test7"}} 
Document:: {“test":{"createdAt":1.443420379712E12,"firstname":"test1","_class":"com.test.rest.entity.User","type":"User","lastname":"test1"}}

user1697575 · Answer 1 · 2015-10-01T14:07:47.920

0

It should be test1 to test5, though users being selected randomly

Looks like you are expecting to sort by first name, since id is generated by CB.

Try replacing "id" with "firstname", e.g.:

Statement statement = select("*").from("test").where(x("_class")
  .eq(s("com.test.rest.entity.User")))
  .orderBy(Sort.asc("firstname")).limit(5).offset(offset);

NOTE: I suspect that field name "id" collides with the metadata's id (hence not returned in your json result). Try naming it differently like "_id", "id#" etc.

edited Oct 01 '15 at 14:07

answered Sep 28 '15 at 14:34

user1697575

2,830
1
24
37

Nope. That's not the case. While create record, I am setting id sequentially., and Id is Long. I am appending the same id after "test". We can also take out order by, but records are still random. – Pritish Shah Sep 28 '15 at 17:01
Understand. Why then id attribute is not present in your output of the query? Perhaps that is the issue? – user1697575 Sep 28 '15 at 17:35
Good point. QueryRow class has value() method which return JsonObject, and that doesn't include id field. Also I have tried sorting on createdAt, and than pagination work as expected. But, still I am wondering why id is not being returned as part of json. I guess that will help me to get my solution. Thanks for the help man. Really appreciate it. – Pritish Shah Sep 29 '15 at 06:07

score 0 · Accepted Answer · answered Oct 01 '15 at 16:59

After going through Spring Data Couchbase codebase, I figure out the way they are querying the n1ql. By default select(*) doesn't select id because id is not part of document. So,

N1QL Statement:

SELECT META(`test`).id AS _ID, META(`test`).cas AS _CAS, `test`.* FROM `test` WHERE `_class` = "com.test.rest.entity.User";

Couchbase Java Client code:

 Statement statement = select("META(`test`).id AS _ID, META(`test`).cas AS _CAS, `test`.*").from("test").where(x("_class").eq(s("com.test.rest.entity.User"))).orderBy(Sort.asc("_ID")).limit(5).offset(offset);

NOTE: orderBy(Sort.asc("_ID")) is not required. I just kept it for sample.

Couchbase N1QL Java SDK Pagination and Sorting issue

2 Answers2