Find the rank of a sorted query

[Dave Fooks]

Hi,

Does mongodb support the ability to find the rank of a query?

For example suppose I have a scoreboard collection with users and
scores (with an index on both) how would I find a specific users rank
efficiently?
Something like:

var cursor = db.scoreboard.find({'user':'dave'}).sort('score':-1);
var userScore = cursor.next();
var rank = userScore.rank();

Thanks,

David Fooks

[Robert Stam]

Here's one way you could to it with two queries.

Here's some sample data:

> db.scoreboard.find()
{ "_id" : ObjectId("4d99f71450f0ae2165669ea9"), "user" : "dave",
"score" : 4 }
{ "_id" : ObjectId("4d99f71b50f0ae2165669eaa"), "user" : "steve",
"score" : 5 }
{ "_id" : ObjectId("4d99f72350f0ae2165669eab"), "user" : "tom",
"score" : 3 }
>

First, find the score of the user "dave":

> db.scoreboard.find({ user : "dave" }, { score : 1 })
{ "_id" : ObjectId("4d99f71450f0ae2165669ea9"), "score" : 4 }

Then, count how many users have a higher score:

> db.scoreboard.find({ score : { $gt : 4 }}).count()
1

Since there is 1 higher score, dave's rank is 2 (just add 1 to the
count of higher scores to get the rank).

[Dave Fooks]

Nice I like it. But is it not possible to do it with only one query?

Thanks,

David Fooks

[Andreas Jung]

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

MongoDB just has no notion of "ranking" - whatever "ranking" means
in this context.

- -aj

- --
ZOPYX Limited | zopyx group
Charlottenstr. 37/1 | The full-service network for Zope & Plone
D-72070 T�bingen | Produce & Publish
www.zopyx.com | www.produce-and-publish.com
- ------------------------------------------------------------------------
E-Publishing, Python, Zope & Plone development, Consulting

-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.11 (Darwin)
Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org/

iQGUBAEBAgAGBQJNmvybAAoJEADcfz7u4AZj0EELv3v8IADRGB3IsbqDs7GXe66S
2lnzpE6znh8hpKcqtMJYZYfy2e8mC6syuB9zkBV/OHHV91e3szCbast/NQNdbtZE
qtqT3amiyxL47a5Mhr0BT4b7B1P2PNzm2FH4rosID1r6zBog6lJVg3vg/Qy72wUX
o8rUA17ZPSENc9OBZohpP6HUlwWkxIOj6sUzxSM8162T2FH+jMRuYzVxoyFvP+i8
uJT7Yg2ZBsy84/vW6r0mo5RXMYvhosxK5upRoPMbE6T83NeDDz9WbJRvTuqDsAG2
jGa8Dtz0ltblfl5U/BqIaucpV4Ni/0eSrN8wksXylgK6gcW72JMIJUyMnD4RJyGk
etvtPHLqY5yLcVCs+B5J8WV7JBTExt9Sd14U5E7cF6sTn8sJ1hNM2B178NYVqITX
nCPC/gN9/1DqgY+gSyri8a1jkb8Db10v6JMWlb4vOU0KEP0IdSy73yjZDx9kTIl6
/90EL0kIWhn5QPVygd9Ika6OgnHGc1k=
=1CIm
-----END PGP SIGNATURE-----

[David Fooks]

Hi, I've tested your code Robert

db.scoreboard.find({ score : { $gt : 4 }}).count()

but it is very slow since it cant use the b-tree structure to count
the queries.
This seems a bit ridiculous. I would have thought that the rank of a
query would be a feature that should be supported by a database
system.
You would need a custom index to do it (that keeps a count of the
number of descendants of each node in the index's tree).

> D-72070 T�bingen        | Produce & Publishwww.zopyx.com          |www.produce-and-publish.com

> - ------------------------------------------------------------------------
> E-Publishing, Python, Zope & Plone development, Consulting
>
> -----BEGIN PGP SIGNATURE-----
> Version: GnuPG v1.4.11 (Darwin)

> Comment: Using GnuPG with Mozilla -http://enigmail.mozdev.org/

>
> iQGUBAEBAgAGBQJNmvybAAoJEADcfz7u4AZj0EELv3v8IADRGB3IsbqDs7GXe66S
> 2lnzpE6znh8hpKcqtMJYZYfy2e8mC6syuB9zkBV/OHHV91e3szCbast/NQNdbtZE
> qtqT3amiyxL47a5Mhr0BT4b7B1P2PNzm2FH4rosID1r6zBog6lJVg3vg/Qy72wUX
> o8rUA17ZPSENc9OBZohpP6HUlwWkxIOj6sUzxSM8162T2FH+jMRuYzVxoyFvP+i8
> uJT7Yg2ZBsy84/vW6r0mo5RXMYvhosxK5upRoPMbE6T83NeDDz9WbJRvTuqDsAG2
> jGa8Dtz0ltblfl5U/BqIaucpV4Ni/0eSrN8wksXylgK6gcW72JMIJUyMnD4RJyGk
> etvtPHLqY5yLcVCs+B5J8WV7JBTExt9Sd14U5E7cF6sTn8sJ1hNM2B178NYVqITX
> nCPC/gN9/1DqgY+gSyri8a1jkb8Db10v6JMWlb4vOU0KEP0IdSy73yjZDx9kTIl6
> /90EL0kIWhn5QPVygd9Ika6OgnHGc1k=
> =1CIm
> -----END PGP SIGNATURE-----
>

>  lists.vcf
> < 1KViewDownload

[Robert Stam]

Sorry, I don't know any faster way to do it.

I expect that if you have an index on score it can do the count by
scanning the index, which should be smaller than scanning the
collection because it is smaller.

[Robert Stam]

Ooops... read: "should be *faster*" where I wrote "should be smaller".