Rails 3 ActiveRecord & Postgres: order by count on association

activerecord, postgresql, ruby-on-rails, ruby-on-rails-3, sql

Solution

You're missing a grouping, I believe. Here's the query you're trying to do:

select profiles.*, count(votes.id) as votes_count 
  from profiles 
  left join votes on votes.for_id = profiles.user_id 
  group by profiles.id 
  order by votes_count desc;

So, let's turn this into an ActiveRecord scope:

scope :top, joins('left join votes on votes.for_id = profiles.user_id').
  select('profiles.*, count(votes.id) as votes_count').
  group('profiles.id').
  order('votes_count desc')

Problem

I have the following 2 rails models: ``` class Profile < ActiveRecord::Base belongs_to :user has_many :votes, through: :user default_scope includes(:user) end ``` and ``` class Vote < ActiveRecord::Base attr_accessible :by, :for belongs_to :by, class_name: "User" belongs_to :for, class_name: "User" validates :by, :for, presence: true validates_uniqueness_of(:by, scope: :for) end ``` I'm trying to create a "top" scope on Profile which sorts the Profiles based on the number of "for" votes the associated user record has received A vote is created by a user, for a user. The "by" column indicates the user who casted the vote, the "for" column indicates the user that has received a vote. I'm trying to get the profile of the user that has received the most votes. This is what I've got so far: ``` scope :top, select("profile.*, count(votes.id) AS votes_count"). joins(:votes, :user). order("votes_count DESC") ``` This does not work with the following error: ActiveRecord::StatementInvalid: PG::Error: ERROR: column "votes_count" does not exist It also doesn't take the "for" column into account Can anyone help me out ?

Original source