ibrahimyaacob•7mo ago

I need to rank users

I have a table called players with a column score. i want to return the ranking of the user for a leaderboard. I need an advice, should i create a column called rank and keep updating the whole table when the score changes, or is there a way to get the rank another way ?

5 Replies

lee•7mo ago

Cool question. You could keep a rank column or you could calculate the rank as-needed in the query. Something like

let rank = 1;
for await (const rankedPlayer of ctx.db.query("players").withIndex("by_score")) {
  if (player._id === rankedPlayer._id) break;
  rank++;
}

let rank = 1;
for await (const rankedPlayer of ctx.db.query("players").withIndex("by_score")) {
  if (player._id === rankedPlayer._id) break;
  rank++;
}

But there is a more scalable way for if you have more than 1000 players. I've been working on a helper library to support things like this, but it's not ready yet.

ibrahimyaacobOP•7mo ago

hey @lee thanks for the suggestion, I am looking for that 1k++ players solution. Any estimate when is that helper library is going to be released ?

lee•7mo ago

I can aim to release an unpolished version this week. The trick, in case you're curious, is adding a btree data structure that sorts by score, where each node keeps a count of the keys it contains. That way you can ask "how many items are before this target item, in score order" while only reading log(n) documents

lee•7mo ago

made the repo, with a README that should describe how to use it https://github.com/ldanilek/aggregate

GitHub

GitHub - ldanilek/aggregate

Contribute to ldanilek/aggregate development by creating an account on GitHub.

lee•3mo ago

for anyone finding this thread later, ^ that repo no longer works, although we're about to ship something that operates on the same principles and is easier to use

I need to rank users

Did you find this page helpful?