Limits, Offsets,Sort and result Counts

SQL++
1

As i am working to rewrite my API to handle SDK 3.X and upcoming 7.X release i am wondering if there has or will be any major changes to the Limit, Offset and resultCount.

On Offset and Limit it would be nice to specify a value which would take no action at all to avoid having to maintain multiple N1QL Queries. Specially in query’s for a grid I often use the grids paging mode or infinite scroll where i need limit and offset, but there is sometimes the case where I want all records. N1QL doesn’t allow you to add conditions for the query which means one has to dynamically create the query string outside of Couchbase . So to make it simpler it would be nice to be able to set a value of maybe -1 for limit which could mean no limit.
Another point of paging is the total record count which one needs to page thru the result and know when we reach the end, right now one has to do 2 queries, one to get the actual count and one to get the data. It would be nice if Couchbase would return in the dat query with limit the total row count for the query prior to limit. Would save an extra query as well no need to maintain 2 query’s to achieve that.
On the Order by it would be also nice to more dynamically pass in conditions to build a sort order. IN SQL you can write a lot of the logic into the stored procedure which will allow you to be quite dynamic

2

no changes in 7.0.0

You can do following and set values you want. $offset =0, $limit = 2**63-1 will return everything.

OFFSET $offset
LIMIT $limit

result count you need to do separate query. Only way get count to do actual count. This needs resources (doing one user not asking is unnecessary and increases latencies).

Also Limit query once query requirement met it stops. No way it knows how many items remaining.