Rails - Sep 2006 - 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

--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups
"Ruby on Rails: Talk" group.
To post to this group, send email to
rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
To unsubscribe from this group, send email to
rubyonrails-talk-unsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
For more options, visit this group at
http://groups.google.com/group/rubyonrails-talk
-~----------~----~----~----~------~----~------~--~---

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

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


--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups
"Ruby on Rails: Talk" group.
To post to this group, send email to
rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
To unsubscribe from this group, send email to
rubyonrails-talk-unsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
For more options, visit this group at
http://groups.google.com/group/rubyonrails-talk
-~----------~----~----~----~------~----~------~--~---

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

On 9/24/06, Mark Reginald James
<mrj-bzGI/hKkdgQnC9Muvcwxkw@public.gmane.org>
wrote:
>
> 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
>
> --
> We develop, watch us RoR, in numbers too big to ignore.
>
>
> >
>
--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups
"Ruby on Rails: Talk" group.
To post to this group, send email to
rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
To unsubscribe from this group, send email to
rubyonrails-talk-unsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
For more options, visit this group at
http://groups.google.com/group/rubyonrails-talk
-~----------~----~----~----~------~----~------~--~---

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

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


--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups
"Ruby on Rails: Talk" group.
To post to this group, send email to
rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
To unsubscribe from this group, send email to
rubyonrails-talk-unsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
For more options, visit this group at
http://groups.google.com/group/rubyonrails-talk
-~----------~----~----~----~------~----~------~--~---

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

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


--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups
"Ruby on Rails: Talk" group.
To post to this group, send email to
rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
To unsubscribe from this group, send email to
rubyonrails-talk-unsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
For more options, visit this group at
http://groups.google.com/group/rubyonrails-talk
-~----------~----~----~----~------~----~------~--~---

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

Cheers

Hammed

On 9/25/06, Mark Reginald James
<mrj-bzGI/hKkdgQnC9Muvcwxkw@public.gmane.org>
wrote:
>
> 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
>
> --
> We develop, watch us RoR, in numbers too big to ignore.
>
>
> >
>
--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups
"Ruby on Rails: Talk" group.
To post to this group, send email to
rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
To unsubscribe from this group, send email to
rubyonrails-talk-unsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
For more options, visit this group at
http://groups.google.com/group/rubyonrails-talk
-~----------~----~----~----~------~----~------~--~---

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

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


--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups
"Ruby on Rails: Talk" group.
To post to this group, send email to
rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
To unsubscribe from this group, send email to
rubyonrails-talk-unsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
For more options, visit this group at
http://groups.google.com/group/rubyonrails-talk
-~----------~----~----~----~------~----~------~--~---

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.

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

--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups
"Ruby on Rails: Talk" group.
To post to this group, send email to
rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
To unsubscribe from this group, send email to
rubyonrails-talk-unsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
For more options, visit this group at
http://groups.google.com/group/rubyonrails-talk?hl=en
-~----------~----~----~----~------~----~------~--~---