How do you order an array by a connected integer in Ruby on Rails?

Question

I'm creating a most popular activity section for user profiles. I have no difficulty pulling questions through the user_id but I'm having trouble pulling then ordering by the associated integer: question.votes.size . This is probably a simply question but how do I sort then limit the output to 3? How do I do this without lagging the database? There will eventually be a lot of votes to be counted. Should this be a named_scope?

    @user_id = User.find_by_username(params[:username]).id
    questions = Question.find(:all, :conditions => {:user_id => @user_id })

Answer 1

I'm assuming that each question has many votes.

You can do this by using

Question.find_by_sql("
  SELECT question.*, COUNT(votes.id) as vote_count 
   FROM questions 
   LEFT JOIN votes on questions.id = votes.question_id 
   GROUP BY questions.id 
   ORDER BY vote_count DESC
");

or something roughly equivalent to that (I didn't test it)

Answer 2

Hey there!

I wanted to pop in and suggest another way that is, perhaps, a bit more native RoR. :-)

@user = User.find_by_username( params[:username], :include => [{:questions => :votes}]) @sorted_questions = @user.questions.sort { |q1,q2| q2.votes.length <=> q1.votes.length }

This has a number of advantages:

1) No SQL written, maintains DB portability, easier to read(?) 2) Relieves DB of sort compute, should scale better

and a couple of disadvantages:

1) Works Ruby harder, higher latency at low loads, less efficient on single box 2) Moves more data, potentially mitigates advantage #2

Ideally, you'd want to look at ActiveRecord's counter cache functionality. It automatically caches relationship counts by denormalizing the child row count into the parent table. For it to work, all child row manipulation must occur via the parent object, but that's Rails best practice in any case.

Having a votes counter cache in questions would eliminate the need to reference the votes table in the query. Doing this, and sorting in Ruby, might be the ideal situation from both performance and code esthetic points of view.

Finally, I have to admit punting on Rails 3's very cool relational algebra stuff. With it, it's likely that this could be a super readable one-liner that generates optimal SQL. How cool is that going to be? :-)