Limitations of :conditions on associations

This is an issue also described in rails ticket #6924 [1]

Simple :conditions on associations work very well when associations
are loaded separately, but they don't work so well when eager
loading is used and either several tables use the same field name
or the more extreme case of the same table appearing more than
once in the SQL (self-referential associations).

The first case can be worked around by just refering to the table
attributes in :conditions through their table name, and ActiveRecord
even does it automatically when the conditions are specified
as a hash.

In the second case the workarounds are more limited, it is possible
to find out how activerecord aliases a table in a specific case so
in the :conditions you can use that alias but this association would
only work in that one specific case, it can not be eager loaded in
any way that would produce a different alias and whats worse it
can not be loaded in the normal way (without eager loading).

It's clear that not all association options can be supported for
eager loading so one could argue that :conditions being limited
in this way is fine but I think this could be fixed.

What is needed is a way to refer to the table of the current
association in :conditions in a way that allows ActiveRecord to
replace those references with the real table name or alias. In case
of conditions as a hash this would require no syntax changes at
all but in other cases it would require a special marker, like the
one used for named or positional bind
variables (":table", ":current_table", ":association"...).

Do you think this should be fixed?
Have I described a solution that would be acceptable?

Thanks in advance for any responses,

[1] http://dev.rubyonrails.org/ticket/6924

I’ve been thinking about this issue myself and have thought of how I would like to use it, but I have not worked out how I could patch it. I hope I’m not way off the conversation here. :wink:

Basically I have been thinking that if a hash is passed to the conditions option whose keys are either the current table, or one of the included associations then the conditions for that association should be scoped appropriately

eg.

User.find( :all, :include => :articles, :conditions => { :users => { :active => true } ,
:articles => { :active => true,
:published => true } } )

If the conditions hash is an array or does not include keys for active associations then it should behave as current.

User.find( :all, :include => :articles, :conditions => { :active => true } ) #=> Should be scoped to user

Is this inline with what your saying? Sorry if it’s not.

Daniel

Yes, though I still think that there should also be a fix
for this problem when using non-hash conditions because hash
can only do simple conditions (IS, IN, =, BETWEEN).

As for how to fix it, one way would be to extend the sanitize_sql
method to do more pre- or post-processing on the query. It should
be possible to provide the real (aliased) table name to sanitize_sql.

You can already do what you want (assuming it hasn't changed since
1.2.3), but it is ugly:

  :conditions=>'#{"#{aliased_table_name}." rescue ""}attribute = value'

You need the rescue because aliased_table_name isn't available when
doing lazy loading (it used to be before Rails 1.2).

Jeremy

I actually tried this but without the rescue. It indeed appears to work,
at least with edge rails.

But still, shouldn't there be a more natural way for doing this?

I've run into problems trying to anticipate the aliased table name as
well. I think Rails should have a more consistent way to refer to
aliased table names. Perhaps a parameter to the association
definition?

class Employee < AR::Base
  ...
  has_many :subordinates, :class_name => 'Employee', :sql_alias =>
'subordinates', :conditions => 'subordinates.active is true'
  has_one :boss, :class_name => 'Employee', :sql_alias =>
'bosses', :conditions => 'bosses.active is true'
  ...
end

The problem isn't determining the table alias during eager loading (it's
aliased_table_name), the problem is setting it correctly during lazy
loading. Some of the internal API needs to be changed to support it.
I think ActiveRecord::Base#interpolate_sql is the function that
evaluates the strings, so that is the function that needs to supply
aliased_table_name. It works for eager loading because eager loading
uses ActiveRecord::Associations::ClassMethods::JoinDependency::JoinAssociation#interpolate_sql.

Jeremy