# Query to search table that has many in another table

**URL:** <https://www.ruby-forum.com/t/query-to-search-table-that-has-many-in-another-table/140327>\
**Category:** Rails\
**Created:** [June 13, 2008, 12:01am UTC](https://www.ruby-forum.com/t/query-to-search-table-that-has-many-in-another-table/140327 "2008-06-13T00:01:31Z")\
**Posts on this page:** 2\
**Page:** 1

<div class="post-metadata">

**Author:** ![skirkpatrick](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/s/73ab20/32.png) [@skirkpatrick](https://www.ruby-forum.com/u/skirkpatrick)\
**Post date:** [June 13, 2008, 12:01am UTC](https://www.ruby-forum.com/t/query-to-search-table-that-has-many-in-another-table/140327/1 "2008-06-13T00:01:31Z")

</div>

i have 3 tables

ITEMS  
id  
name  
…

COMPOUNDS has many compound\_materials  
id  
item\_id  
…

COMPOUND\_MATERIALS belongs to compounds  
id  
item\_id  
compound\_id  
…

i am trying to do a search to find all compounds or compounds that have  
commpound materials that match a search for name

here is what i want to do but it i think i need some help. i’m not sure  
if this is even possible without doing multiple queries

class Compound \< ActiveRecord::Base

```
def name
  item.name
end

def self.search(search, page)
        paginate :per_page => 100, :page => page,
       :include => [:item, :compound_materials]
       :conditions => ['name like ? OR compound_materials.item.name

```

like ?', “%#{search}%”, “%#{search}%”],  
:order =\> ‘name’  
end  
end

can someone point me in the right direction? thanks!

here is a link to my site so you can see what i mean:

[http://wl.kuliksco.com/compounds](http://wl.kuliksco.com/compounds)

---

<div class="post-metadata">

**Author:** ![skirkpatrick](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/s/73ab20/32.png) [@skirkpatrick](https://www.ruby-forum.com/u/skirkpatrick)\
**Post date:** [June 13, 2008, 12:25am UTC](https://www.ruby-forum.com/t/query-to-search-table-that-has-many-in-another-table/140327/2 "2008-06-13T00:25:14Z")

</div>

i forgot to mention that compounds and compound\_materials both belong to  
items

and here is the code from my compounds controller

```
@compounds = Compound.search(params[:search], params[:page])
```