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