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

rubyonrails-talk
1

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

2

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

3

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

4

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

5

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

6

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

Cheers

Hammed

7

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