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

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. 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!

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

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

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