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
--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups
"Ruby on Rails: Core" group.
To post to this group, send email to rubyonrails-core@googlegroups.com
To unsubscribe from this group, send email to
rubyonrails-core-unsubscribe@googlegroups.com
For more options, visit this group at
http://groups.google.com/group/rubyonrails-core?hl=en
-~----------~----~----~----~------~----~------~--~---
On 7/4/07, Tarmo Tänav <tarmo@itech.ee> wrote:> > > 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/6924I''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. ;) 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 --~--~---------~--~----~------------~-------~--~----~ You received this message because you are subscribed to the Google Groups "Ruby on Rails: Core" group. To post to this group, send email to rubyonrails-core@googlegroups.com To unsubscribe from this group, send email to rubyonrails-core-unsubscribe@googlegroups.com For more options, visit this group at http://groups.google.com/group/rubyonrails-core?hl=en -~----------~----~----~----~------~----~------~--~---
On K, 2007-07-04 at 20:55 +1000, Daniel N wrote:> 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. ;) > > 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.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 received this message because you are subscribed to the Google Groups "Ruby on Rails: Core" group. To post to this group, send email to rubyonrails-core@googlegroups.com To unsubscribe from this group, send email to rubyonrails-core-unsubscribe@googlegroups.com For more options, visit this group at http://groups.google.com/group/rubyonrails-core?hl=en -~----------~----~----~----~------~----~------~--~---
On 7/4/07, Tarmo Tänav <tarmo@itech.ee> wrote:> > 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"...).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 --~--~---------~--~----~------------~-------~--~----~ You received this message because you are subscribed to the Google Groups "Ruby on Rails: Core" group. To post to this group, send email to rubyonrails-core@googlegroups.com To unsubscribe from this group, send email to rubyonrails-core-unsubscribe@googlegroups.com For more options, visit this group at http://groups.google.com/group/rubyonrails-core?hl=en -~----------~----~----~----~------~----~------~--~---
On K, 2007-07-04 at 11:49 -0700, Jeremy Evans wrote:> 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). > > JeremyI 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? --~--~---------~--~----~------------~-------~--~----~ You received this message because you are subscribed to the Google Groups "Ruby on Rails: Core" group. To post to this group, send email to rubyonrails-core@googlegroups.com To unsubscribe from this group, send email to rubyonrails-core-unsubscribe@googlegroups.com For more options, visit this group at http://groups.google.com/group/rubyonrails-core?hl=en -~----------~----~----~----~------~----~------~--~---
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 On Jul 4, 6:39 am, Tarmo Tänav <t...@itech.ee> wrote:> 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--~--~---------~--~----~------------~-------~--~----~ You received this message because you are subscribed to the Google Groups "Ruby on Rails: Core" group. To post to this group, send email to rubyonrails-core@googlegroups.com To unsubscribe from this group, send email to rubyonrails-core-unsubscribe@googlegroups.com For more options, visit this group at http://groups.google.com/group/rubyonrails-core?hl=en -~----------~----~----~----~------~----~------~--~---
On 7/5/07, Chris Cruft <cch1@hapgoods.com> wrote:> > 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?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 --~--~---------~--~----~------------~-------~--~----~ You received this message because you are subscribed to the Google Groups "Ruby on Rails: Core" group. To post to this group, send email to rubyonrails-core@googlegroups.com To unsubscribe from this group, send email to rubyonrails-core-unsubscribe@googlegroups.com For more options, visit this group at http://groups.google.com/group/rubyonrails-core?hl=en -~----------~----~----~----~------~----~------~--~---