Order Table By Grouping Values In Another Table

I have a table of questions: title Each question has answers, in another table: text, question_id Each answer has votes, in another table: answer_id I can ask for total votes with

Solution 1:

First, I think that you mean table when you say DB (database). A table a structure inside a database that holds data for a specific model, for example (questions, votes and answers. In your case you have three tables.

Second, calling attr_accessible :answer_id does not make an attribute searchable by an index. It is searchable by this attribute regardless of whether or not you declare it accessible. It is not an index unless you specify it as an index in the database. attr_accessible means that the attribute can be set using mass-assignment, for example when you call update_attributes.

Finally, if you want to group by answer_id and order by the number of votes, you can do so with the following query:

Vote.select('count(id) as votes, answer_id').group('answer_id').order('votes DESC')

If you want to order by some other value than the one you are grouping by, you'll have to add that to your group as well. This ensures that you aren't getting arbitrary results for the second value. For example:

Vote.group('answer_id').group('column_1').order('column_1 DESC')

Solution 2:

I just figured out a way to organize my links by vote count, using the sort method in my links view:

<% @user.links.sort { |a, b| b.votes.sum(:score) <=> a.votes.sum(:score) }.each do |link| %>

votes.sum(:score) grabs the number of votes a particular link has from the votes table.

hope that helps.