Forum: Ruby on Rails help with search query

Announcement (2017-05-07): www.ruby-forum.com is now read-only since I unfortunately do not have the time to support and maintain the forum any more. Please see rubyonrails.org/community and ruby-lang.org/en/community for other Rails- und Ruby-related community platforms.
Scott K. (Guest)
on 2009-04-14 08:37
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
http://wlodb.com/compounds

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.
This topic is locked and can not be replied to.