Forum: Ruby on Rails like conditions on associated tables (nested selects)

Announcement (2017-05-07): www.ruby-forum.com is now read-only since I unfortunately do not have the time to support and maintain the forum any more. Please see rubyonrails.org/community and ruby-lang.org/en/community for other Rails- und Ruby-related community platforms.
7db3b1680807d393d40260591b4ae5e0?d=identicon&s=25 sa 125 (sa125)
on 2009-02-17 14:50
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?
81b61875e41eaa58887543635d556fca?d=identicon&s=25 Frederick Cheung (Guest)
on 2009-02-17 15:07
(Received via mailing list)
On 17 Feb 2009, at 13:50, sa 125 wrote:

> 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
7db3b1680807d393d40260591b4ae5e0?d=identicon&s=25 sa 125 (sa125)
on 2009-02-18 13:21
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
This topic is locked and can not be replied to.