How to do Pagination by using N1QL query?

SQL++
1

Hi.
How to get to 1-10 and 10-20 and 20-30 records by using N1QL query.

1 Like
2

Please use LIMIT and OFFSET
LIMIT: http://developer.couchbase.com/documentation/server/4.5/n1ql/n1ql-language-reference/limit.html
OFFSET: http://developer.couchbase.com/documentation/server/4.5/n1ql/n1ql-language-reference/offset.html

thanks
-cihan

3

@cihangirb Thanks i will check it.

4

Late update! For people arriving via Google, paging in N1QL queries is covered quite a bit here:

https://blog.couchbase.com/paging-data-queries-with-n1ql/

5

Is there any plans by the CB team to avoid doing an additional query to get the count of the total number of results?
At the moment we always have to do two queries to display something like “You are on page 2 of 5 pages showing 50 items of 250 total items”.

Both of our queries are having the same WHERE condition while the query getting the total number of results has a SELECT count(*)

My assumption is that this is a very common requirement and it maybe much more efficient to do this in Couchbase under the hood in one query instead of two separate queries. Maybe even optional would be fine.

1 Like
6

Hi @synesty, Yes. We are investigating into approximate counts.
Our indexing team is working on this and could be exploited if the query is covered.

@venkat, @deepkaran.salooja, @siri: please comment.

1 Like
7

@synesty, as @keshav_m mentioned, indexing team is investigating on how to build approximate counts. Once available, a single query should be sufficient to get back both the results and the count.

Also in 5.0, the count gets pushed down to the index service and you should see a big improvement in the response time when you execute your first query to get the count.

1 Like
8

Thanks, that is great news :slight_smile: Yes we are on 5.0 beta2 already and counts are fast. But the approximate counts would still be another great thing to reduce another query and make the code simpler and more intuitive.