按关联模型中的列对 Rails 数据库表进行排序
我正在尝试实现 Ryan Bates 的可排序表列代码（Railscast #228），但我希望能够对关联列进行排序。特别是，我有以下模型和关联：
class Project < ActiveRecord::Base
belongs_to :program_manager, :class_name => "User"
class User < ActiveRecord::Base
has_many :program_manager_projects, :class_name => "Project", :foreign_key => "program_manager_id"
项目模型和用户模型之间的关联由“program_manager_id”外键介导，用户使用集合选择下拉列表在新/编辑视图中设置该外键。以下是project.rb 顶部注释的一部分：
# Table name: projects
# program_manager_id :integer
我希望能够按项目经理的名称（即project.program_manager.name）对索引视图中的项目列表进行排序。
理想情况下，我能够以某种方式指向这个名称，也许在我的 ProjectsController 的索引方法中使用类似的东西：
@projects = Project.find(:all, :order => project.program_manager.name)
但这显然行不通（更不用说 Ryan 的例程通过对表的特定引用来实现这一点） ）
我遇到过一些使用named_scope的令人生畏的方法，例如：
named_scope :most_active, :select => "questions.*", :joins => "left join comments as comments_for_count on comments_for_count.question.id = questions.id", :group => "questions.id", :order => "count(questions.id) desc"
但是由于我缺乏MySQL专业知识，这对我来说相当难以理解。
任何人都可以帮助我针对我的具体情况概括上面的named_scope示例，或者为我指出一个更直接的策略吗？
非常感谢，
迪恩
I'm trying to implement Ryan Bates' sortable table columns code (Railscast #228) but I'd like to be able to sort on an associated column. In particular, I have the following models and associations:
class Project < ActiveRecord::Base
belongs_to :program_manager, :class_name => "User"
class User < ActiveRecord::Base
has_many :program_manager_projects, :class_name => "Project", :foreign_key => "program_manager_id"
The association between the Project model and the User model is mediated by the 'program_manager_id' foreign key, which the user sets in the new/edit views using a collection-select dropdown. Here's part of the annotation at the top of project.rb:
# Table name: projects
# program_manager_id :integer
I want to be able to sort my list of projects in the index view by the program manager's name, i.e., by project.program_manager.name.
Ideally, I'd be able to point :order to this name somehow, perhaps with something like this in the index method of my ProjectsController:
@projects = Project.find(:all, :order => project.program_manager.name)
But that obviously won't work (not to mention Ryan's routine implements this with a specific reference to table names from the model to be sorted.)
I've come across some intimidating approaches that use named_scope, such as:
named_scope :most_active, :select => "questions.*", :joins => "left join comments as comments_for_count on comments_for_count.question.id = questions.id", :group => "questions.id", :order => "count(questions.id) desc"
But given my lack of MySQL expertise, this is fairly impenetrable to me.
Can anyone help me either generalize the named_scope example above for my specific case, or point me to a more straightforward strategy?
Thanks very much,
Dean
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（1）
让我们剖析一下您上面引用的命名范围。想象一个有很多评论的模型问题。
您的范围的名称。您将这样引用： Question.find(:all).most_active
默认范围无论如何都会选择表中的所有列，因此这将结果限制为仅问题表，而不是评论表。这是可选的。
这就是说，对于每个问题，我也想获得与它们相关的所有评论。评论表有一列“question_id”，我们将使用该列将它们与适当的问题记录进行匹配。这很重要。它允许我们访问模型中没有的字段！
这是 order 子句中的 count() 函数所必需的，它告诉我们我们想要基于问题的评论计数。我们的 order 子句中不需要 count 函数，因此我们也不需要这个 group 语句
按评论数从高到低的顺序返回结果。
因此，对于我们的示例，丢弃我们不需要的内容并应用到您的需求，我们最终会得到：
这个named_scope将被这样调用：
注意这基本上相当于：
但是，正如上面提到的cam，你应该真正知道底层 SQL。如果没有这种理解，你的能力将受到严重阻碍
Let's dissect that named scope you referenced above. Imagine a model Question which has many Comments.
the name of your scope. You would reference thusly: Question.find(:all).most_active
by default scopes selects all columns from your table anyway, so this limits the results to only the questions table, and not the comments table. This is optional.
this is saying for every question, I also want to get all comments associated with them. The comments table has a column 'question_id' which is what we'll be using to match them up to the appropriate question record. This is important. It allows us access to fields that are not on our model!
This is required for the count() function in the order clause to tell us that we want the count of comments based on question. We don't need the count function in our order clause, so we also don't need this group statement
Return the results in order of number of comments, highest to lowest.
So for our example, discarding what we don't need, and applying to your needs, we end up with:
This named_scope would be called thusly:
Note this is basically equivalent to:
But, as cam referenced above, you should really know the underlying SQL. Your abilities will be severely hampered without this understanding