Ruby on Rails - Search in Database Based on a Query

Ruby on Rails - search in database based on a query

One solution would be to break up your query into individual terms and build a set of database queries connected by OR.

terms = params[:q].split
query = terms.map { |term| "name like '%#{term}%'" }.join(" OR ")
Product.where(query).order('price')

Ruby on Rails search like any

I found the solution

module Searchable
  module_function
  def search_like_any(attributes, keyword)
    return if attributes.blank? || keyword.blank?
    
    values = keyword.split(/[,?.\s]/).uniq.reject(&:blank?)
    matches = attributes.uniq.product(values).map do |(attr, value)|
     arel_table[attr].matches("%#{value}%")
    end
    where matches.inject(:or)
  end
end

Rails Search with query

The problem here is that the method .with_query(qry) returns an Array. You want to do chain-scoping, so you must use scopes that returns ActiveRecord::Relation objects.

model.with_query(query) 
# returns an Array

model.with_query(query).where(criteria) 
# calling .where on an Array object => NoMethodError

model.where(criteria) 
# returns an ActiveRecord::Relation

model.where(criteria).with_query(query) 
# calls the query on an AR::Relation object, which is doable

---

Short version: Change this:

results << model.with_query(query).where(criteria)

To this:

results << model.where(criteria).with_query(query)

Rails search SQL query

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).

Create Rails query using LIKE that searches over association tables

And version

User.joins(:products).where("users.description LIKE ? AND products.description LIKE ?", "%happy%", "%happy%")

Or version

User.joins(:products).where("users.description LIKE ? OR products.description LIKE ?", "%happy%", "%happy%")

Error in using WHERE for search function in Ruby on Rails

LIKE performs a case sensitive match. If you want to perform a case insensitive match in a somewhat polyglot fashion you can use the LOWER() SQL function:

@users = User.where("LOWER(firstname) LIKE ? OR LOWER(lastname) LIKE ?", "%#{@word.downcase}%", "%#{@word.downcase}%")

Postgres has a ILIKE function which is case insensitive:

@users = User.where("firstname ILIKE ? OR lastname ILIKE ?", "%#{@word.downcase}%", "%#{@word.downcase}%")

You can also use Arel to construct it instead of a SQL string:

class User < ApplicationRecord
  has_many :favorite_jobs

  def self.search(term)
    where(
      arel_table[:firstname].matches("#{name}").or(
        arel_table[:lastname].matches("#{name}")
      )
    )
  end
end

This approach is more portable and espcially shines if you want to built the query programatically.

Search using like query in Ruby on Rails

It's often true that a bad name indicates wrong thinking. I believe your name Search for the model is in this category. It should probably be called Tutorial, no? Search is something you do to a model, not the model itself.

If this guesswork is correct and the model is now called Tutorial and it has a field called name that is a string, then your model will be

class Tutorial < ActiveRecord::Base

  def self.search(pattern)
    if pattern.blank?  # blank? covers both nil and empty string
      all
    else
      where('name LIKE ?', "%#{pattern}%")
    end
  end

end

This makes the model "smart" on how to search through tutorial names: Tutorial.search('foo') will now return all tutorial records that have foo in their names.

So we can create a controller that uses this new functionality:

class SearchController < ApplicationController 

  def show
    @tutorials = Tutorial.search(params[:q])

    respond_to do |format|
      format.html # show.html.erb
      format.json { render json: @tutorial }
    end
  end
end

The corresponding view must display the tutorials. Yours doesn't. The simplest way to do this is write a partial that renders exactly one tutorial. Say it's called _tutorial.html.erb.

Then in the view for Search, you need to add

<%= render :partial => @tutorials %>

to actually display the search results.

Addition

I'll build a little example.

# Make a new rails app called learning_system
rails new learning_system             

# Make a new scaffold for a Tutorial model.
rails g scaffold Tutorial name:string description:text 

# Now edit app/models/tutorial.rb to add the def above.

# Build tables for the model.
rake db:migrate

rails s # start the web server

# Now hit http://0.0.0.0:3000/tutorials with a browser to create some records.

<cntrl-C> to kill the web server

mkdir app/views/shared
gedit app/views/shared/_search_box.html.erb
# Edit this file to contain just the <%= form_tag you have above.

# Now add a header at the top of any view you like, e.g. 
# at the top of app/views/tutorials/index.html.erb as below
# (or you could use the layout to put it on all pages):

<h1>Listing tutorials</h1>
<%= render :partial => 'shared/search_box' %>

# Make a controller and view template for searches
rails g controller search show  

# Edit config/routes.rb to the route you want:  get "search" => 'search#show'

# Verify routes:

rake routes
       search GET    /search/:id(.:format)         search#show
    tutorials GET    /tutorials(.:format)          tutorials#index
              POST   /tutorials(.:format)          tutorials#create
 new_tutorial GET    /tutorials/new(.:format)      tutorials#new
edit_tutorial GET    /tutorials/:id/edit(.:format) tutorials#edit
     tutorial GET    /tutorials/:id(.:format)      tutorials#show
              PUT    /tutorials/:id(.:format)      tutorials#update
              DELETE /tutorials/:id(.:format)      tutorials#destroy

# Edit app/controllers/search_controller.rb as above.

# Create app/views/tutorial/_tutorial.html.erb with following content:
<tr>
<td><%= tutorial.name %></td>
<td><%= tutorial.description %></td>
</tr>

# Edit app/views/search/show.html.erb to have following content:
<h1>Show Search Results</h1>
<table>
<%= render :partial => @tutorials %>
</table>

Now try a little test. Fill in a search criterion and press the Search button.

---

Related Topics