The split_array_nil extension overrides Sequel’s default handling of IN/NOT IN with arrays of values to do specific nil checking. For example,
ds = DB[:table].where(column: [1, nil])
By default, that produces the following SQL:
SELECT * FROM table WHERE (column IN (1, NULL))
However, because NULL = NULL is not true in SQL (it is NULL), this will not return rows in the table where the column is NULL. This extension allows for an alternative behavior more similar to ruby, which will return rows in the table where the column is NULL, using a query like:
SELECT * FROM table WHERE ((column IN (1)) OR (column IS NULL)))
Similarly, for NOT IN queries:
ds = DB[:table].exclude(column: [1, nil]) # Default: # SELECT * FROM table WHERE (column NOT IN (1, NULL)) # with split_array_nils extension: # SELECT * FROM table WHERE ((column NOT IN (1)) AND (column IS NOT NULL)))
To use this extension with a single dataset:
ds = ds.extension(:split_array_nil)
To use this extension for all of a database’s datasets:
DB.extension(:split_array_nil)
Related module: Sequel::Dataset::SplitArrayNil