CODEWITHSUNDEEP
mysqlruby-on-railsrails-activerecord
Zack Herbert
Zack Herbert

Reputation: 960

Syntax error for where() active record query

I am trying to perform the following active record query, but keep getting a syntax error.

@kid.toys.where("name ILIKE ? OR description ILIKE ?", params[:query], params[:query])

I am trying to find all toys were the name is like params[:query] or the description is like params[:query]

The syntax error I get is the following:

{"error":"Mysql2::Error: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'ILIKE '\\\"robot\\\"' OR `description` ILIKE '\\\"robot\\\"')' at line 1: SELECT `items`.* FROM `items` WHERE `items`.`kid_id` = 2 AND (`name` ILIKE '\\\"robot\\\"' OR `description` ILIKE '\\\"robot\\\"')"}

Any help or guidance is much appreciated. I will continue to dig around for answers.

Upvotes: 0

Views: 188

Answers (1)

eggyal
eggyal

Reputation: 125835

ILIKE is not an operator in MySQL. Did you simply mean to use LIKE?

@kid.toys.where("name LIKE ? OR description LIKE ?", params[:query], params[:query])

If you were instead looking to force the pattern matching to be case-insensitive, you could explicitly use a case-insensitive collation by adding a COLLATE clause to either operand of each LIKE operation. For example:

@kid.toys.where("name LIKE ? COLLATE utf8_general_ci OR description LIKE ? COLLATE utf8_general_ci", params[:query], params[:query])

Upvotes: 4

Related Questions