I am writing a DAL (Data Access Layer) which uses CB 4.1.
My goal is to use n1ql for any query operation, and obviously - paginating.
so I’ll list the issues by the story that unfolds them:
first I ran the following query (type field is secondary index):
select id, fname, lname from bucket where type=“user” limit 1;
I got a result, didn’t change anything, ran the query again and again and again and each time got a totally different result.
So I went on to check how Views handle this and it turns out that Views are returning the data sorted by natural order.
If n1ql is indeed using Views behind the scenes, and my made-up index created a view, then I should get the same result each time I ran the query… So question 1: why do I see this behavior?
I went on to try and tell n1ql to sort the data by the id.
I tried running the same query but with an ORDER BY meta(id). So yes, this time I get the same result each time BUT(!!!) the query takes 9 seconds(!!!) to run.
So I thought maybe n1ql doesn’t know how to “order by a meta field”, I’ll just use the id field I manually place in the value of the document so I made an index:
create index doc_id on bucket(id) using GSI;
tested it with a simple select query and it worked (3ms to get a document by an id, nice).
But then I ran my attempt to order only this time ORDER BY id… got the same 9 seconds response time.
Looked in the web, I saw that in fact n1ql is known to have an issue with ORDER BY that should have been fixed by now… so question 2: given all this, how to get a consistent limit+skip behavior from n1ql?
we move on to the most important part. I do not want to use limit+skip for actual pagination. I wish to use the startkey + startkey_docid mechanism that couchbase has in its REST API… so question 3: how do I use that mechanism with n1ql only?
all these 3 question will answer the most important thing: The smartest most efficient way to paginate in N1QL.