Rails - May 2005 - A search method for your models

I found that searching for text across multiple fields and/or multiple
associated tables was a fairly common need, but not quite addressed by
the #find method.  Therefore, I built the following "search" method
that can be placed inside of any ActiveRecord-derived model class (or
if found useful, it could be placed inside ActiveRecord itself):

== Usage examples =
# All calls to #search return an array of Movie objects.

# 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
movie has_many
# genres 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 => [:genres]  

# This search 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 => [:movies], :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 => [:genres],
:conditions => "genres.hidden = 0"


== The two methods =  # Returns a list of searchable (i.e. text or varchar)
fields in this
table as well
  # as any other tables mentioned in the "tables" array passed in.
  def self.searchable_fields(tables = nil, klass = self)
    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 (default is :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 :search_columns.
  #   :join_include => an array of tables to include in the joins, but only
  #     for joining. (Text fields from these tables will not
automatically be searched.)
  #   :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 { |f| "UCASE(#{f}) LIKE
''%#{text.upcase}%''" }.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

-- 
Duane Johnson
(canadaduane)

On Monday 02 May 2005 18:00, Duane Johnson wrote:
> I found that searching for text across multiple fields and/or
> multiple associated tables was a fairly common need, but not quite
> addressed by the #find method.  Therefore, I built the following
> "search" method that can be placed inside of any
ActiveRecord-derived
> model class (or if found useful, it could be placed inside
> ActiveRecord itself):
There''s third (well, second and a half) way: Provide a
"static" method
that adds the search method to an AR-derived class. Here''s the user 
(programmer) interface I''m thinking of

class Something < ActiveRecord::Base
  make_searchable # determine searchable fields introspectively
  ...
end

class SomethingElse < ActiveRecord::Base
  make_searchable :col1, :col2 # use explicitly given fields
  ...
end

As Ruby classes are open, i.e. you can add (and even remove and 
override) methods in multiple places, you can apply the requisite 
additions without any changes to Rails itself. Somewhere after the 
Rails code is loaded, require a file containing something like the 
following

module ActiveRecord
  class Base
    def self.make_searchable
      # Here goes code that adds a search method to the concrete
      # class in whose definition this method is called
    end
  end
end

The commented part is left there as an excercise, admittedly only 
because I''m not yet nearly well-versed enough in Ruby metaprogramming 
to write it down quickly. Have a look at the implementations of 
belongs_to, has_one, et al. for inspiration.

Michael

-- 
Michael Schuerig               Most people would rather die than think.
mailto:michael-q5aiKMLteq4b1SvskN2V4Q@public.gmane.org                          
In fact, they do.
http://www.schuerig.de/michael/                      --Bertrand Russell

That sounds like a terrific approach, Michael!  I hadn''t thought of
that option.  Perhaps in the next little while I''ll do some
refactoring and implement just such a method.  It would be nice to be
able to tell the model which fields, by default, should be searched
(and while we''re at it, what default :conditions and :case to use).

I''m thinking I''ll use the term "searches_on" as the
name of the class
method.  For example, as in Michael''s post:

class SomethingElse < ActiveRecord::Base
 searches_on :col1, :col2 # use explicitly given fields
 ...
end

Any thoughts?

-- 
Duane Johnson
(canadaduane)

As a tentative implementation of Michael''s suggestion, here is a  
"lib" version of the #search method.  All you need is a  
"require_dependency ''search''" at the beginning of
your model and
optionally a "searches_on" declaration in the model class, and  
voila!  a searchable model.  I left the "search" method inside of  
ActiveRecord::Base so that "search" can be called even without the  
searches_on declaration.

My model, for example, looks like this:

require_dependency "search"

class Movie < ActiveRecord::Base
   has_and_belongs_to_many :genres
   searches_on :all
end

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:

class Movie < ActiveRecord::Base
   has_and_belongs_to_many :genres
   searches_on :title, :description
end

Note that explicitly telling the model what to search on will  
introduce a "bug" (feature?) at this point: you can''t get the
search
method to go back to using the default set of all text/varchar  
fields.  You will have to override the model''s search fields by using  
the :only parameter in a call to the #search method.

== lib/search.rb =
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 :search_columns.
     #   :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 { |f| "UCASE(#{f}) LIKE ''%#{text.upcase}
%''" }.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

== end of search.rb ==

Just wanted to say thanks for this, looks like a nice addition to
Rails. I hope it becomes part of ActiveRecord.

Regards,
Tomas Jogin

On 5/3/05, Duane Johnson
<duane.johnson-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org>
wrote:
> As a tentative implementation of Michael''s suggestion, here is a
> "lib" version of the #search method.  All you need is a
> "require_dependency ''search''" at the beginning
of your model and
> optionally a "searches_on" declaration in the model class, and
> voila!  a searchable model.  I left the "search" method inside of
> ActiveRecord::Base so that "search" can be called even without
the
> searches_on declaration.
> 
> My model, for example, looks like this:
> 
> require_dependency "search"
> 
> class Movie < ActiveRecord::Base
>    has_and_belongs_to_many :genres
>    searches_on :all
> end
> 
> 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:
> 
> class Movie < ActiveRecord::Base
>    has_and_belongs_to_many :genres
>    searches_on :title, :description
> end
> 
> Note that explicitly telling the model what to search on will
> introduce a "bug" (feature?) at this point: you can''t
get the search
> method to go back to using the default set of all text/varchar
> fields.  You will have to override the model''s search fields by
using
> the :only parameter in a call to the #search method.
> 
> == lib/search.rb => 
> 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 :search_columns.
>      #   :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 { |f| "UCASE(#{f}) LIKE
''%#{text.upcase}
> %''" }.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
> 
> == end of search.rb => 
> _______________________________________________
> Rails mailing list
> Rails-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org
> http://lists.rubyonrails.org/mailman/listinfo/rails
>

On Tuesday 03 May 2005 00:40, Duane Johnson wrote:
> As a tentative implementation of Michael''s suggestion, here is a  
> "lib" version of the #search method.
Cool :-) Would you mind putting it on the Wiki?

Michael

-- 
Michael Schuerig                       Face reality and stare it down
mailto:michael-q5aiKMLteq4b1SvskN2V4Q@public.gmane.org        --Jethro Tull,
Silver River Turning
http://www.schuerig.de/michael/

It''s on the wiki now at a new 3rd party page, 3rd Pary Libs: 

http://wiki.rubyonrails.com/rails/show/3rd+Party+Libs

Thanks for the feedback everyone.

Duane Johnson
(canadaduane)

On 5/3/05, Michael Schuerig
<michael-q5aiKMLteq4b1SvskN2V4Q@public.gmane.org>
wrote:
> On Tuesday 03 May 2005 00:40, Duane Johnson wrote:
> > As a tentative implementation of Michael''s suggestion, here
is a
> > "lib" version of the #search method.
> 
> Cool :-) Would you mind putting it on the Wiki?
> 
> Michael
> 
> --
> Michael Schuerig                       Face reality and stare it down
> mailto:michael-q5aiKMLteq4b1SvskN2V4Q@public.gmane.org        --Jethro
Tull, Silver River Turning
> http://www.schuerig.de/michael/
> _______________________________________________
> Rails mailing list
> Rails-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org
> http://lists.rubyonrails.org/mailman/listinfo/rails
> 

-- 
Duane Johnson
(canadaduane)