# Like conditions on associated tables (nested selects)

**URL:** <https://www.ruby-forum.com/t/like-conditions-on-associated-tables-nested-selects/160534>\
**Category:** Rails\
**Created:** [February 17, 2009, 2:50pm UTC](https://www.ruby-forum.com/t/like-conditions-on-associated-tables-nested-selects/160534 "2009-02-17T14:50:55Z")\
**Posts on this page:** 3\
**Page:** 1

<div class="post-metadata">

**Author:** ![sa125](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/s/b4bc9f/32.png) [@sa125](https://www.ruby-forum.com/u/sa125)\
**Post date:** [February 17, 2009, 2:50pm UTC](https://www.ruby-forum.com/t/like-conditions-on-associated-tables-nested-selects/160534/1 "2009-02-17T14:50:55Z")

</div>

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?

---

<div class="post-metadata">

**Author:** ![sa125](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/s/b4bc9f/32.png) [@sa125](https://www.ruby-forum.com/u/sa125)\
**Post date:** [February 17, 2009, 3:07pm UTC](https://www.ruby-forum.com/t/like-conditions-on-associated-tables-nested-selects/160534/2 "2009-02-17T15:07:11Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![sa125](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/s/b4bc9f/32.png) [@sa125](https://www.ruby-forum.com/u/sa125)\
**Post date:** [February 18, 2009, 1:21pm UTC](https://www.ruby-forum.com/t/like-conditions-on-associated-tables-nested-selects/160534/3 "2009-02-18T13:21:11Z")

</div>

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