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

[Hammed Malik]

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

[Mark Reginald James]

Try:

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

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

--
We develop, watch us RoR, in numbers too big to ignore.

[Hammed Malik]

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

[Mark Reginald James]

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]

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 = '...'

[Hammed Malik]

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

Cheers

Hammed

[Mark Reginald James]

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

[JW]

Hi Mark,

Thank you _so_ much for your code and your return trip to update/fix it
above as well. We have a large schema with tons of lookup tables
defining entity type/kind and the problem as original descibed has come
up a bunch.

Your code above DOES work as described. I just set this up and was able
to do:

Person.with_conditions(:addresses,
'addresses_mailing.id_t_addresses_mailing = 2') do
@people = Person.find :all, :include => [{:addresses =>
:t_address}, :email_addresses], :conditions => "name_last like 'W%'",
:limit => 15
end

which, includes multiple other associations, a limit clause and some
WHERE clause action as well.

All best!

--
Posted via http://www.ruby-forum.com/.