Bear Den Designs

Site wide search using mysql

I’m working on a site wide search using only mysql and not ferret or sphinx. I’m not ready to lift the veil but the big picture is:

• There is a search model and table where with a full text index
• Each model wishing to participate in searching responds to a callback function to create an index in the search table.
• Each model has a view that displays their results.

Anyone interested? So far the speed is very impressive and you don’t need to launch any additional daemons like sphinx and ferret require.

Back to work.

Easy Whole Site Searching

This is a method of searching all or some of your tables using Ruby on Rails and MySQL’s ability to do Full Text Searches. This method is all ruby without using ferret or sphinx ,both of which are very good tools.

This whole idea can probably be put into a plugin but I don’t have the time to mess with that yet.

First the model:

class Search < ActiveRecord::Base
  belongs_to :account
  belongs_to :searchable, :polymorphic => true 

  # this blindly creates an index for a particular klass / account combination.
  # there must be an account_id field in the klass being searched
  #
  # Note: This searches all columns of all records for this account which
  # is probably Too Much Information, TMI, Baby. You may want to define a
  # method called column_names_to_search which will restrict what is searched.
  #
  # Note: You may want to overide this method by defining your own
  # 'create_searchable_index' in your class.
  def self.create_searchable_index(account, klass)
    raise unless account.kind_of?(Account)
    return klass.create_searchable_index(account) if klass.respond_to?('create_searchable_index')
    Search.delete_all ["account_id = ? and searchable_type = ?", account[:id], klass.to_s]
    klass.find(:all, :conditions => "account_id = #{account[:id]}").each do |thing|
      if klass.respond_to?('column_names_to_search')
        text = klass.column_names_to_search.map{|col| thing.send(col).to_s}
      else
        text = klass.column_names.map{|col| thing.send(col).to_s}
      end
      Search.create! :account_id => account[:id], :searchable_id => thing.id, :searchable_type => thing.class.to_s, :searchable_text => text.join(' ')
    end
    Search.count :conditions => "account_id = #{account[:id]} and searchable_type = '#{klass}'"
  end

  def self.match(account, query, just_this_object = nil)
    matches = []
    sql = %{SELECT searchable_id, searchable_type FROM searches WHERE account_id = #{account.id} AND MATCH searchable_text AGAINST ('#{query}')}
    if just_this_object
      sql += %{ AND searchable_type = '#{just_this_object}'}
    end

    Search.find_by_sql(sql).each do |found|
      matches << eval("#{found.searchable_type}").find(found.searchable_id)
    end
    matches
  end
end

As the comments suggest you will probably be better off writing a method in the models you want to search. This allows you to expand certain associations or delete sensitive data. Here’s an example where I’m removing the id, updated_at, system, and account_id from the stuff I search, self.column_name_to_search. self.create_searchable_index does the heavy lifting of:

1. Deleting the old index
2. Finding all the groups for this account and looping on them to
   1. get just the columns I want
   2. add the user’s name to the searchable text so when we search for “Chad” we get the user and all the groups that use is in.
   3. create the entry in the Searches table.
3. Return the count of how many entries were created.

class Group < ActiveRecord::Base
  belongs_to :account
  has_many   :group_user_memberships, :order => 'position', :dependent=> :destroy
  has_many   :users, :through => :group_user_memberships, :order => "group_user_memberships.position, last_name, first_name, username"

# ... 

  private

  def self.column_names_to_search
      self.column_names - ["id", "updated_at", "system", "account_id"]
  end

  def self.create_searchable_index(account)
    Search.delete_all "account_id = #{account[:id]} and searchable_type = 'Group'"
    Group.find(:all, :conditions => ["account_id = ?",account[:id]], :order => 'name').each do |group|
      text = Group.column_names_to_search.map{|col| group.send(col).to_s}
      text << group.users.map(&:name)
      Search.create! :account_id => account[:id], :searchable_id => group[:id], :searchable_type => Group.to_s, :searchable_text => text.flatten.join(' ')
    end
    Search.count :conditions => "account_id = #{account[:id]} and searchable_type = 'Group'"
  end

end

The controller is very simple. It takes the form input which is displayed on everypage ala the layout. We use haml for markup.

-form_tag(:controller => 'search', :action => 'index' ) do
  %input.search_button{ :name => 'search_text', :type => 'text', :size=> '20', :value => (session[:search_text] || 'Search'), :onclick => 'this.focus();this.select()', :onfocus => 'this.select()' }/
  = submit_tag 'Search'

And finally the controller. There are 2 methods. One builds the indexes and the other does the match.

require 'benchmark'

class SearchController < ApplicationController
  layout 'home'
  before_filter :only_admin, :except => [:index]

  def index
    session[:search_text] = params[:search_text]
    @bench = Benchmark.measure do
      @matches = Search.match @user.account, session[:search_text] 
    end
  end

  def create_index
    count = 0
    SEARCH_CLASSES.each do |klass|
      count += Search.create_searchable_index(@account,klass)
    end
    flash[:success] = "Indexed #{count} records"
    redirect_to :controller => 'home', :action => 'index'
  end

  private

  def only_admin
    permit SUPERUSER_GROUP_NAME
  end
end

The view for the search results is pretty simple. Rather than create a unified view for all models that can be searched I create a partial called _search.haml in the views for their corresponding controller. You don’t have to do this but probably you will want to differentiate your models display results.

#search
  %br/
  %h3
    Found 
    = @matches.nitems
    matches for
    %span.italic
      = '"' + session[:search_text] + '"'
    in
    = sprintf "%2.3f", @bench.real
    seconds
  %br/
  
  %ul
    - @matches.each do |thing|
      %li.no_list_style
        %strong
          = thing.class.to_s
        %br/
        = render(:partial => %{#{thing.class.to_s.tableize}/search}, :locals => {:thing => thing}) #rescue thing.inspect
      %br/