Simple Model Search with Rails

search.rb

http://erniemiller.org/2008/02/07/simple-model-search-with-rails/

#controller
class OffenceController < ApplicationController
 def index
     @offenses = []
     @search = Search.new(Offense,params[:search])

     if @search.conditions
        @offenses = Offense.search(@search)
     end

     respond_to do |format|
        format.html # index.html.erb
        format.xml  { render :xml => @offenses }
     end
  end
end

#model
class Offence < ActiveRecorc::Base
  has_one :offince_level
  has_one :offence-type
  has_one :account_type
  has_one :site

 # The "s" parameter is an instance of Search, instantiated from form input.
  def self.search(s, args = {})
    Offense.where(s.conditions).includes(:offense_level, :offense_type, :account_type, :account_status, :site).order('offenses.created_at')
  end
end

app/views/offenses/index.html.erb:
    <% form_for :search, @search,
                :html => { :method => :get } do |f| %>
      <%= f.label :offense_type_id, "Type" %>
      <%= f.collection_select :offense_type_id, @offensetypes,
                              :id_before_type_cast, :name,
                              { :include_blank => true } %>
      <br />
      <%= f.label :offense_level_id, "Level" %>
      <%= f.collection_select :offense_level_id, @offenselevels, :id_before_type_cast, :name,
                           { :include_blank => true } %>
      <br />
      <%= f.label :investigation_number, "Investigation #" %>
      <%= f.text_field :investigation_number %>
      <br />
      <%= f.label :created_from, "Created after" %>
      <%= f.date_select :created_from, :order => [:month, :day, :year], :include_blank => true %>
      (more...)
    <% end %>

class Search
  attr_reader :options

  def initialize(model, options)
    @model = model
    @options = options || {}
  end

  def created_from
    date_from_options(:created_from)
  end

  def created_to
    date_from_options(:created_to)
  end

  def updated_from 
    date_from_options(:updated_from)
  end

  def updated_to
    date_from_options(:updated_to)
  end

  def modem_mac
    options[:modem_mac].to_s.gsub(/[^0-9a-f]/i, '').upcase
  end

  # method_missing will autogenerate an accessor for any attribute other
  # than the methods already written. I love this magic. :)
  def method_missing(method_id, *arguments)
    if @model.column_names.include?(method_id.to_s)
      options[method_id].to_s
    else
      raise NoMethodError, "undefined method #{method_id}"
    end
  end

  def conditions
    conditions = []
    parameters = []

    return nil if options.empty?

    if created_from
      conditions << "#{@model.table_name}.created_at >= ?"
      parameters << created_from.to_time
    end

    if created_to
      conditions << "#{@model.table_name}.created_at <= ?"
      parameters << created_to.to_time.end_of_day
    end

    if updated_from
      conditions << "#{@model.table_name}.updated_at >= ?"
      parameters << updated_from.to_time
    end

    if updated_to
      conditions << "#{@model.table_name}.updated_at <= ?"
      parameters << updated_to.to_time.end_of_day
    end

    # note that we're using self.send to make sure we use the getter methods
    # so that stuff like modem_mac gets its proper formatting in parameters
    options.each_key do |k|
      next unless @model.column_names.include?(k.to_s)
      v = self.send(k) unless k == :conditions # No infinite recursion for you.
      next if v.blank?
      if k =~ /_id$/
        conditions << "#{@model.table_name}.#{k} = ?"
        parameters << v.to_i
      else
        conditions << "#{@model.table_name}.#{k} LIKE ?"
        parameters << "%#{v}%"
      end
    end

    unless conditions.empty?
      [conditions.join(" AND "), *parameters]
    else
      nil
    end
  end

  private

  # Just like the one in the Report model, but just for dates instead of times.
  # Using a Proc to generate input parameter names like those for date_select.
  def date_from_options(which)
    part = Proc.new { |n| options["#{which}(#{n}i)"] }
    y,m,d = part[1], part[2], part[3]
    y = Date.today.year if y.blank?
    Date.new(y.to_i, m.to_i, d.to_i)
  rescue ArgumentError => e
    return nil
  end
end

