This project is archived and is in readonly mode.

#2357 new
Steffen Bartsch

Predictable Table Aliases in Joins

Reported by Steffen Bartsch | March 27th, 2009 @ 02:36 PM | in 3.x

Right now, ActiveRecord assigns table aliases for association joins fairly unpredictable. The first association to a certain table keeps the table name. Further joins with associations to that table use aliases consisting of the association names in the path.

With the great flexibility of named_scoped, it is increasingly common for app developers not to know about already existing joins at coding time. Also, the order of joins in a Hash is undefined in Ruby 1.8.

Table aliases are needed, though, for building conditions on the association tables. With our plugin declarative_authorization, which is using named_scopes to rewrite queries according to permissions, we frequently run into problems because of unpredictable table aliases.

I'd like to propose (and am willing to implement) the following simple API for querying the expected table alias for certain association join path, to be used by app developers


# Example:
company_alias = User.table_alias_for(:home_branch, :company)
    :joins      => {:home_branch => :company}, 
    :conditions => ["#{company_alias}.name LIKE ?", search])

The actual algorithm for determining the table alias should work as follows:

  • All joins receive a table alias according to the join path
  • Association names in a path are concatenated into a long alias, i.e. :home_branch, :company => "home_branch_company"
  • Aliases exceeding the max alias name length are shortened to (max_length - 9) and concatenated with an underscore and 8 characters from the long alias string hash in hex


long_alias = join_path * "_"
table_alias = long_alias[0, max_table_alias_length - 9] + 
    "_" + ("%x" % long_alias.hash)[0,8]


Comments and changes to this ticket

  • ronin-38963 (at lighthouseapp)

    ronin-38963 (at lighthouseapp) April 25th, 2009 @ 01:50 AM

    • Tag changed from activerecord, joins, table_alias to activerecord, joins, named_scope, table_alias

    This should definitely be addressed. It's a huge issue for named_scope use, as you stated.

  • Brian Langenfeld

    Brian Langenfeld April 26th, 2009 @ 08:03 AM

    Good to see you again, Steffen!

    I recently did some screwing around in ActiveRecord base.rb to make :conditions "follow along" with the :joins specified in find. So you can do something like this...

      :joins => {:foo => {:bar => {:baz => :foos}}},
      :conditions => {:foo => {:bar => {:baz => {:foos => {:name => 'Qux'}}}}}

    ...and the generated SQL will have the correct table alias for :foos (probably something like blahs_foos). The conditions can be arbitrarily complex, as long as your attribute and association names are correct and all referenced associations are included in :joins.

    This patch doesn't really do what Steffen requested, but it might help some users. It makes :conditions behave in such a way that you don't really need to know the table aliases in the first place.

    Of course, if you want to test for anything but equality (like in Steffen's example, using a LIKE), this patch isn't going to help. I wonder if we could/should get the job done by also amending find to make :conditions able to handle things other than equality (e.g. like, not_like, between, not_between).

        :joins      => {:home_branch => :company}, 
        :conditions => {:home_branch => :company => {:name => like {search}}})

    Patch attached. It doesn't include any new tests, but I did make sure that none of the existing ActiveRecord tests broke. (Tested against sqlite3 only.)

  • Steffen Bartsch

    Steffen Bartsch April 26th, 2009 @ 11:16 AM

    I'd say, we should have both, a way of predicting table aliases for complex named_scopes and an easy mechanism for nested conditions as you propose, Brian.

    The problem with the last code block in Brian's comment is that it relies on the method like to be available in the scope of the find call. Even if you added all operators as instance methods to AR (which would introduce quite a lot of noise in the models), you still wouldn't be able to use those when calling find from a controller, for instance.

    You could do something like this, but it is quite a lot of code, I'd say:

        :conditions => ActiveRecord.condition {
            {:home_branch => :company => {:name => like {search}}}})
  • ronin-38963 (at lighthouseapp)

    ronin-38963 (at lighthouseapp) June 6th, 2009 @ 11:01 AM

    I still support strongly Steffen's original report. It's easy to misuse the nested hash conditions/joins syntax and think you're doing The Right Thing, until you look at the SQL on the console.

    Joining a table twice requires a crafted joins string and a crafted conditions string, else all hell breaks loose. It really kills the joy of scoping.

    I would love to see what Frederick Cheung has to say about this.

  • wtn

    wtn September 28th, 2009 @ 11:18 PM

    You should check out arel:

    I think Emilio's arel branch will get merged into ActiveRecord at some point.

  • wtn

    wtn September 28th, 2009 @ 11:22 PM

    • Assigned user set to “Michael Koziarski”

    This looks like a dupe of #2087

    Even though you offer a solution, I think the preference is to stick with the status quo for now, so I imagine it will be marked as won't fix.

  • Jeremy Kemper

    Jeremy Kemper May 4th, 2010 @ 06:48 PM

    • Milestone changed from 2.x to 3.x
  • tankwanghow

    tankwanghow February 18th, 2011 @ 02:12 PM

    Yes Predictable table aliasing in needed in activerecord

  • bingbing

Create your profile

Help contribute to this project by taking a few moments to create your personal profile. Create your profile »

<h2 style="font-size: 14px">Tickets have moved to Github</h2>

The new ticket tracker is available at <a href=""></a>