#2357 Predictable Table Aliases in Joins - Ruby on Rails

Type	To find
responsible:me	tickets assigned to you
tagged:"@high"	tickets tagged @high
milestone:next	tickets in the upcoming milestone
state:invalid	tickets with the state invalid
created:"last week"	tickets created last week
sort:number, importance, updated	tickets sorted by #, importance or updated
Combine keywords for powerful searching.
Use advanced searching »

This project is archived and is in readonly mode.

#2357 new

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


ActiveRecord::Base.table_alias_for(*path)

# Example:
company_alias = User.table_alias_for(:home_branch, :company)
User.some_scope.find(:all, 
    :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

I.e.


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

Thoughts?

Comments and changes to this ticket

You flagged this item as spam.
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.
You flagged this item as spam.
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...
```
Blah.find(:all,
  :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).
```
User.some_scope.find(:all, 
    :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.)
- base.patch 7.6 KB
You flagged this item as spam.
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:
```
User.some_scope.find(:all,
    :conditions => ActiveRecord.condition {
        {:home_branch => :company => {:name => like {search}}}})
```
You flagged this item as spam.
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.
You flagged this item as spam.
wtn September 28th, 2009 @ 11:18 PM
You should check out arel:

http://github.com/nkallen/arel

I think Emilio's arel branch will get merged into ActiveRecord at some point.
You flagged this item as spam.
wtn September 28th, 2009 @ 11:22 PM
- Assigned user set to “Michael Koziarski”
This looks like a dupe of #2087

https://rails.lighthouseapp.com/projects/8994-ruby-on-rails/tickets...

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 May 4th, 2010 @ 06:48 PM
- Milestone changed from “2.x” to “3.x”
[bulk edit]
You flagged this item as spam.
tankwanghow February 18th, 2011 @ 02:12 PM
Yes Predictable table aliasing in needed in activerecord
You flagged this item as spam.
bingbing March 26th, 2011 @ 02:08 AM
louis vuitton watches