I haven’t found this issue reported here or at dev.rubyonrails.org, so I’m sending out this description of what I’ve encountered to see if anyone else thinks it’s a problem.
Summary: When referencing an association multiple times using the :include option to find, the expected behavior as described in the API is to join on the associated table multiple times using aliases. Rails 1.1.6 conforms to this behavior, 1.2RC2 does not.
Discussion:
The API for ActiveRecord Associations has this to say under Table Aliasing:
ActiveRecord uses table aliasing in the case that a table is referenced multiple times in a join. If a table is referenced only once, the standard table name is used. The second time, the table is aliased as #{reflection_name}_#{parent_table_name}. Indexes are appended for any more successive uses of the table name.
It can be very useful to join on an associated table more than once. For simplicity, let’s say I have:
class Party < ActiveRecord::Base
acts_as_taggable
end
…and I want to find all records having both of the tags “Ruby” and “Rails”. A Ruby solution might be a simple array intersection:
tagged_ruby = Tag.find_by_name(‘Ruby’).tagged tagged_rails = Tag.find_by_name(‘Rails’).tagged tagged_both = tagged_ruby & tagged_rails
But what if I want to do this in a single database query? (I might be concerned about efficiency, for example, if I’m building an interactive query tool that’s dealing with much more than Tags…) In Rails 1.1.6, following the rules for table aliasing cited above, I can do the following:
opts = {} opts[:conditions] = [“tags.name = ? AND tags_parties.name = ?”, “Ruby”, “Rails”] opts[:include] = [:tags, :tags]
tagged_ruby_and_rails = Party.find(:all, opts)
The executed SQL returns the expected results and looks something like this:
SELECT parties.id AS … (etc),
tags.id AS t1_r0, tags.name AS t1_r1,
tags_parties.id AS t2_r0, tags_parties.name AS t2_r1
FROM parties
LEFT OUTER JOIN taggings ON (taggings.taggable_id =
parties.id AND taggings.taggable_type = ‘Party’)
LEFT OUTER JOIN tags ON tags.id = taggings.tag_id
LEFT OUTER JOIN taggings tags_parties_join ON (tags_parties_join.taggable_id = parties.id AND tags_parties_join.taggable_type = ‘Party’)
LEFT OUTER JOIN tags tags_parties ON tags_parties.id = tags_parties_join.tag_id
WHERE (
tags.name = ‘Ruby’) AND (tags_parties.name = ‘Rails’)
In Rails 1.2RC2, however, the same code throws an error.
ActiveRecord::StatementInvalid: Mysql::Error: #42S22Unknown column ‘tags_parties.name’ in ‘where clause’:
SELECT <…>,
tags.id AS t1_r0, tags.name AS t1_r1 FROM parties
LEFT OUTER JOIN taggings ON (taggings.taggable_id = parties.id AND taggings.taggable_type
= ‘Party’)
LEFT OUTER JOIN tags ON tags.id = taggings.tag_id
WHERE (tags.name = ‘Ruby’)
AND (tags_parties.name = ‘Rails’)
Note that the SQL executed under Rails 1.1.6 has four LEFT OUTER JOINs, while Rails 1.2RC2 seems to think that two are sufficient.
I haven’t looked at the code to see what’s different: right now I’m limiting myself to describing the changed behavior, which seems to conflict with the API and will make it harder to do certain kinds of complex queries via ActiveRecord.
Thanks,
Brian Green