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 = JOIN addresses work_address ON (people.work_address_id = LEFT JOIN addresses work_address_2 ON (people.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 => "{} = 'Belgium' ", :order => "{home_address.street_name}" ) Person.find(:all, :include => :valiases, :conditions => "{} = 'bduc ", :order => "{}" )

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 ; 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: