Sql Like Operator in Ruby on Rails

SQL Like operator in ruby on rails

Try:

@studentname = Student.where("name LIKE :name1 AND city = :cityId1",
  {:name1 => "#{params[:name]}%", :cityId1 => params[:cityId]})

This is a rather dirty solution, but pure ARel cannot handle this case the way you desire. You might want to try the Sqeel gem.

Rails SQL query where column like?

So, if the input is bla, then you want

• names that begin with bla
• and names that have a space followed by bla

That would be:

YourModel.where("name LIKE ? OR name LIKE ?", "#{term}%", "% #{term}%")

How to use like clause query in rails?

You can chain where queries, but this AND each where query results

Employee.where('fname LIKE ?', "%#{str}%").where('lname LIKE ?', "%#{str}%").where('mname LIKE ?', "%#{str}%").where('username LIKE ?', "%#{str}%").where('id LIKE ?', "%#{str}%")

or to use OR clause

Employee.where('fname LIKE ? OR lname LIKE ? OR mname', "%#{str}%", "%#{str}%", "%#{str}%")

How to use LIKE query in Ruby on Rails

try this..

User.joins(:job).where("job_name like ? and name like ?","%Dummy%", "%Bzupnick")

You can also verfify the SQL query by putting to_sql at the end of the above statement

SQL query in Rails using % and LIKE

You need to add the %s in Ruby before quoting:

connection.query("SELECT * 
                  FROM test
                  WHERE y LIKE #{connection.quote('%' + name + '%')}
                  ORDER BY x ASC")

connection.quote will add single quotes to produce a valid SQL string literal and you want to get the %s inside that string literal, hence the Ruby string concatenation before connection.quote is called.

Or you could do it in SQL:

connection.query("SELECT * 
                  FROM test
                  WHERE y LIKE '%' || #{connection.quote(name)} || '%'
                  ORDER BY x ASC")

|| is the standard SQL string concatenation operator, you might need to use the concat function or something else if you're using a database that doesn't really support SQL.

You're better off using the ActiveRecord interface as spickermann suggests but sometimes you need to do it by hand so it is useful to know how.

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.

In a Rails WHERE LIKE query, what does the percent sign mean?

The percent sign % is a wildcard in SQL that matches zero or more characters. Thus, if search is "hello", it would match strings in the database such as "hello", "hello world", "well hello world", etc.

Note that this is a part of SQL and is not specific to Rails/ActiveRecord. The queries it can be used with, and the precise behavior of LIKE, differ based on SQL dialect (MySQL, PostgreSQL, etc.).

Rails 4 LIKE query - ActiveRecord adds quotes

Your placeholder is replaced by a string and you're not handling it right.

Replace

"name LIKE '%?%' OR postal_code LIKE '%?%'", search, search

with

"name LIKE ? OR postal_code LIKE ?", "%#{search}%", "%#{search}%"

How to include a LIKE clause in Rails query on HABTM join table

Try this:

.where("keywords.name LIKE ?", "%#{search}%")

EDIT

Note: this requires using .joins instead of .includes before the WHERE query.

Mysql error on sql like operator rails

SQL's LIKE operator takes strings on both sides so the SQL you want to produce is:

LOWER(reservations.message) LIKE '%booked%'

i.e. %booked% in single quotes. The easiest way to do that is to use a placeholder in your scope:

scope :with_type, ->(type) { where('LOWER(reservations.message) LIKE ?', "%#{type}%") }

That puts the string interpolation in Ruby and hands that string to where to quote it so you're also protected from injection issues.

---

Related Topics