Databases
StackOverflow2013
Postsdbo
PORails: Finding a deeply nested association with a where clause
Body
 

Note that there are some explanatory texts on larger screens.

plurals
  1. PORails: Finding a deeply nested association with a where clause
    text
    copied!<p>I have two models joined with a has_many :through relationship:</p> <pre><code>class Publication &lt; ActiveRecord::Base has_many :publication_contributors has_many :contributors, :through =&gt; :publication_contributors end class Contributor &lt; ActiveRecord::Base has_many :publication_contributors has_many :publications, :through =&gt; :publication_contributors end class PublicationContributor &lt; ActiveRecord::Base belongs_to :publication belongs_to :contributor end </code></pre> <p>(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.)</p> <p>I want to find a Publication that has specific contributors like so:</p> <pre><code>Publication .joins(:publication_contributors =&gt; :contributor) .where(:publication_contributors =&gt; {:contributor_type =&gt; "Author", :contributor =&gt; {:name =&gt; params[:authors]}}) </code></pre> <p>Everything works fine until I get to the nested :contributor, at which point the SQL sputters:</p> <pre><code>Mysql2::Error: Unknown column 'publication_contributors.contributor' in 'where clause' </code></pre> <p>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?</p> <p>UPDATE:</p> <p>The generated SQL</p> <pre><code>←[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 </code></pre> <p>Also, I have this association in my Publications model:</p> <pre><code>has_many :authors, :through =&gt; :publication_contributors, :source =&gt; :contributor, :conditions =&gt; {:publication_contributors =&gt; {:contributor_type =&gt; "Author"}} </code></pre> <p>I was thinking that I could do this:</p> <pre><code>Publication.joins(:authors).where(:authors =&gt; {:name =&gt; params[:authors]}) </code></pre> <p>But that throws the error:</p> <pre><code>Mysql2::Error: Unknown column 'authors.name' in 'where clause' </code></pre>
 

Querying!

 
Guidance
An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload