CODEWITHSUNDEEP
ruby-on-railspostgresqlactiverecord
Matias Carpintini
Matias Carpintini

Reputation: 137

OR condition in query

Try makes the following query:

title = "%#{params[:title]}%"
group = params[:group]

@foods = Food.order('visits_count DESC').where("title ILIKE ? OR group ILIKE ?", title, group).decorate

and in return I get the following error:

ActionView::Template::Error (PG::SyntaxError: ERROR:  syntax error at or near "group"
LINE 1: ..."foods".* FROM "foods" WHERE (title ILIKE '%%' OR group ILIK...
                                                             ^
: SELECT "foods".* FROM "foods" WHERE (title ILIKE '%%' OR group ILIKE '') ORDER BY visits_count DESC):

Any ideas?

Upvotes: 0

Views: 64

Answers (2)

Phlip
Phlip

Reputation: 5343

Try

"group"

with double-quotes.

Upvotes: 1

mu is too short
mu is too short

Reputation: 434585

Since this is PostgreSQL, you need to quote identifiers using double quotes when case sensitivity is an issue or you're using a keyword as an identifier:

...where('title ILIKE ? OR "group" ILIKE ?', title, group)...

You'd be better off not using a keyword as a column IMO so that you don't have to worry about this sort of thing. I'd go with he quoting for now and then rename the column as soon as possible but that's just me.

While I'm here, you might want to leave out those checks when they don't make sense. This part of your query:

title ILIKE '%%'

will always match when title is not null so you might want to use an explicit not null check or leave it out entirely when there is no title. Similarly for "group" ILIKE '' which only matches when "group" is empty.

Upvotes: 0

Related Questions