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?