task_filter.rb

https://github.com/ari/jobsworth/blob/master/app/models/task_filter.rb

# Table name: task_filters
#
# id :integer(4) not null, primary key
# name :string(255)
# company_id :integer(4)
# user_id :integer(4)
# shared :boolean(1)
# created_at :datetime
# updated_at :datetime
# system :boolean(1) default(FALSE)
# unread_only :boolean(1) default(FALSE)
# recent_for_user_id :integer(4)
#
class TaskFilter < ActiveRecord::Base
  belongs_to :user
  belongs_to :company
  has_many(:qualifiers, :dependent => :destroy, :class_name => "TaskFilterQualifier")
  has_many :keywords, :dependent => :destroy
  has_many :task_filter_users, :dependent => :delete_all

  accepts_nested_attributes_for :keywords
  accepts_nested_attributes_for :qualifier
  
  # Returns an array of the conditions to use for a sql lookup
  # of tasks for this filter
  def conditions(extra_conditions = nil)
    time_qualifiers = qualifiers.select { |q| q.qualifiable_type == "TimeRange" }
    status_qualifiers = qualifiers.select { |q| q.qualifiable_type == "Status" }
    property_qualifiers = qualifiers.select { |q| q.qualifiable_type == "PropertyValue" }
    customer_qualifiers = qualifiers.select { |q| q.qualifiable_type == "Customer" }
    standard_qualifiers = (qualifiers - property_qualifiers - status_qualifiers -
                           customer_qualifiers - time_qualifiers)

    res = conditions_for_standard_qualifiers(standard_qualifiers)
    res += conditions_for_property_qualifiers(property_qualifiers)
    res << conditions_for_status_qualifiers(status_qualifiers)
    res << conditions_for_customer_qualifiers(customer_qualifiers)
    res << conditions_for_time_qualifiers(time_qualifiers)
    res << conditions_for_keywords
    res << extra_conditions if extra_conditions
    res << unread_conditions(user) if unread_only?

    res = res.select { |c| !c.blank? }
    res = res.join(" AND ")

    return res
  end
  
  private
  
  # Returns an array of conditions that will filter tasks based on the
  # given standard qualifiers.
  # Standard qualifiers are things like project, milestone, user, where
  # a filter will OR the different users, but and between different types
  def conditions_for_standard_qualifiers(standard_qualifiers)
    standard_qualifiers = standard_qualifiers.group_by { |qualifier| qualifier.reversed?}
    simple_conditions_for_standard_qualifiers(standard_qualifiers[false]) + 
           simple_conditions_for_standard_qualifiers(standard_qualifiers[true]).map{|sql| 'not ' + sql}
  end

  def simple_conditions_for_standard_qualifiers(standard_qualifiers)
    return [] if standard_qualifiers.nil?
    res = []

    grouped_conditions = standard_qualifiers.group_by { |q| q.qualifiable_type }
    grouped_conditions.each do |type, values|
      name = column_name_for(type)
      ids = values.map { |v| v.qualifiable_id }
      res << "#{ name } in (#{ ids.join(",") })"
    end

    return res
  end
  
end

https://github.com/ari/jobsworth/blob/master/app/controllers/task_filters_controller.rb
class TaskFiltersController < ApplicationController
     def new
    @filter = TaskFilter.new(:user => current_user)
    render :layout => false
  end

  def create
    if params[:replace_filter]
      @filter = current_user.company.task_filters.find(params[:filter_to_replace])
      @filter.select_filter(current_task_filter)
      flash[:success] = "filter #{@filter.name} updated successfully."
    else
      @filter = TaskFilter.new(params[:task_filter])
      @filter.user = current_user
      @filter.copy_from(current_task_filter)
      if !@filter.save
        flash[:error] = @filter.errors.full_messages.join(" ")
      else
        flash[:success] = "filter #{@filter.name} created successfully."
      end
    end

    redirect_using_js_if_needed("/tasks")
  end
end

https://github.com/ari/jobsworth/blob/master/app/models/abstract_task.rb
# this is abstract class for Task and Template
class AbstractTask < ActiveRecord::Base
end

