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

question.votes.count

I have seen how to group the votes db at http://guides.rubyonrails.org/active_record_querying.html#group

Vote.group("answer_id")

But I want to order my questions by votes. That is ordering an array from a table via the grouping of another table. Is that possible?

q=Question.last
q.answers.order_by_vote #How to do this?

I think my answer could be doing a scope on the answer model that makes the grouping on the subset of votes that belong to the question. Am I right?

Thank you.

回答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')

回答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.