h2. What does it do?

It's pretty common to want to search for a string across multiple fields in a table, or even across multiple joined tables.  The ActiveRecord model already knows how the tables are joined together, so can we take advantage of that information to write a search method?  Yes!  Here's what the proposed solution looks like:

<pre>
<code>
require_dependency "search"

class Movie < ActiveRecord::Base
   has_and_belongs_to_many :genres
   searches_on :all
end
</code>
</pre>

Note that ":all" is not a field, but a directive indicating that the #search will occur on all text/varchar fields in my 'movies' table.  I could have explicitly told it to search on :title and :description like this:

<pre>
<code>
class Movie < ActiveRecord::Base
   has_and_belongs_to_many :genres
   searches_on :title, :description
end
</code>
</pre>

Now, all future calls to Movie.search will only search the "title" and "description" fields.

h2. Further Usage Examples

In the following examples, all calls to the search method return an array of Movie objects.

<pre>
<code>
# Make a case insensitive search for "star" within all text (or
varchar) fields of "movies"
  @movies = Movie.search "star"  

# Same, but case sensitive
  @movies = Movie.search "star", :case => :sensitive

# Search all text (or varchar) fields within Movie
# and Genre, according to the associations indicated
# by those models' classes.  For example, if genre 
# has_many movies and Genre has a "name" and
# a "description" field, then this search will return
# all movies containing "star" as well as all movies
# belonging to any genre whose description or name
# contains "star":
  @movies = Movie.search "star", :include => [:genre]  

# The search above could also be flipped around if 
# you want to ask the question "What genres 
# contain movies whose titles or descriptions contain
# 'star'?"
  @genres = Genre.search "star", :include => [:movie], :only =>
["movies.title", "movies.description"]

# Search for "star" within a restricted set of 
# text/varchar fields:
  @movies = Movie.search "star", :only => ["title", "description"]
  @movies = Movie.search "star", :except => ["title"]

# Search for "star" with certain other conditions, 
# such as within a particular genre:
  @movies = Movie.search "star", :conditions => "genre_id = 1"

# Search for "star" with other conditions in a
# joined table:
  @movies = Movie.search "star", :join_include => [:genre],
:conditions => "genres.hidden = 0"
</code>
</pre>


h2. The Search Library's Code

Add the following code to a file called "search.rb" in your application's "lib" directory:

<pre>
<code>
module ActiveRecord
   class Base
     # Allow the user to set the default searchable fields
     def self.searches_on(*args)
       if not args.empty? and args.first != :all
         @searchable_fields = args.collect { |f| f.to_s }
       end
     end

     # Return the default set of fields to search on
     def self.searchable_fields(tables = nil, klass = self)
       # If the model has declared what it searches_on, then use that...
       return @searchable_fields unless @searchable_fields.nil?

       # ... otherwise, use all text/varchar fields as the default
       fields = []
       tables ||= []
       string_columns = klass.columns.select { |c| c.type == :text or
c.type == :string }
       fields = string_columns.collect { |c| klass.table_name + "." +
c.name }

       if not tables.empty?
         tables.each do |table|
           klass = eval table.to_s.classify
           fields += searchable_fields([], klass)
         end
       end

       return fields
     end

     # Search the movie database for the given parameters:
     #   text = a string to search for
     #   :only => an array of fields in which to search for the text;
     #     default is 'all text or string columns'
     #   :except => an array of fields to exclude from the
     #     default searchable columns
     #   :case => :sensitive or :insensitive
     #   :include => an array of tables to include in the joins.
     #     Fields that have searchable text will automatically be
     #     included in the default set of fields to search
     #   :join_include => an array of tables to include in the joins,
     #     but only for joining. (Searchable fields will not
     #     automatically be included.)
     #   :conditions => a string of additional conditions (constraints)
     #   :offset => paging offset (integer)
     #   :limit => number of rows to return (integer)
     def self.search(text = nil, options = {})
       validate_options([:only, :except, :case, :include,
                         :join_include, :conditions, :offset, :limit],
                         options.keys)
       case_insensitive = true unless options[:case] == :sensitive

       # The fields to search (default is all text fields)
       fields = options[:only] || searchable_fields(options[:include])
       fields -= options[:except] if not options[:except].nil?

       # Now build the SQL for the search if there is text to search for
       condition_list = []
       unless text.nil?
         text_condition = if case_insensitive
           fields.collect do |f|
             "UCASE(#{f}) LIKE '%#{text.upcase}%'" 
           end.join " OR "
         else
           fields.collect { |f| "#{f} LIKE '%#{text}%'" }.join " OR "
         end

         # Add the text search term's SQL to the conditions string unless
         # the text was nil to begin with.
         condition_list << "(" + text_condition + ")"
       end
       condition_list << "#{sanitize_sql(options[:conditions])}" if options[:conditions]
       conditions = condition_list.join " AND "
       conditions = nil if conditions.empty?

       includes = (options[:include] || []) +
         (options[:join_include] || [])
       includes = nil if includes.size == 0

       find :all, :include => includes, :conditions => conditions,
            :offset => options[:offset], :limit => options[:limit]
     end
   end
end
</code>
</pre>

h2. Author

You can contact [[Duane Johnson]] if you have suggestions, modifications or questions regarding this code contribution.

h2. See Also
* FullTextSearch