Topic: Advanced find across associations

I am trying to develop a very simple query (ha!) that will find a number of objects based off of an array of search terms.  Each object has a few other associated items such that the structure is as follows:

Recipe
     has_many :ingredients
     has_and_belongs_to_many :tags

Ingredient
     has_one :food


Where recipe has a title (in addition to a bunch of other attributes), and foods and tags each have names.

My goal is to take a list of search terms such as ["appetizer", "grilled", "chicken"] and find them all either in recipe.title, recipe.tags.name or recipe.ingredients.food.name.  This would mean that the previously listed terms would match a recipe with the term "grilled" in the title, "chicken" as an ingredient's food's name, ans "appetizer" as a tag name (or any other combination).  It would also match a recipe with "appetizer" in the title, "grilled" in the tags, and "chicken" in the ingredients (and any other combination that includes ALL of the search terms.

My first idea was to do this using

Recipe.find(:all, :include => [{:ingredients=>:food}, :tags], :condition => blah blah blah

but this has got to generate some massive and confusing SQL that I can't imagine would have very good performance, plus I am not sure how to structure the conditions statement in order to pretty much state that all search terms must be found, but that they can occur in any of those fields, and in any order.

Is there a better way to go about doing such a complex search?  I've looked at some searchable plugins, but they never seem to easily allow this sort of thing.

The only other (and really crappy sounding) method that I can think of is to have a text field in the recipe object that simply holds a list of every term found in either the title, the tag names, or the ingredient food names.

Either that or develop some sort of reverse find where I find any ingredients that match the terms, then find the recipes that contain those ingredients.  Hmmm, maybe that isn't such a terrible idea...  I may be on to something here.

EDIT: Oops, should this be in the "rails programming" forum?  I thought I had put it there.

Last edited by opqdan (2007-06-28 14:25:02)

Re: Advanced find across associations

Arrggggh.  It never fails.  I spend hours trying to find an answer to a problem, and as soon as I get fed up and post a question on a forum (which I probably posted in the wrong place to begin with anyways), I stumble across the answer.

In this case, my best option is to use ferret and acts_as_ferret with something like

class Recipe < ActiveRecord::Base
     acts_as_ferret :fields => [:title, :tags_with_spaces, :foods_with_spaces]
     ...
     def tags_with_spaces
          return a joined string of all of the tag names,
          separated by spaces
     end

     def foods_with_spaces
          return a joined string of all the food names,
          separated by spaces
     end
end


This seems like it would work out beautifully, and since ferret does all the indexing and stuff for me, it should be fine in performance also.

Man, I just love RoR.  It never fails that somebody has done the same thing I am trying to do, and has done it in an elegant and pluggable way.

Re: Advanced find across associations

opqdan wrote:

Arrggggh.  It never fails.  I spend hours trying to find an answer to a problem, and as soon as I get fed up and post a question on a forum (which I probably posted in the wrong place to begin with anyways), I stumble across the answer.

I know what you mean. Maybe you should try Rubber Ducking. Works wonders. wink

Moved the topic to Rails Programming, btw.

Railscasts - Free Ruby on Rails Screencasts

Re: Advanced find across associations

Ha.  Thanks for the information and for the move.