Recipe.where query causing errors

[rdalin82]

This line of code is causing the errors at the bottom. I am using Ruby 2.2 and Rails 4.2. Also please ignore that I changed recipe to items as shown in the error messages.

Error causing code -> Recipe.where('name ilike ?',"%#{params[:keywords]}%")

I was able to change it to
Recommended correction -> Recipe.where('name LIKE ?',"%#{params[:keywords]}%")

if you would like I could do a pull request with the change.

class RecipesController < ApplicationController
skip_before_filter :verify_authenticity_token
def index
@recipes = if params[:keywords]
Recipe.where('name ilike ?',"%#{params[:keywords]}%")
else
[]
end
end
end

This line of code is causing the below errors

Received the below errors

1. ItemsController index when the search finds results should 200
   Failure/Error: xhr :get, :index, format: :json, keywords: keywords
   ActionView::Template::Error:
   SQLite3::SQLException: near "ilike": syntax error: SELECT "items".* FROM "items" WHERE (name ilike '%baked%')

   ./app/views/items/index.json.jbuilder:1:in `_app_views_items_index_json_jbuilder__555461427235302198_50235440'

   ./spec/controllers/items_controller_spec.rb:13:in`block (3 levels) in <top (required)>'

2. ItemsController index when the search finds results should return two results
   Failure/Error: xhr :get, :index, format: :json, keywords: keywords
   ActionView::Template::Error:
   SQLite3::SQLException: near "ilike": syntax error: SELECT "items".* FROM "items" WHERE (name ilike '%baked%')

   ./app/views/items/index.json.jbuilder:1:in `_app_views_items_index_json_jbuilder__555461427235302198_50235440'

   ./spec/controllers/items_controller_spec.rb:13:in`block (3 levels) in <top (required)>'

3. ItemsController index when the search finds results should included 'Baked Brussel Sprouts'
   Failure/Error: xhr :get, :index, format: :json, keywords: keywords
   ActionView::Template::Error:
   SQLite3::SQLException: near "ilike": syntax error: SELECT "items".* FROM "items" WHERE (name ilike '%baked%')

   ./app/views/items/index.json.jbuilder:1:in `_app_views_items_index_json_jbuilder__555461427235302198_50235440'

   ./spec/controllers/items_controller_spec.rb:13:in`block (3 levels) in <top (required)>'

4. ItemsController index when the search doesn't find results should return no results
   Failure/Error: xhr :get, :index, format: :json, keywords: keywords
   ActionView::Template::Error:
   SQLite3::SQLException: near "ilike": syntax error: SELECT "items".* FROM "items" WHERE (name ilike '%foo%')

   ./app/views/items/index.json.jbuilder:1:in `_app_views_items_index_json_jbuilder__555461427235302198_50235440'

   ./spec/controllers/items_controller_spec.rb:13:in`block (3 levels) in <top (required)>'

Finished in 7.8 seconds
5 examples, 4 failures

[davetron5000]

I guess SQLite doesn't support ilike. Any reason you are using that and not another database like MySQL or Postgres?

In order to replicate the ilike version using like, you have to adjust the case in the query:

Recipe.where('lower(name) LIKE ?',"%#{params[:keywords].downcase}%")

Not sure if SQLite supports the lower() function, though.

[rdalin82]

Just tested lower(name) with .downcase. It does work, I was able to make it work without those added(searches Baked, baked, bAKED yielded same results), however your way is probably safer. I was using SQLite because I didn't plan on deploying to heroku. I'm mid tutorial and I will try to switch to Postgres to prevent future issue. Thank you for your response

[davetron5000]

Cool. I'm wondering if it works without the lower() call because of some oddity in SQLite?

[rdalin82]

It did, seemed to not care about case when using LIKE