Jack Pincus Jack Pincus -3 years ago 117
SQL Question

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

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 ?",

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'.

Answer Source

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.

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download