Rails: Finding a deeply nested association with a where clause

Question

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

(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]}})

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'

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?

UPDATE:

The generated 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

Also, I have this association in my Publications model:

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

I was thinking that I could do this:

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

But that throws the error:

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

Answer 1

try to change your where clause :

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

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 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.

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