Rails - Jul 2008 - 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.

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!
--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups
"Ruby on Rails: Talk" group.
To post to this group, send email to
rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
To unsubscribe from this group, send email to
rubyonrails-talk-unsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
For more options, visit this group at
http://groups.google.com/group/rubyonrails-talk?hl=en
-~----------~----~----~----~------~----~------~--~---

andrew.ohnstad-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org 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])



--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups
"Ruby on Rails: Talk" group.
To post to this group, send email to
rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
To unsubscribe from this group, send email to
rubyonrails-talk-unsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
For more options, visit this group at
http://groups.google.com/group/rubyonrails-talk?hl=en
-~----------~----~----~----~------~----~------~--~---

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

On Sat, Jul 26, 2008 at 7:10 AM, Phlip
<phlip2005-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org> wrote:
>
> andrew.ohnstad-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org 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])
>
>
>
> >
>
--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups
"Ruby on Rails: Talk" group.
To post to this group, send email to
rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
To unsubscribe from this group, send email to
rubyonrails-talk-unsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
For more options, visit this group at
http://groups.google.com/group/rubyonrails-talk?hl=en
-~----------~----~----~----~------~----~------~--~---

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

On Jul 26, 3:25 pm, "Miles Georgi"
<azi...-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org>
wrote:
> 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
>
>
>
> On Sat, Jul 26, 2008 at 7:10 AM, Phlip
<phlip2...-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org> wrote:
>
> > andrew.ohns...-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org 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])- Hide quoted text -
>
> - Show quoted text -
--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups
"Ruby on Rails: Talk" group.
To post to this group, send email to
rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
To unsubscribe from this group, send email to
rubyonrails-talk-unsubscribe@googlegroups.com
For more options, visit this group at
http://groups.google.com/group/rubyonrails-talk?hl=en
-~----------~----~----~----~------~----~------~--~---