Rails 3中ActiveRecord的＆放大器; Postgres的：为了通过关联数 [英] Rails 3 ActiveRecord & Postgres: order by count on association

问题描述

我有以下2轨型号：

class Profile < ActiveRecord::Base
  belongs_to :user

  has_many :votes, through: :user

  default_scope includes(:user)

end

和

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.

这是我有这么远：

   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 ::错误：错误：列   votes_count不存在

ActiveRecord::StatementInvalid: PG::Error: ERROR: column "votes_count" does not exist

它也不会走为列进去

谁能帮我？

推荐答案

您错过一组，我相信。 这里就是你要做的查询：

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;

所以，让我们把它变成一个ActiveRecord范围：

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

这篇关于Rails 3中ActiveRecord的＆放大器; Postgres的：为了通过关联数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！