ActiveRecord (SQL) query multiple columns only if search string not empty

Question

Using a PG database filled with registered voters.

Trying to set it up so I can search by first name, last name, zip or city. I want to be able to find all voters that match all of the entered params, but having trouble dealing with empty search fields.

where("zip LIKE ? OR city LIKE ? OR last_name LIKE ? OR first_name LIKE ?",
 "#{params[:zip]}","#{params[:city]}","#{params[:last_name]}","#{params[:first_name]}")

Is there a better way to build it out so that it matches ALL entered parameters, but ignores empty string parameters? Right now if I enter a first and last name 'John Smith' I will get 'John Jones' and 'Jane Smith'.

MrYoshiji · Accepted Answer · 2017-08-16 18:38:13Z

2

This can do the trick:

attrs_name_to_search = %w( zip city last_name first_name )
fitlered_params = params.slice(*attrs_name_to_search).select { |_,v| v.present? }
sql_cond = filtered_params.map { |k,_| "#{k} LIKE :#{k}" }.join(' OR ')
YourModel.where(sql_cond, filtered_params)

But it should return all the records if no zip/city/last_name/first_name is given.

answered Aug 16, 2017 at 18:38

MrYoshiji

54.9k13 gold badges130 silver badges120 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Jack Pincus Over a year ago

Thanks, this filters out the empty strings coming in from the params. The only tweak I had to make was that the joining word should be AND for my purposes. Might not have been clear in my original question. Also worth noting is that ActiveRecord params are not mappable on their own, they have to be passed through strong params, then a to_h params.permit(:something).to_h

Collectives™ on Stack Overflow

ActiveRecord (SQL) query multiple columns only if search string not empty

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related