Rails: search for members of a model using its columns or columns of related models, SQL-only

searchable.rb

module Searchable
  extend ActiveSupport::Concern

  module ClassMethods
    def searchable(*args)
      class_attribute :searchable_attributes
      self.searchable_attributes = args
    end

    def search(search_string, limit=100)
      return find(:all) if search_string.nil? || search_string.blank?
      query = self.except(:includes)
      attrs = searchable_attributes.dup

      searchable_columns = []
      
      if attrs.last.is_a? Hash
        related = attrs.pop

        related.each_pair do |relationship, column_names|
          relative = reflections.with_indifferent_access[relationship]
          fail 'Cannot search polymorphic relationships' if relative.options[:polymorphic]
          searchable_columns += column_names.map{|column_name| "`#{relative.table_name}`.`#{column_name.to_s}`"}
          query = case relative.source_macro
          when :belongs_to
            query.joins("LEFT OUTER JOIN `#{relative.table_name}` ON `#{table_name}`.`#{relative.foreign_key}` = `#{relative.table_name}`.`#{relative.association_primary_key}`")
          when :has_many, :has_one
            query.joins("LEFT OUTER JOIN `#{relative.table_name}` ON `#{relative.table_name}`.`#{relative.foreign_key}` = `#{table_name}`.`#{relative.association_primary_key}`")
          else
            fail 'Can only search direct relationships'
          end
        end
      end

      searchable_columns += attrs.map {|a| "`#{table_name}`.`#{a.to_s}`" }
      query = query.select(["`#{table_name}`.`#{primary_key}`", "concat_ws(' ', #{searchable_columns.join(', ')}) as searchable_fields"])
      
      search_string.split(' ').each do |s|
        query = query.having("searchable_fields LIKE ?", "%#{s.strip.gsub(/[%_]/){|x|'\\'+x}}%")
      end
      
      self.where("`#{table_name}`.`#{primary_key}` in (SELECT #{primary_key} FROM (#{query.to_sql}) AS searchable_temp)")
    end
  end
end

user.rb

class User < ActiveRecord::Base
  # example of use
  include Searchable
  belongs_to :employer
  has_many :houses
  has_many :cars
  
  searchable :forenames, :surname, employer: [:name], houses: [:name, :address, :telephone], cars: [:name, :licence_plate]
end

# Who is the guy parked at BigCorp that scratched my car?
# User.search('BigCorp LIC1234')