How do I get the rank / row number for a row?

Question

Is there a way to get the row numbers (rank) for a filtered set and append the row number to the result?

An example scenario would be that I have a table with records like:

[ 
  { points: 123, name: 'Glenn' },
  { points: 948, name: 'Bob' },
  { points: 22, name: 'Sarah' }
]

In the above table there are hundreds of thousands of rows, and I want to be able to rank all records based on a condition like points descending and then return a subset of the rows (using a filter) with their rank value included in the result like this:

[ { points: 123, name: 'Glenn', rank: 2 }]

Don't this Rethink has such future. You should use something special for this. For example, you can use Redis on the top of Rethink for this queries. — Suvitruf - Andrei Apanasik, Oct 25 '15 at 22:45

score 2 · Accepted Answer · answered Nov 11 '15 at 20:18

2

You should use the offsetsOf function

r.table('users') .orderBy({index: 'points'}) .offsetsOf(r.row('user_id').eq(yourUserId)) .run(conn, callback)

Command Reference: http://rethinkdb.com/api/javascript/offsets_of/

answered Nov 11 '15 at 20:18

Rafael dos Santos

328
1
7

score 0 · Answer 2 · answered Oct 25 '15 at 13:16

0

r.table('users').orderBy({index: 'points'}).run(conn, callback)

Tis will return the list sorted by points. (Ie: The first element will have rank 1, the second element rank 2, etc.)

See also: https://rethinkdb.com/api/javascript/order_by/

answered Oct 25 '15 at 13:16

MrD

4,986
11
48
90

Thanks, but that does not address the problem - if there are 300k records in the table and I want the rank of the user Glenn how can I get the users rank number without reading out every record in the table? – Glenn Morton Oct 25 '15 at 13:30
@GlennMorton can you find out the rank of a player without looking at other players? No. At the end of the day, you are sorting a list, and the complexity (Read: Time taken) is proportional to the size of the list. You could look into pre-processing your tables, which is possible with databases such as MySQL. I haven't found any reference for RethinkDB so yo umight have to write some custom script for that. – MrD Oct 25 '15 at 13:42
Also - in your question you did not mention efficiency or speed as constraints, you simply asked how to get the rank of a given user. – MrD Oct 25 '15 at 13:42

score 0 · Answer 3 · answered Jun 15 '18 at 18:47

The .map function accept one or more sequences/arrays (See Docs). So you can use your filtered sequence for first argument and use r.range() as second parameter. So your callback function will have 2 arguments (each one representing an element of one sequence/array). See below:

r.table('users')
    .orderBy({index: 'points'})
    .map(r.range(), (user, number) => {
        return user.merge(rank: number.add(1))
    }

How do I get the rank / row number for a row?

3 Answers3

Linked