Rails4: Is it possible to do an optional ActiveRecord search?

Question

Say i had a record in my database like

+----+-----------+----------+
| id | firstname | lastname |
+----+-----------+----------+
|  1 | 'Bill'    | nil      |
+----+-----------+----------+

(note last name is nil)

Is there any where I can retrieve the above record using the following hash structure as search parameters:

vals = {firstname: "Bill", lastname: "test"}
Table.where(vals)

(ie: find the closest match, ignoring the nil column value in the table)

(I'm thinking of checking each key in the hash individually and stopping when a match is found, but just wondering if there is a more efficient way, specially for larger tables)

If you want fuzzy matches, SQL is not the best tool for the job. — BroiSatse, Feb 03 '16 at 13:26
This is now possible in Rails 5: http://stackoverflow.com/questions/32753168/rails-5-activerecord-or-query — maniacalrobot, Feb 03 '16 at 13:53

Nermin · Accepted Answer · 2016-02-04T09:03:24.630

You could make custom search.

def self.optional_where params
   query_params = params.keys.map do |k|
      "(#{k} = ? OR #{k} IS NULL)"
   end.join(" AND ")
   where(query_params, *params.values)
end

Then you would use it like

Table.optional_where(vals)

This will produce next query

SELECT "tables".* FROM "tables" WHERE ((firstname = 'Bill' OR first_name IS NULL) AND (lastname = 'test' OR last_name IS NULL))

score 0 · Answer 2 · answered Feb 03 '16 at 15:27

Let make a custom search like this:

scope :custom_search, -> (params) {
  params.each do |k, v|
    params[k] = if
      if v.is_a? Array
        (v << nil).uniq
      else
        [v, nil]
      end
    where(params)
  end
}

Then we use it like:

search_params = {firstname: "Bill", lastname: "test"}
Table.custom_search(search_params)

The generated sql will be:

SELECT * FROM tables where firstname IN ['Bill', null] AND lastname IN ['test', null]

This means you don't care if one or more fields are nil

Rails4: Is it possible to do an optional ActiveRecord search?

2 Answers2