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.