https://github.com/ari/jobsworth/blob/master/app/models/task_record.rb
class TaskRecord < AbstractTask

  def self.search(user, keys)
    tf = TaskFilter.new(:user => user)

    conditions = []
    keys.each do |k|
      conditions << "tasks.task_num = #{ k.to_i }"
    end
    name_conds = Search.search_conditions_for(keys, [ "tasks.name" ], :search_by_id => false)
    conditions << name_conds[1...-1] # strip off surounding parentheses

    conditions = "(#{ conditions.join(" or ") })"
    return tf.tasks(conditions)
  end

  def csv_header
    ['Client', 'Project', 'Num', 'Name', 'Tags', 'User', 'Milestone', 'Due', 'Created', 'Completed', 'Worked', 'Estimated', 'Resolution'] +
      company.properties.collect { |property| property.name }
  end

  def to_csv
    [customers.uniq.map{|c| c.name}.join(','), project.name, task_num, name, tags.collect(&:name).join(','), owners_to_display, milestone.nil? ? nil : milestone.name, self.due_date, created_at, completed_at, worked_minutes, duration, status_type ] +
      company.properties.collect { |property| property_value(property).to_s }
  end

end

https://github.com/ari/jobsworth/blob/master/app/models/search.rb
class Search
  ###
  # Returns an array to use as the conditions value
  # in a find.
  # When used in a find, the id and any given fields
  # will be searched and any objects with a ANY of the
  # given strings as a starting substring will be returned.
  # If a number is given, any objects with that id will be
  # returned, but so will any objects with that number in fields.
  #
  # If options[:search_by_id] is false, ids won't be searched automatically.
  # If options[:start_search_only], only values starting with the given string will be returned.

  ###
  def self.search_conditions_for(strings, fields = [ :name ], options = {})
    search_by_id = options.has_key?(:search_by_id) ? options[:search_by_id] : true
    id_field= options.has_key?(:table) ? "#{options[:table]}.id" : "id"

    conds = []
    cond_params = []

    if search_by_id
      strings.each do |s|
        next if s.to_i <= 0
        conds << "#{id_field} = ?"
        cond_params << s
      end
    end

    fields.each do |field|
      strings.each do |s|
        next if s.strip.blank?
        conds << "lower(#{ field }) like ?"
        if options[:start_search_only]
          cond_params << "#{ s.downcase.strip }%"
        else
          cond_params << "%#{ s.downcase.strip }%"
        end
      end
    end

    if conds.any?
      full_conditions = [ conds.join(" or ") ] + cond_params
      sanitized = ActiveRecord::Base.send(:sanitize_sql_array, full_conditions)
      return "(#{ sanitized })"
    end
  end
end

https://github.com/ari/jobsworth/blob/master/app/models/customer.rb
Table name: customers
#
# id :integer(4) not null, primary key
# company_id :integer(4) default(0), not null
# name :string(200) default(""), not null
# contact_name :string(200)
# created_at :datetime
# updated_at :datetime
# active :boolean(1) default(TRUE)
#
class Customer < ActiveRecord::Base

  def self.from_company(company_id)
    where('customers.company_id' => company_id)
  end

  def self.search_by_name(term)
    name = arel_table[:name]

    where(name.matches("#{term}%").or(
          name.matches("%#{term}%")))
    .order('name')
  end

  ###
  # Searches the customers for company and returns
  # any that have names or ids that match at least one of
  # the given strings
  ###
  def self.search(company, strings)
    conds = Search.search_conditions_for(strings, [ :name ], :start_search_only => true)
    return company.customers.where(conds)
  end

end

https://github.com/ari/jobsworth/blob/master/app/models/user.rb
class User < ActiveRecord::Base

    ###
  # Searches the users for company and returns
  # any that have names or ids that match at least one of
  # the given strings
  ###
  def self.search(company, strings)
    conds = Search.search_conditions_for(strings, [ :name ], :start_search_only => true)
    return company.users.where(conds)
  end

  def self.search_by_name(term)
    name = arel_table[:name]
    where(name.matches("#{term}%").or(name.matches("%#{term}%"))).order('name')
  end

end