I''m trying to use the :conditions option on the activerecord find. For
a
simple query such as "SELECT * FROM products WHERE name LIKE
''%#{name}%''" it''s obviously
:conditions => ["name LIKE ?",
"''%#{name}%''"]
However, I have associated models, so for example I might have
product.location.address to access in the view, but only a location_id
field in the products table.
To find a product based on location address in MySQL (say, from a search
box saved in variable address), I might perform this query:
"SELECT * FROM products WHERE location_id in (SELECT distinct id FROM
locations WHERE address LIKE ''%#{address}%'')"
So, I have nested select statements. How can I perform this query inside
the :conditions option? Is there a simpler and secure way to accomplish
what I need?
--
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
-~----------~----~----~----~------~----~------~--~---
Frederick Cheung
2009-Feb-17 14:06 UTC
Re: like conditions on associated tables (nested selects)
On 17 Feb 2009, at 13:50, sa 125 wrote:> > I''m trying to use the :conditions option on the activerecord find. > For a > simple query such as "SELECT * FROM products WHERE name LIKE > ''%#{name}%''" it''s obviously > > :conditions => ["name LIKE ?", "''%#{name}%''"] > > However, I have associated models, so for example I might have > product.location.address to access in the view, but only a location_id > field in the products table. > > To find a product based on location address in MySQL (say, from a > search > box saved in variable address), I might perform this query: > > "SELECT * FROM products WHERE location_id in (SELECT distinct id FROM > locations WHERE address LIKE ''%#{address}%'')"a subselect like this is usually better written as a join (which is probably what the db does behind the scenes ie SELECT products.* from products inner join locations on locations.id = location_id where address like ''%foo%'' which will be produced by Product.find :all, :joins => :location, :conditions =>"address like ''%foo%''" Fred> > > So, I have nested select statements. How can I perform this query > inside > the :conditions option? Is there a simpler and secure way to > accomplish > what I need? > -- > 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 -~----------~----~----~----~------~----~------~--~---
Thanks, this is just what I wanted.> a subselect like this is usually better written as a join (which is > probably what the db does behind the scenes ie > SELECT products.* from products > inner join locations on locations.id = location_id > where address like ''%foo%'' > > which will be produced by Product.find :all, :joins > => :location, :conditions =>"address like ''%foo%''" > > Fred-- 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 -~----------~----~----~----~------~----~------~--~---