How to specify condition for eagerly joined model using :include?

I'd like to specify conditions for a model that I'm left outer joining
using :include. A simplification of the problem:

User.find :all, :include => :addresses, :conditions =>
["addresses.is_active = 1"]

Results in the following SQL.

SELECT
    users.*,
    addresses.*
FROM users
    LEFT OUTER JOIN addresses ON addresses.user_id = users.id
WHERE (addresses.is_active = 1)

The SQL I'd like is:

SELECT
    users.*,
    addresses.*
FROM users
    LEFT OUTER JOIN addresses ON addresses.user_id = users.id AND
addresses.is_active = 1

Note the condition has been moved from the main query's WHERE clause
to the left join's condition. Any way to do this? I really don't want
to use find_by_sql.

Thanks

Hammed

Hammed Malik wrote:

I'd like to specify conditions for a model that I'm left outer joining
using :include. A simplification of the problem:

User.find :all, :include => :addresses, :conditions =>
["addresses.is_active = 1"]

Results in the following SQL.

SELECT
    users.*,
    addresses.*
FROM users
    LEFT OUTER JOIN addresses ON addresses.user_id = users.id
WHERE (addresses.is_active = 1)

The SQL I'd like is:

SELECT
    users.*,
    addresses.*
FROM users
    LEFT OUTER JOIN addresses ON addresses.user_id = users.id AND
addresses.is_active = 1

Note the condition has been moved from the main query's WHERE clause
to the left join's condition. Any way to do this? I really don't want
to use find_by_sql.

Try:

class User
   has_many :active_addresses, :className => Address,
                               :conditions => 'addresses.is_active = 1'
end

User.find :all, :include => :active_addresses

Thanks Mark.

That works for the example I quoted but I do need to specify the
conditions (which vary) when creating the query in the controller.

Hammed

Hammed Malik wrote:

That works for the example I quoted but I do need to specify the
conditions (which vary) when creating the query in the controller.

Hmm, the only way I can think of is:

class User
   has_many :conditional_addresses, :className => Address
end

User.reflect_on_association(:conditional_addresses).options[:conditions] = '...'
User.find :all, :include => :conditional_addresses

Mark Reginald James wrote:

Hmm, the only way I can think of is:

class User
   has_many :conditional_addresses, :className => Address
end

User.reflect_on_association(:conditional_addresses).options[:conditions] = '...'
User.find :all, :include => :conditional_addresses

Somewhat neater:

class User
   has_many :conditional_addresses, :className => Address do
     def conditions=(condition)
       reflection.options[:conditions] = condition
     end
   end
end

User.conditional_addresses.conditions = '...'
User.find :all, :include => :conditional_addresses

Thanks Mark. Not exactly what I was hoping for but this actually
answers another question I had.

Cheers

Hammed

Mark Reginald James wrote:

class User
   has_many :conditional_addresses, :className => Address do
     def conditions=(condition)
       reflection.options[:conditions] = condition
     end
   end
end

User.conditional_addresses.conditions = '...'
User.find :all, :include => :conditional_addresses

Just for the record, this won't work because conditional_addresses
is a User instance method, not a class method, so with this code
you'd have to write: User.new.conditional_addresses.conditions = '...'

Better would be:

class User < ActiveRecord::Base
   has_many :addresses

   def self.with_conditions(assoc, conditions)
     options = reflect_on_association(assoc).options
     orig_conditions = options[:conditions]
     options[:conditions] = conditions
     yield
     options[:conditions] = orig_conditions
   end
end

User.with_conditions(:addresses, 'addresses.is_active = 1') do
   User.find :all, :include => :addresses
end

You could even augment method_missing and add a class context
so you can instead write:

   User.with_address_conditions('addresses.is_active = 1') do
     find :all, :include => :addresses
   end