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