### 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!
