Consider the following example of a school. There is a classroom (chosen over the term 'class' to avoid any syntactic confusions). Each classroom has many students and one prefect, who is one among those students. Shown below is how I have written the models:
考虑以下学校的例子。有一个教室（在“课堂”一词中选择以避免任何语法混淆）。每个教室都有许多学生和一名长官，他们是这些学生中的一员。下面显示的是我如何编写模型：
class Classroom
has_many :students, inverse_of: :classroom
belongs_to :prefect, class_name: "Student", inverse_of: :classroom
end
class Student
belongs_to :classroom, inverse_of: :students
belongs_to :house, inverse_of: :members
end
class House
has_many :members, class_name: "Student", inverse_of: :house, foreign_key: "house_id"
has_one :address, as: :addressable
end
class Address
# attribute city: :string
belongs_to :addressable, polymorphic: true
end
The purpose of house is to track students who come from the same house. Each house has an address and one of the attributes of address is city. Of course there are other attributes in each of these models that I have not mentioned for brevity. Address, for instance, belongs to other models that are not listed here, hence the polymorphic association.
房子的目的是跟踪来自同一所房子的学生。每个房子都有一个地址，其中一个地址属性是城市。当然，为简洁起见，我在这些模型中还有其他属性。例如，地址属于此处未列出的其他模型，因此属于多态关联。
Objective
目的
My objective is to get the list of classrooms whose prefects are from a particular city. I know there are ways to solve this in Ruby, but how can I achieve this using an ActiveRecord/SQL query? I'm using Rails 5.0 and Postgres 9.6.
我的目标是获得其长官来自特定城市的教室清单。我知道有很多方法可以在Ruby中解决这个问题，但是我如何使用ActiveRecord / SQL查询实现这一目的呢？我正在使用Rails 5.0和Postgres 9.6。
What I Have Tried
我曾经尝试过什么
I have already tried a couple of ways, all by joining the tables up. However, the issue I face is that due to the unconventional names, AR is unable to generate the apt query. Here's the most promising variation of what I tried:
我已经尝试了几种方法，所有这些都是通过加入表格来完成的。但是，我面临的问题是由于非常规名称，AR无法生成apt查询。这是我尝试过的最有希望的变化：
Classroom.joins(prefect: {house: :address}).where(prefect: {house: {address: {city: "Barcelona"}}})
The join seems to be working fine. However, the where bit goes bonkers since the underlying table for prefect is students. The result of running this is just an ActiveRecord Relation object. But run .to_a on that and I get the following:
加入似乎工作正常。然而，因为知识的基础表是学生，所以位置变得疯狂。运行它的结果只是一个ActiveRecord Relation对象。但是运行.to_a，我得到以下内容：
ActiveRecord::StatementInvalid: PG::UndefinedTable: ERROR: missing FROM-clause entry for table "address"
I was successfully able to retrieve classrooms whose prefects have a particular first name as follows:
我成功地能够检索其长官具有特定名字的教室，如下所示：
Classroom.joins(prefect: {house: :address}).where(students: {first_name: "Juliana"})
But that's the best I got. I'm not able to apply the same logic when the nesting comes in:
但这是我得到的最好的。嵌套进来时，我无法应用相同的逻辑：
Classroom.joins(prefect: {house: :address}).where(students: {house: {address: {city: "Barcelona"}}})
Running this one results in the following error:
运行此导致以下错误：
NoMethodError: undefined method `_reflect_on_association' for nil:NilClass
from /home/vagrant/.rvm/gems/ruby-2.3.1@school/gems/activerecord-5.0.0.1/lib/active_record/table_metadata.rb:47:in `associated_table'
Any ideas what I should do here?
有什么想法我应该在这做什么？
2 个解决方案
#1
1
Classroom.joins(prefect: {house: :address}).where(prefects: { houses: { addresses: { city: "Barcelona"}}})
Side note: I think you're overusing :inverse_of. From the Rails guides
旁注：我认为你过度使用：inverse_of。来自Rails指南
Active Record supports automatic identification for most associations with standard names. However, Active Record will not automatically identify bi-directional associations that contain any of the following options:
Active Record支持大多数标准名称关联的自动识别。但是，Active Record不会自动识别包含以下任何选项的双向关联：
-
:conditions
：条件
-
:through
：通过
-
:polymorphic
多态
-
:class_name
：班级名称
-
:foreign_key
：foreign_key
and
和
class Author < ApplicationRecord
has_many :books, inverse_of: 'writer'
end
class Book < ApplicationRecord
belongs_to :writer, class_name: 'Author', foreign_key: 'author_id'
end
So you can simplify your models a bit to this
所以你可以简化你的模型
class Classroom
has_many :students
belongs_to :prefect, class_name: "Student"
end
class Student
belongs_to :classroom
belongs_to :house, inverse_of: :members
end
class House
has_many :members, class_name: "Student", foreign_key: "house_id"
has_one :address, as: :addressable
end
class Address
belongs_to :addressable, polymorphic: true
end
#2
0
I finally found something that worked for me, and it's much simpler than what I originally tried:
我终于找到了对我有用的东西，它比我最初尝试的要简单得多：
Classroom.joins(prefect: {house: :address}).where("addresses.city = ?", "Barcelona")
#1
1
Classroom.joins(prefect: {house: :address}).where(prefects: { houses: { addresses: { city: "Barcelona"}}})
Side note: I think you're overusing :inverse_of. From the Rails guides
旁注：我认为你过度使用：inverse_of。来自Rails指南
Active Record supports automatic identification for most associations with standard names. However, Active Record will not automatically identify bi-directional associations that contain any of the following options:
Active Record支持大多数标准名称关联的自动识别。但是，Active Record不会自动识别包含以下任何选项的双向关联：
-
:conditions
：条件
-
:through
：通过
-
:polymorphic
多态
-
:class_name
：班级名称
-
:foreign_key
：foreign_key
and
和
class Author < ApplicationRecord
has_many :books, inverse_of: 'writer'
end
class Book < ApplicationRecord
belongs_to :writer, class_name: 'Author', foreign_key: 'author_id'
end
So you can simplify your models a bit to this
所以你可以简化你的模型
class Classroom
has_many :students
belongs_to :prefect, class_name: "Student"
end
class Student
belongs_to :classroom
belongs_to :house, inverse_of: :members
end
class House
has_many :members, class_name: "Student", foreign_key: "house_id"
has_one :address, as: :addressable
end
class Address
belongs_to :addressable, polymorphic: true
end
#2
0
I finally found something that worked for me, and it's much simpler than what I originally tried:
我终于找到了对我有用的东西，它比我最初尝试的要简单得多：
Classroom.joins(prefect: {house: :address}).where("addresses.city = ?", "Barcelona")