CODEWITHSUNDEEP
sqlruby-on-railsactiverecord
Ahmad Ali
Ahmad Ali

Reputation: 168

How to write a query which icludes OR with AND condition in ILIKE search query

I am trying to write query for search, with includes more than 1 parameters, i wanna search based on that parameters using 'AND' and 'OR' operator and get data using ILIKE. My query is: TableName.where("( first_name ILIKE ? OR last_name ILIKE ? AND concat_ws(' ', address1, address2, city, state, country) ILIKE ? )", "%"+name+"%", "%"+last_name+"%", "%"+address+"%")

Upvotes: 0

Views: 895

Answers (1)

mahemoff
mahemoff

Reputation: 46479

I think you want this:

("( (first_name ILIKE ? OR last_name ILIKE ?) AND concat_ws(' ', address1, address2, city, state, country) ILIKE ?  )", "%"+name+"%", "%"+last_name+"%", "%"+address+"%")

I just added parentheses around the name condition, so essentially it's saying "either name must match" AND "address must also match"

(Side note it would be unusual to see an address condition like that - if there's any user input involved, they'll rarely match the exact address.)

Upvotes: 1

Related Questions