0

Running a simple statement on my User model but keep on getting errors about the operator not matching any of the given arguments. I've never run into anything like this before and don't know how to handle it. I understand I have to perhaps add type casts, but in my POSTGRES database the column keywords is of type array.

@users = User.where("keywords ILIKE ?", "%#{params[:keywords]}%")

How do you handle this?

Exact error message:

ActionView::Template::Error (PG::SyntaxError: ERROR:  syntax error at or near "ANY"
LINE 1: SELECT "users".* FROM "users" WHERE (ANY(keywords) LIKE '%an...
                                         ^
: SELECT "users".* FROM "users" WHERE (ANY(keywords) LIKE '%angularjs%')):

Thanks!

luke
  • 1,513
  • 2
  • 20
  • 38
  • Try `@users = User.where("ANY(keywords) ILIKE ?", "%#{params[:keywords]}%")` – Pavan Jun 17 '17 at 16:43
  • Thanks for responding man - still get this `ActionView::Template::Error (PG::SyntaxError: ERROR: syntax error at or near "ANY"` – luke Jun 17 '17 at 16:49
  • Is `params[:keywords]` a single keyword or many? – Eyeslandic Jun 17 '17 at 16:54
  • it's just a string, so an example would be something like this: `angularjs` – luke Jun 17 '17 at 16:56
  • Can you post the exact error and the SQL returned with that query? – Pavan Jun 17 '17 at 17:02
  • made an edit in the question to include it – luke Jun 17 '17 at 17:06
  • I mean the error with original query. – Pavan Jun 17 '17 at 17:06
  • Two related answers that may be of help: [Can PostgreSQL index array columns?](https://stackoverflow.com/a/29245753/939860) And are you using the best technique for your particular pattern matching needs? [Pattern matching with LIKE, SIMILAR TO or regular expressions in PostgreSQL](https://dba.stackexchange.com/a/10696/3684) – Erwin Brandstetter Jun 18 '17 at 14:34

1 Answers1

2

If you want to search in a postgres array you use the any operator. Note, this solution assumes params[:keywords] is just a single string like apples or something. It also matches on exact match, not substrings.

@users = User.where('? = ANY(keywords)', params[:keywords])

If you need to use the ILIKE operator you must to something like

@users = User.where("array_to_string(names, ',') ILIKE ?", "%#{params[:keywords]}%")

or if you want to be fancy with regex

@users = User.where("array_to_string(names, ',') ~* ?", params[:keywords])
Eyeslandic
  • 14,553
  • 13
  • 41
  • 54
  • 1
    thanks! didn't necessarily need to use ILIKE. really liking the regex way. thanks again! – luke Jun 17 '17 at 20:15