Like conditions on associated tables (nested selects)

Rails
1

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?

2

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

3

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