ActiveRecord：尝试从合并范围中排序结果时出错 [英] ActiveRecord: error when trying to order results from a scope

问题描述

我有以下模型：

  class Master< ActiveRecord :: Base 
属于：语言
 has_and_belongs_to_many：users 
 
作用域：with_users_count，->做
加入（'在masters_users.master_id上为LEFT OUTER JOIN masters_users = masters.id'）
 .select（'masters。*，COUNT（masters_users.user_id）as users_count'）
 .group （'masters.id'）
结束
结束
  

我正在尝试要做：

  Master.with_users_count.includes（：language）.order（'languages.name'）
  

会发生以下错误：

  ActiveRecord :: StatementInvalid：PG :: GroupingError：错误： languages.id列必须出现在GROUP BY子句中或在聚合函数
   
 
 
请问如何更改范围，以便此错误得到解决？
 
 
 
 编辑（其他信息）： 
 
 
 
 
 
上面的代码用于添加 quotes_count 归因于结果，因此我可以显示每个主服务器的用户数，而无需N + 1且不需要
 
 
作用域工作得很好，当我调用 order 方法。
 
 
排序是由Ransack gem完成的，所以我无法控制它的完成方式。我已经使用 order 方法来重现尝试通过Ransack  sort_link 进行排序时出现的错误， 
 
 
 
 
 
谢谢。
解决方案
您也可以在范围内使用left_outer_join与语言，它应该在订购时消除错误，因为它将在SQL查询中加入语言表。
  scope：with_users_count，->做
联接（'masters_users.master_id上的LEFT OUTER JOIN masters_users = masters.id语言.id = masters.language_id'上的OUTER JOIN语言user_id）为users_count'）
 .group（'masters.id'）
  
 end 
 
I have the following model:
class Master < ActiveRecord::Base
  belongs_to :language
  has_and_belongs_to_many :users

  scope :with_users_count, -> do
    joins('LEFT OUTER JOIN masters_users on masters_users.master_id = masters.id')
      .select('masters.*, COUNT(masters_users.user_id) as users_count')
      .group('masters.id')
  end
end
I'm trying to do:
Master.with_users_count.includes(:language).order('languages.name')
And the following error happens:
ActiveRecord::StatementInvalid: PG::GroupingError: ERROR:  column "languages.id" must appear in the GROUP BY clause or be used in an aggregate function
Could you please advise on how to change the scope so this error is fixed?

Edit (additional info):


The above code is meant to add the quotes_count "attribute" to the results, so I can display the count of users for each master without N+1 and without the need for a counter cache (which seems to be buggy for HABTM).
The scope works just fine, the error appears when I call the order method.
The sorting is done by the Ransack gem, so I have no control over how it's done. I have used the order method as a way to reproduce the error that happens when trying to sort via Ransack sort_link, in an attempt to avoid adding more complexity to the question.


Thank you.
解决方案
You can have left_outer_join with languages also in the scope, it should remove the error while ordering because it will have language table joined in the sql query.
scope :with_users_count, -> do
joins('LEFT OUTER JOIN masters_users on masters_users.master_id = masters.id LEFT OUTER JOIN languages on languages.id = masters.language_id')
  .select('masters.*, COUNT(masters_users.user_id) as users_count')
  .group('masters.id')
end

                        
这篇关于ActiveRecord：尝试从合并范围中排序结果时出错的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！