TonyGW TonyGW - 4 months ago 13
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

Book
where it has
category_id
(foreign key to table
Category
) and
author
(varchar 255). I need to query this table by
category_id
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')


or

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


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

Answer

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.

Comments