# Help with search query

**URL:** <https://www.ruby-forum.com/t/help-with-search-query/165463>\
**Category:** Rails\
**Created:** [April 14, 2009, 6:37am UTC](https://www.ruby-forum.com/t/help-with-search-query/165463 "2009-04-14T06:37:51Z")\
**Posts on this page:** 1\
**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:** [April 14, 2009, 6:37am UTC](https://www.ruby-forum.com/t/help-with-search-query/165463/1 "2009-04-14T06:37:51Z")

</div>

ok, here’s my dilemna…i have a site that has a list of item  
combinations (aka compounds) for a computer game.

i’m having a hard time coming up with a good way to search a compounds  
result and the items that are contained within the compound.

the formulas are setup like:

COMPOUND = COMPOUND\_MATERIAL + COMPOUND\_MATERIAL…(compound has up to 5  
compound\_materials)

to get a better idea of what i mean check this page from the site

> **[Compounds - Wonderland Online Database](http://wlodb.com/compounds)**
>
> Compounds - Wonderland Online Database

my table setup is:

ITEMS (which can be a compound result or compound component)  
-ID  
-name  
-description  
-…

COMPOUNDS (has\_many compound\_materials)  
-ID  
-item\_id  
-…

COMPOUND\_MATERIALS (belongs\_to item, compound  
-ID  
-item\_id  
-compound\_id

i guess my first question would be what is the best way that i can  
search on ITEM table values like names with the least amount of queries.

i thought about doing joins from compound and compound\_materials to  
items but i’m sure if it’s possible joining to the same table twice.

at the moment i am doing it this way:

@compounds = Compound.search(…)

@compound\_materials = CompoundMaterial.search(…)

```
  for compound_material in @compound_materials
    compound = Compound.find(:all, :conditions => [ "compounds.id =

```

?", compound\_material.compound\_id])  
@compounds += compound if not compound.blank?  
end

```
  @compounds.uniq!

  @compounds.sort! {...}

```

pretty ugly huh?

hope someone can help since i have been trying to come up with a  
solution for a few hours now.