Rails:找到与 where 子句的深层嵌套关联 [英] Rails: Finding a deeply nested association with a where clause

问题描述

我有两个模型加入了 has_many :through 关系:

I have two models joined with a has_many :through relationship:

class Publication < ActiveRecord::Base
  has_many :publication_contributors
  has_many :contributors, :through => :publication_contributors
end

class Contributor < ActiveRecord::Base
  has_many :publication_contributors
  has_many :publications, :through => :publication_contributors
end

class PublicationContributor < ActiveRecord::Base
  belongs_to :publication
  belongs_to :contributor
end

(关于我的 PublicationContributor 模型的一个不同寻常和重要的地方在于，它不仅仅是一对数据库 ID，它还具有一个名为 contributor_type 的字符串属性.该字符串可以包含诸如作者"或翻译者"或出版商".我不认为这是这里的问题，但解决方案仍然必须考虑它.)

(Something unusual and important about my PublicationContributor model is that it has more than just a pair of database ids, it also has a string attribute called contributor_type. This string could contain roles such as "Author" or "Translator" or "Publisher". I don't believe this is the problem here, but a solution must still account for it.)

我想找到像这样有特定贡献者的出版物:

I want to find a Publication that has specific contributors like so:

Publication
  .joins(:publication_contributors =>  :contributor)
  .where(:publication_contributors => 
            {:contributor_type => "Author", 
             :contributor => {:name => params[:authors]}})

一切正常，直到我到达嵌套的 :contributor，此时 SQL 会崩溃:

Everything works fine until I get to the nested :contributor, at which point the SQL sputters:

Mysql2::Error: Unknown column 'publication_contributors.contributor' in 'where clause'

它不是在寻找publication_contributors.contributor_id，而是在寻找并不存在的publication_contributors.contributor.我的代码做错了吗?我找不到像这样具有深层嵌套关联的 where 子句的任何其他示例.也许这甚至不可能?

Rather than looking for publication_contributors.contributor_id, it's looking for publication_contributors.contributor, which doesn't exist. Am I doing something wrong in my code? I can't find any other examples of a where clause with deeply nested associations like this. Perhaps it's not even possible?

更新:

生成的SQL

←[1m←[35mPublication Load (0.0ms)←[0m  SELECT `publications`.* FROM `publicati
ons` INNER JOIN `publication_contributors` ON `publication_contributors`.`public
ation_id` = `publications`.`id` INNER JOIN `contributors` ON `contributors`.`id`
 = `publication_contributors`.`contributor_id` WHERE `publication_contributors`.
`contributor_type` = 'Author' AND `publication_contributors`.`contributor` = '--
-\n:name:\n- Marilynne Robinson\n' LIMIT 1

此外，我的 Publications 模型中有此关联:

Also, I have this association in my Publications model:

has_many :authors, :through => :publication_contributors, :source => :contributor, :conditions => {:publication_contributors => {:contributor_type => "Author"}}

我以为我可以这样做:

Publication.joins(:authors).where(:authors => {:name => params[:authors]})

但这会引发错误:

Mysql2::Error: Unknown column 'authors.name' in 'where clause'

推荐答案

尝试改变你的 where 子句:

try to change your where clause :

Publication
  .joins( :publication_contributors => :contributor )
  .where( :publication_contributors => {:contributor_type => "Author"}, 
          :contributors             => {:name => params[:authors]} ) 

ActiveRecord api 在这里不是非常一致:where 的参数与 joins 的参数不完全相同.这是因为 joins 的参数 不 反映底层 SQL，而 where 的参数反映底层 SQL.

ActiveRecord api is not extremely consistent here : the arguments for where do not work exactly as those for joins. This is because the arguments for joins do not reflect the underlying SQL, whereas the arguments for where do.

where 接受一个散列，其键是表名，值是散列(它们本身以列名作为键).它只是在定位两个表中具有相同名称的列时防止歧义.

where accepts an hash whose keys are table names, and values are hashes (that themselves have column names as keys). It just prevents ambiguity when targetting a column that has the same name in two tables.

这也解释了为什么会出现第二个问题:authors 关系不存在.

This also explains why your second problem arises : the relation authors does not exist.

这篇关于Rails:找到与 where 子句的深层嵌套关联的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！