Most efficient way to return total row count on a grouped N1QL query

query
n1ql

#1

Brief question:
Is there a way to get total rows in N1QL grouped query without returning whole result set?

Expanded:
I am writing a set of queries where a leaderboard/scoreboard is queried in such a way that a player can find their score among others in a successive ranked order.

Ranking is derived on the client via simple offset+n.

Ordering is done by max of the group of scores for distinct player.

Query that returns 3 top scores above a certain score looks like this:

select 
    distinict userId, 
    max([score,meta(mybucket).id])[0] as score, 
    max([score,meta(mybucket).id])[1] as scoreId, 
    nick from mybucket
where app = 'my_app' and meta(mybucket).id like 'score_%'
group by userId having max([score,meta(mybucket).id])[0] > 24808
order by score desc
limit 3

[
  {
    "nick": "Ragged Ceiling",
    "score": 24952,
    "scoreId": "score_269"
  },
  {
    "nick": "Overjoyed Mind",
    "score": 24866,
    "scoreId": "score_514"
  },
  {
    "nick": "Lonely Mistake",
    "score": 24845,
    "scoreId": "score_575"
  }
]

Same query with < 24808 having clause to get 3 neighboring scores below.

To get the rank I need to know where this score exists in global query. The best way I can think of right now is to return:

select 
    distinict userId, max([score,meta(mybucket).id])[0] as score from mybucket
where app = 'my_app' and meta(mybucket).id like 'score_%'
group by userId having max([score,meta(mybucket).id])[0] > 24808
order by score desc

This will return a bunch of documents, and I can use row count (using .net) to get the count of all the scores above 24808. This is of course not efficient, and will grow unmanageable when score count grows and bottom of the list is queried.

Is there a way to do this more efficiently? Like a COUNT(*) but for total groups returned?


#2

Yes. If you have an ORDER BY, the metrics.sortCount field in the cbq shell output represents the total number of rows sorted, before applying OFFSET and LIMIT.

In your case, this would be the total number of groups.


#3

Gerald,
I am looking at this:
http://developer.couchbase.com/documentation/server/4.1/n1ql/n1ql-rest-api/executen1ql.html

If I understand correctly metrics are added to response containing whole result set. Is there a way to return response without the results section?


#4

Metrics are added to every response, even those with LIMIT. Why don’t you try it out and see.


#5

Gerald, apologies, that went over my head initially.