Forum: Ruby on Rails 1.2RC2: Broken Table Aliasing for Associations

Announcement (2017-05-07): www.ruby-forum.com is now read-only since I unfortunately do not have the time to support and maintain the forum any more. Please see rubyonrails.org/community and ruby-lang.org/en/community for other Rails- und Ruby-related community platforms.
Brian (Guest)
on 2007-01-10 04:21
(Received via mailing list)
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.
This topic is locked and can not be replied to.