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?
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%'"