### 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 [email protected]
To unsubscribe from this group, send email to
[email protected]
For more options, visit this group at
http://groups.google.com/group/rubyonrails-core?hl=en
-~----------~----~----~----~------~----~------~--~---
