Rails order by count through two model instances

Question

I have this bit of code where each project can be voted on. In my views I want to list each project by the order of votes from highest to lowest.

I found one way, but is there a better way to do this?

My way:

Controller

class Admin::ProjectsController < ApplicationController
  def index
     @projects = Project.all.sort_by { |project| project.votes.count }
  end
end

Views

- @projects.each do |project|
  %li= project.name
  %li= project.teacher_firstname
  %li= project.teacher_lastname
  %li= project.votes.count

I was trying to do something along the lines of Project.votes.all.order(:desc) but obviously that doesn't work, but that's what I want to try to do.

And then someone pointed me to :counter_cache but that seems a little too much just to count. No need to add a count column.

Any other suggestions?

Update Okay so two things

1. @jgautsch's worked, but I'm curious, it's a class method wouldn't I want to make this an instance method? Or would it not matter? Just take off self? I sometimes get confused (as much as read articles on this) of when it's appropriate for instance verses class method.

2. Also, I noticed that I only get projects that have been voted on. How would I continue to display them even if there are 0 votes? I even took out the limit and the num argument

Answer 1

Check out this question: Rails 3 ActiveRecord: Order by count on association

As described in the linked question, you can either use counter_cache and keep your query to one table (nothing wrong with adding a column here), or do something like this (untested):

class Project
  has_many :votes

  def self.top_voted(num = 10)
    select("projects.id, OTHER_ATTRS_YOU_NEED, count(votes.id) AS votes_count")
    .joins(:votes)
    .group("projects.id")
    .order("votes_count DESC")
    .limit(num)
  end
end

Project.top_voted(5) # top 5 most voted projects

Answer 2

Interesting question. You can do this:

Project.select('*, (SELECT count(*) FROM votes WHERE votes.project_id=project.id) as vote_count').order("vote_count DESC")

Ugly, I know, but at least its just 1 query.

Would be cool if there's a fully ORM way to do that, something like:

Project
  .select(vote_count: Vote.where("project_id = project.id").count)
  .order(vote_count: :desc)

UPDATE: another functional way:

Project
  .select(
    Vote
      .select('count(*)')
      .where('project_id=projects.id')
      .to_sql.sub(/(.*)/, '*, (\1)as vote_count'))
  .order("vote_count DESC")

or maybe we can achieve a more ORMed by using Arel directly or search by Vote (and join with Project inside) instead of search by Project (and join with Vote inside).