I have a query I'm using for a search with :conditions like this:
:conditions => ['family_name LIKE ? OR given_name LIKE ?', "%#{params[:search]}%", "%#{params[:search]}%"]
The query works fine locally on SQLite, but when I push to Heroku on PostgreSQL, only the first % works for both family_name and given_name. In other words, it will match a keyword that occurs at the end of a word but not the beginning or middle.
Example: There is an existing record with :family_name => "Washington" and :given_name => "George"
A search for "ington" or "rge" will retrieve this record. A search for "Wash" or "Geo" will not.
I'm a new user and am pretty new to rails. Thanks in advance for your help.
Solution
Related threads: 1 2
Here's the fix I am using:
:conditions => ['LOWER(family_name) LIKE ? OR LOWER(given_name) LIKE ?', "%#{params[:search].downcase}%", "%#{params[:search].downcase}%"]
LIKE
is a case-sensitive operator in Postgres. For case-insensitive pattern matching use ILIKE
or other standard methods.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With