Combo Mysql/Rails/DBD question - How do I do rankings?

Rails
1

So I’m trying to be jack of all trades on a project, and instead i am
mastering none.

Let’s say I have a table of users. Each user has a popularity score
which is a non-negative float.

On each user’s homepage I want to display their rank. “Jimbo is
ranked #3” Jimbo has the third highest number in the “popularity”
column (out of all rows) at the moment that I pulled the row out of
the DB.

Umm…how do I do this? I can think of a really expensive way
involving a giant data load, a sort, and a count, but it’s not the
rails way.

Any ideas? Thanks!

2

[email protected] wrote:

On each user’s homepage I want to display their rank. “Jimbo is
ranked #3” Jimbo has the third highest number in the “popularity”
column (out of all rows) at the moment that I pulled the row out of
the DB.

rank = User.count(:conditions => [‘popularity >= ?’, jimbo.popularity])

3

I think his point is “popularity” is NOT 3. It’s some float that is 3rd
highest of all entries in the column.

I think you have to dip into the db specific features of the database to
do
this.

Usually this involves using a variable in the query in MySQL.

Personally I Think you should maintain a column that reflects the
popularity
rank, especially if you plan to paginate such results. I would do
something
like this: when a person’s popularity changes, using their current
popularity and their new popularity, you should be able to find the
specific
people who need to be moved down or up 1. I would execute an UPDATE
query
that moves these people up/down 1. And then update the popluarity rank
column of the person in question. That is really inexpensive to do.

Then you don’t need a giant data load. I wouldn’t try to do it with
vendor
specific SQL unless you truely needed to avoid adding a column.

That’s my $0.02

4

Miles, you understood the problem correctly.

My point of view was that any time a user’s popularity changes I will
have to run through the “ranking” column for every user. I can’t be
sure that 50 will swap places with 49; it’s possible that 50 drops to
55, so then I’ve got to re-rank everyone between 50 and 55, I guess.

Still worth doing in a column though, I guess.

Thanks for the idea.
—Andrew