ActiveRecord Conditions Rework Needed

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

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'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 :slight_smile: