Have Geek Brain

ActiveRecord and SELECT AS SQL Statements

I am developing in Rails an application where I would like to rank a list of users based on their current points. The table looks like this: user_id: string, points: integer. Since I can't figure out how to do this "The Rails Way", I wrote the following SQL code:

self.find_by_sql ['SELECT t1.user_id, t1.points, COUNT(t2.points) as user_rank FROM registrations as t1, registrations as t2 WHERE t1.points <= t2.points OR (t1.points = t2.points AND t1.user_id = t2.user_id) GROUP BY t1.user_id, t1.points ORDER BY t1.points DESC, t1.user_id DESC']

The point is that the only way to access the column with the alias "user_rank" is to give the rating [0] .user_rank, which gives me big headaches if I want to easily display the resulting table.

Is there a better option?

+1


a source to share


3 answers


What about:

@ranked_users = User.all :order => 'users.points'

then in your opinion you can say



<% @ranked_users.each_with_index do |user, index| %>
  <%= "User ##{index}, #{user.name} with #{user.points} points %>
<% end %>

if for some reason you need to store this numeric index in the database, you will need to add a callback after_save

to update the complete list of users whenever the point count has any changes. You can look into using a plugin acts_as_list

to help with this, or it might be overkill.

+1


a source


Try adding user_rank to your model.

class User < ActiveRecord::Base

  def rank
   #determine rank based on self.points (switch statement returning a rank name?)
  end

end


Then you can access it with @ user.rank.

0


a source


What to do, if:

SELECT t1.user_id, COUNT(t1.points) 
FROM registrations t1 
GROUP BY t1.user_id 
ORDER BY COUNT(t1.points) DESC

If you want to get all rails-y then do

cool_users = self.find_by_sql ['(sql above)']

cool_users.each do |cool_user|
  puts "#{cool_user[0]} scores #{cool_user[1]}"
end
0


a source






More articles:


All Articles