query to search table that has many in another table

rubyonrails-talk
1

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

2

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])