Sync Gateway view query - getting total rows when filtering

view
query

#1

Hi!

We are developing a web application showing data queried from a SG view using public REST API.
Data is displayed by a table with paging (DataTables).
Paging needs information about total rows, so we tried to use the QueryResult’s total_rows value, as it has the following description on the API explorer: “Number of documents in the database. This number is not the number of rows returned.”

Our main problem that this seems to be inaccurate.
When querying the view (which was created by SG admin REST API) with skip and limit parameters, the total_rows value always equals to the number of returned rows.
This means the table always shows it has only 1 page.

I did some research on the SG project on GitHub and I found a related issue.
This helped me understand the problem better, but I cannot say I agree with the current solution.
Reading the following line, it is clear, that total_rows does equal to the number of returned rows, despite stating the opposite on API explorer:

I think SG should follow the Server’s formula on this, which - I think - would be another problem, since the server request made by SG only returns a total_rows value without channel filtering (obviously).

There could have been another solution for our problem: creating the same view with reduce (_count) function for requesting count separately, but the public (SG) REST API could not serve queries for views with reduce functions correctly.

Could you confirm, that calculating total_rows on SG view querying needs some redesign (or the API documentation is incorrect)?
Is there any alternative for us to query the count?

Regards,
Levente


#2

@lewaa,

Using views is sync gateway for the long term is not the best idea.
Can you do it via N1QL?

If you need N1QL through SG interface you can put your commits in the below github issues:
N1QL through SG: https://github.com/couchbase/sync_gateway/pull/1182
FTS through SG: https://github.com/couchbase/sync_gateway/issues/2906


#3

@househippo,

Thank you for your answer!

May I ask why is that “not the best idea”? :slight_smile:

I can do it via N1QL, I just didn’t really want to access data by both API.
(The web application also manipulates data, therefore we must use SG API.)


#4

@lewaaa,

N1QL can do 100,000s of unique queries/second
Do adhoc and multi-parameter WHERE clause too.
vs
views can do maybe 5,000 - 10,000 queries/second
fixed question

More people are asking for N1QL through SG. So its probably come out soon.


#5

Thanks for the clarification!