How to use like clause query in rails?

Question

I wanted to get a json format of the data when searching for the keyword so I use LIKE clause and query like this

"select * from employees where fname like ? or mname like ? or lname like ? or username like ? or id like ?", str, str, str, str, str

but I want to code it using rails. I have this code in my controller

def showemployees
  str = params[:str]
  render json: @employee = Employee.where(Employee.employees[:fname].matches("%#{str}%")) or
    (Employee.employees[:mname].matches("%#{str}%")) or
    (Employee.employees[:lname].matches("%#{str}%")) or
    (Employee.employees[:id].matches("%#{str}%"))
end

and this code in my config/routes.rb

get 'employees/showemployees'
root :to => 'employees#new'
resources :employees
post 'employees/update_info'

when i type this, http://localhost:3000/employees/showemployees?str=samplename, a json format of the record should appear yet I got this error message

undefined method `employees' for #<Class:0x8e38900>
app/controllers/employees_controller.rb:6:in `showemployees'

where line 6 has this code

render json: @employee = Employee.where(Employee.employees[:fname].matches("%#{str}%")) or

Please note that [`and`/`or` is *not* the same as `&&`/`||` in Ruby](http://devblog.avdi.org/2010/08/02/using-and-and-or-in-ruby/). Only the latter should be used when boolean logic is the intent. (Though this isn’t your problem here.) — Andrew Marshall, Mar 30 '15 at 12:32

Sonalkumar sute · Accepted Answer · 2015-03-30T12:10:11.957

11

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}%")

edited Mar 30 '15 at 12:10

answered Mar 30 '15 at 12:04

Sonalkumar sute

2,565
2
17
27

thanks, i got rid with the error but it returns null though my database has a record with the keyword entered – MAC Mar 30 '15 at 12:10
3

You can cut down on repetition with [named placeholders](http://guides.rubyonrails.org/v4.2.1/active_record_querying.html#placeholder-conditions): `Employee.where('fname LIKE :q OR lname LIKE :q OR mname LIKE :q', q: "%#{str}%")`. – Andrew Marshall Mar 30 '15 at 12:37
But unfortunately, the "%" and "_" characters in `str` won't be escaped! You need to escape these characters manually before using them in `"%#{str}%"`. You could do it like: `ActiveRecord::Base.send(:sanitize_sql_like, str)` – Robert Aug 22 '16 at 10:06

How to use like clause query in rails?

1 Answers1

Linked