How to specify condition for eagerly joined model using :inc

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

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 M. wrote:

addresses.*
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


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

Mark Reginald J. 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


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

Hammed M. 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


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

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

Cheers

Hammed

Mark Reginald J. 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


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

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!

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


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

This forum is not affiliated to the Ruby language, Ruby on Rails framework, nor any Ruby applications discussed here.

| Privacy Policy | Terms of Service | Remote Ruby Jobs