matches_any throws exception on empty array writing custom filter for Datatables using Arel

Viewed 247

I have am working with Ruby on Rails using the ajax-datatables-rails gem for datatables for which I need a custom filter.

My current filter looks like this:

def filter_status_column
  statuses = []
  ->(column, value) do
    # Do stuff and put statuses in the array
    ::Arel::Nodes::SqlLiteral.new(column.field.to_s).matches_any(statuses)
  end
end

This, when my array is not empty will generate some sql like this:

0> ::Arel::Nodes::SqlLiteral.new(column.field.to_s).matches_any(like_mapped_values).to_sql
=> "(status ILIKE 'Foo' OR status ILIKE 'Bar')"

If the array this causes an exception which my expectation is after running .where("status in ?", []) against the model is like this as that turns [] to null:

"(status ILIKE NULL)"

Calling

::Arel::Nodes::SqlLiteral.new(column.field.to_s).matches_any([]).to_sql

generates the error

Unsupported argument type: NilClass. Construct an Arel node instead.

What is the correct method to handle an empty array in matches_any? I could also do this without arel I suppose as well. Status is just a column on a model

EDIT: Further background, this datatable on the UI side has a search box and this field differs in text between what's displayed and what's actually in the database. This custom filter takes the distinct values from the database and maps the view text to the meaningful database text. It "likes" the viewed text, takes the match from the database side, and needs to apply filters as it goes. So, a partial match on the view text is matched to the actual database match. This means there could be no database matches and match_any? pukes on that.

1 Answers

Querying for "status matches an empty array" is ambiguous, depending on your use case it may mean several things, you may want to:

  • match all possible statuses
  • match no rows at all
  • match rows that have a NULL status
  • match all possible statuses except NULL

You should therefore check if statuses is empty and return a query that matches what you would like to do. Either:

  • not adding a filter (or 1=1)
  • filter out everything (or 1=0)
  • status IS NULL
  • status IS NOT NULL
Related