### The Problem - Issues with ActiveRecord nested sql conditions on
associated tables
# ActiveRecord does not allow anyone to perform complex searches
across multiple models easily. Currently in rails you are expected to
know what the
# table name of the relationship you want to use is going to be
aliased too. There are several problems with this:
# * It''s not intuitive to the developer about which table name to use
in the sql conditions (without reading ar docs clearly)
# * It makes it difficult to generate advanced search forms, which can
dynamically include or exclude queries
# To demonstrate the issue lets have a look at the following example:
class Person < ActiveRecord::Base
# Everybody has a home address
belongs_to :home_address,
:class_name => ''Address'',
:foreign_key => ''home_address_id''
# Everybody has a work address
belongs_to :work_address,
:class_name => ''Address'',
:foreign_key => ''work_address_id''
# Only some people have a second work address
belongs_to :work_address_2,
:class_name => ''Address'',
:foreign_key => ''work_address_2_id''
end
class Address < ActiveRecord::Base
# street_no (string)
# street_name (string)
end
# Lets say that we want to find all the people who has a home_address
that has a street_number starting with 22. With the rails 2.3
Person.find(
:all,
:joins => [:home_address, :work_address],
:include => [:work_address_2]
:conditions => {
:address => {:street_no => ''22''}
}
)
# The problem with this is, we have to know the name of the table
rails is going to alias too. In this case address. What rails
currently does is simply do a simple change
# :address => {:street_no => ''22''} into
"addresses.street_no = ''22''"
If we then want to change to search on work_address_2 or work_address
we have to either re-order the joins and includes, or guess what the
table name is going to be.
An additional issue to this is again, there is no way of writing
custom SQL for the association tables.
### The solution
Currently to solve these issues, I use my own sql generator to which i
pass :includes, :joins and a hash of conditions to produce the sql. I
believe a better solution
would be to change activerecord to handle this.
A good solution would be to use the relationships name, rather than
the tables name. For example the same query above could be written as:
Person.find(
:all,
:joins => [:home_address, :work_address],
:include => [:work_address_2]
:conditions => {
:home_address => {:street_no => ''22''}
}
)
# This would need to work across all cases including:
# :joins
# :includes
# or if the user did not include either of these
The first changes we would need to make would be to:
* Alias tables to their relationship names
* rework nested conditions to use the aliased names
* Consider "auto-joining" tables if the user provides specific
conditions
SELECT *
FROM people
JOIN addresses home_address ON (people.home_address_id home_address.id)
JOIN addresses work_address ON (people.work_address_id work_address.id)
LEFT JOIN addresses work_address_2 ON (people.work_address_2_id
work_address_2.id)
WHERE home_address.street_no = ''22''
The next change we would need is to be able to support custom sql
# Custom SQL for each nesting, so that you can use any sql but not
have to explicitly know the name of the table would mean it would be
easy to write dynamic conditions for the sql
scope = Person.scoped({})
scope.scoped({
:conditions => "home_address LIKE ''candy%''"
:join => :home_address
}) if params[''search_for_home_address_candy'']
scope.scoped({
:conditions => "work_address LIKE ''candy%''"
:join => :work_address
}) if params[''search_for_work_address_candy'']
This would pave the way for easy generation of very complex sql logic
generation. There will be issues involving backwards compatibility
and complexity of code, but if my rubbishy little plugin can manage
this, i''m sure activerecord can.
I''d like to get feedback into pitfulls i might run into and whether
anyone has any better ideas before sinking many hours into it.
I''d love to hear feedback... so give it to me!
--~--~---------~--~----~------------~-------~--~----~
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 had the same problem (and opinion), I wrote a plugin last year to
solve this issue, and called it virtual aliases.
I solved two issues with it:
1. make :select work even when eager loading (:include ); you can thus
limit the fields that are requested in the query ; objects are created
but fields not requested are not present in the object.
2. be able to specify conditions/order/group in a way that you can
access relations on ActiveRecord.
To extend your example (added country, and creator)
class Person < ActiveRecord::Base
# Everybody has a home address
belongs_to :home_address,:class_name => ''Address'',
:foreign_key =>
''home_address_id''
# Everybody has a work address
belongs_to :work_address, :class_name => ''Address'',
:foreign_key
=> ''work_address_id''
# Only some people have a second work address
belongs_to :work_address_2, :class_name => ''Address'',
:foreign_key
=> ''work_address_2_id''
belongs_to :creator, : class_name => ''User''
end
class Address < ActiveRecord::Base
belongs_to :creator, : class_name => ''User''
belongs_to :country
# street_no (string)
# street_name (string)
end
class Country
belongs_to :creator, : class_name => ''User''
# name
end
These will all work and do the necessary joins automatically :
Person.find(:all, :include => :valiases, :conditions =>
"{work_address.country.name} = ''Belgium'' ", :order
=>
"{home_address.street_name}" )
Person.find(:all, :include => :valiases, :conditions =>
"{work_address.creator.name} = ''bduc ", :order =>
"{home_address.country.creator.name}" )
All valiases are placed between curly braces and replaced by the
correct real aliases activerecord makes up for then.
Currently used and tested on rails 2.2
The code is at dev.dyndaco.com ; but you cannot checkout the code yet;
only view it (will make checkout available later this month).
There currently is not much documentation and there are no tests
included; that''s way I don''t make to much fuss about it.
Apart from that , it works great !
If you''re intrested let me know.
--~--~---------~--~----~------------~-------~--~----~
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
-~----------~----~----~----~------~----~------~--~---
You''ve got a very interesting solution. My only issue with it is the syntax (i.e. :include => :valiases). For a patch to be included to core, we need to adapt it so that it fits in with the current :include, :joins etc system. However i might just use your plugin if nobody is interested in pushing something like this to core Additionally we should also consider a solution that is going to allow conditions to work with named_scopes, i.e. adding more and more conditions to the relationship tables. I''ll try and come up with some more ideas after i''ve had time to understand your codes :) On Mar 16, 10:56 pm, bduc <b...@dyndaco.com> wrote:> I had the same problem (and opinion), I wrote a plugin last year to > solve this issue, and called it virtual aliases. > > I solved two issues with it: > > 1. make :select work even when eager loading (:include ); you can thus > limit the fields that are requested in the query ; objects are created > but fields not requested are not present in the object. > 2. be able to specify conditions/order/group in a way that you can > access relations on ActiveRecord. > > To extend your example (added country, and creator) > > class Person < ActiveRecord::Base > # Everybody has a home address > belongs_to :home_address,:class_name => ''Address'', :foreign_key => > ''home_address_id'' > > # Everybody has a work address > belongs_to :work_address, :class_name => ''Address'', :foreign_key > => ''work_address_id'' > > # Only some people have a second work address > belongs_to :work_address_2, :class_name => ''Address'', :foreign_key > => ''work_address_2_id'' > belongs_to :creator, : class_name => ''User'' > end > > class Address < ActiveRecord::Base > belongs_to :creator, : class_name => ''User'' > belongs_to :country > # street_no (string) > # street_name (string) > end > > class Country > belongs_to :creator, : class_name => ''User'' > # name > end > > These will all work and do the necessary joins automatically : > > Person.find(:all, :include => :valiases, :conditions => > "{work_address.country.name} = ''Belgium'' ", :order => > "{home_address.street_name}" ) > Person.find(:all, :include => :valiases, :conditions => > "{work_address.creator.name} = ''bduc ", :order => > "{home_address.country.creator.name}" ) > > All valiases are placed between curly braces and replaced by the > correct real aliases activerecord makes up for then. > > Currently used and tested on rails 2.2 > > The code is at dev.dyndaco.com ; but you cannot checkout the code yet; > only view it (will make checkout available later this month). > There currently is not much documentation and there are no tests > included; that''s way I don''t make to much fuss about it. > Apart from that , it works great ! > > If you''re intrested let me know.--~--~---------~--~----~------------~-------~--~----~ 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 -~----------~----~----~----~------~----~------~--~---