Predictable Table Aliases in Joins

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

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}}}})

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 should check out arel:

http://github.com/nkallen/arel

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

Yes Predictable table aliasing in needed in activerecord

louis vuitton watches