Rails search SQL query

Question

I am trying to make search method in my Rails app, but sadly - my SQL knowledge is rather pathetic. I managed to find guides that have searched text in the description by:

@guides = Guide.where(['description LIKE ?', "%#{params[:search]}%"])

or in the title:

 @guides = Guide.where(['title LIKE ?', "%#{params[:search]}%"])

but I don't know how to look in both title and descripton at the same time. Please help me solve this problem.

Additionally I would appreciate any suggestions of sources to learn SQL from for usage in Rails apps (PostgreSQL mainly I guess?).

Its not the easy answer to your question, but if you want a more robust solution to searching a Rails application, I would humbly suggest standing up an elastic search instance and utilizing the searchkick gem. Although its another level of abstraction and more difficult to setup than a simple search postgres type of solution, it will keep you focused on your app on not on how to search the data in your application — engineerDave
– engineerDave, Commented Dec 3, 2015 at 23:43

MrYoshiji · Accepted Answer · 2015-12-03 22:36:57Z

7

You can use the keyword "or" in your SQL queries:

Guide.where('description LIKE :search OR name LIKE :search', search: "%#{params[:search]}%")

About the doc, you can try this website which seems to offer interactive courses about SQL in general: http://www.sqlcourse.com/

This website is about PostgreSQL: http://www.tutorialspoint.com/postgresql/postgresql_overview.htm

Also, ILIKE might be usefull in this case (it is case-insensitive).

edited Dec 3, 2015 at 22:36

answered Dec 3, 2015 at 22:08

MrYoshiji

54.9k13 gold badges130 silver badges120 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Jakub Kopyś Over a year ago

Thanks, worked like a charm. And I really appreciate these suggestions!

Don Pflaster Over a year ago

If you think you're going to use OR in your queries frequently (though try to avoid it because it is a drag on query performance), check out the squeel gem.

Jim Van Fleet · Accepted Answer · 2015-12-03 22:28:38Z

1

Try this:

class Guide
  def self.search(term)
    where("to_tsvector(coalesce(title,'') || ' ' " +
          "|| coalesce(description,'')) @@ plainto_tsquery('english', ?)", term)
  end
end

coalesce is the kindest treatment in this scheme for possible null values in the database.

This will perform whole-word, non-stemmed matching for the search term you supply. You don't have a lot of options to tune relevance rankings or improve performance with this naive scheme, but it will work!

PostgreSQL full-text search is quite powerful, and potentially worthy of more sophisticated options.

answered Dec 3, 2015 at 22:28

Jim Van Fleet

1,1187 silver badges13 bronze badges

2 Comments

Don Pflaster Over a year ago

There is also a quite excellent pg_search gem that will construct queries like this for you pretty painlessly as well. But if you find yourself searching through hundreds of thousands to millions of rows for full-text, definitely check out the "sophisticated" link above.

Jim Van Fleet Over a year ago

That's the 'sophisticated' link above, Don!

bideowego · Accepted Answer · 2015-12-03 23:29:51Z

The shortest answer is tag this method onto your model:

def search_by_fields(q, fields=nil)
  sql = fields.map {|field| "#{field} LIKE ?"}.join(' OR ')
  parameters = fields.map {"%#{q}%"}
  where(sql, *parameters)
end

A longer answer is in a concern!

Using a concern is a great way to provide search functionality to ANY model you choose. Check this out. First create a file in your app/models/concerns/ called searchable.rb with the following code:

#app/models/concerns/searchable.rb

module Searchable
  def self.included(base)
    base.extend(ClassMethods)
    base.include(AdapterAcknowledgeable)
  end

  module ClassMethods
    @@searchable_fields = []
    @@searchable_scope = nil

    def search(q, method=nil)
      search_method = resolve_search_method(method)
      self.send(search_method, q)
    end

    def search_by_fields(q, fields=nil)
      fields = searchable_fields unless fields
      sql = fields.map {|field| "#{field} LIKE ?"}.join(' OR ')
      parameters = fields.map {"%#{q}%"}
      where(sql, *parameters)
    end

    def search_by_scope(q, scope=nil)
      scope = searchable_scope unless scope
      scope.call(q)
    end

    def searchable_scope(scope=nil)
      @@searchable_scope = scope unless scope.nil?
      @@searchable_scope
    end

    def searchable_fields(*fields)
      @@searchable_fields = fields if fields.present?
      @@searchable_fields
    end


    private
    def resolve_search_method(method)
      method = method.downcase.to_sym unless method.nil?
      if method == :searchable_fields ||
        searchable_fields.present? && searchable_scope.nil?
        :search_by_fields
      elsif method == :searchable_scope ||
        !searchable_scope.nil? && searchable_fields.empty?
        :search_by_scope
      else
        raise "Unable to determine search method within #{self}, you must declare exactly one search method in the including class"
      end
    end
  end
end

Usage goes like this: This will enable the all columns foo, bar, fiz and baz to be queried via the SQL WHERE LIKE. By joining them with OR it allows any of them to match the query parameter.

#app/models/my_searchable_model.rb

class MySearchableModel < ActiveRecord::Base
  include Searchable

  searchable_fields :foo, :bar, :fiz, :bad
end

The concern also enables you to specify a scope with Ruby's lambda syntax like so:

class User < ActiveRecord::Base
  include Searchable

  searchable_scope ->(q){where("first_name || ' ' || last_name LIKE ?", "%#{q}%")}
end

Note the use of the SQL concatenation operator ||.

Now when we want to search a model that includes the concern:

MySearchableModel.search('foo')

Collectives™ on Stack Overflow

Rails search SQL query

3 Answers 3

2 Comments

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

2 Comments

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related