CODEWITHSUNDEEP
sqlruby-on-railsrubyruby-on-rails-3sorting
grooble
grooble

Reputation: 657

rails order through count on other table

I'm adding quiz functionality to the twitter app from the Hartl tutorial and have these Models:

User is nearly the same as the tutorial:

class User < ActiveRecord::Base

  has_many :followed_users, through: :relationships, source: :followed

  has_many :takens, dependent: :destroy
  has_many :questions, through: :takens
end

Taken is a table of Question ids to User ids:

class Taken < ActiveRecord::Base
  belongs_to :user
  belongs_to :question

end

nothing interesting in Question:

class Question < ActiveRecord::Base
  attr_accessible :category, :correct, :option1, :option2, :option3, :qn
end

I want to be able to show followed_users and followers in order of the number of tests they have taken. In the console this can be had through:

User.find_by_id(1).question_ids.count

Then I can do something like:

User.find_by_id(1).followers.first.question_ids.count

in the console to get the count for a single follower.

I feel like I'm almost there.

How do I sort the followers and followed_users through their 'takens' count? (I was also looking at cache_count, which at first seemed promising, but might not be what I need...)

Upvotes: 1

Views: 1027

Answers (1)

hatkirby
hatkirby

Reputation: 850

Ruby on Rails does not provide an object oriented mechanism to perform this; you have to write the SQL yourself. In your case, I'd say that the following line SHOULD work:

User.find_by_sql("SELECT users.*, COUNT(questions.id)
 AS c FROM users, questions WHERE questions.user_id = users.id
 GROUP BY users.id ORDER BY c DESC")

I don't have the actual tables in front of me, so I can't be sure that this is actual valid SQL, but hopefully it should work.

EDIT: There were a few syntax errors with my SQL but they've been fixed. Note that I'm assuming that your tables are called users and questions. They may differ for you.

Upvotes: 4

Related Questions