TonyGW TonyGW - 1 year ago 68
Ruby Question

Rails 4: model.where.not does not return nil records

I've done a google search and found similar questions to my question here, however, I am still lost as to what the solution should be. My case is following:

I have a database table called

where it has
(foreign key to table
) and
(varchar 255). I need to query this table by
but excluding certain author's name. Example: get all the books for category_id = 10 but excluding books by author John Doe:

books = Book.where(category_id: 10).where.not(author: 'John Doe')


books = Book.where(category_id: 10).where("author != ?", "John Doe")

These two queries work for me except on those records whose author is
. I also need to have these queries return records that have

Answer Source

You were on the right track with the last query.

Book.where(category_id: 10).where("author != ? OR author IS NULL", "John Doe")

This should do what you are asking.

Side note, in Rails 5, you can chain ActiveRecord queries, so you could so something like:

Book.where(category_id: 10).where.not(author: 'John Doe').or(where(author: nil))

Though I'd argue, in this case, the first query is clearer.