ActiveRecord's where.not

Rails 4.0 introduced a helpful new method for ActiveRecord queries: where.not. It can make clunky queries easier to read.

Usage

This query:

User.where.not(name: 'Gabe')

is effectively the same as this:

User.where('name != ?', 'Gabe')

It’s “effectively” the same because where.not has some extra juice: it will fully qualify the column name with the table name, continue to work if the table or column get aliased (during a left outer join clause with includes), and will continue to work if the database implementation is switched.

I’ve usually seen it used for NOT NULL queries:

# Old and busted
# User.where('name IS NOT NULL')
# New hotness
User.where.not(name: nil)

But it works with arrays too:

# Without `where.not`
# Something.where("name NOT IN ?", User.unverified.pluck(:name))
# With `where.not`
Something.where.not(name: User.unverified.pluck(:name))

That example takes advantage of the fact that ActiveRecord automatically uses IN (or in this case NOT IN) if the value you’re querying against is an array.

Complex usage

Here’s a more complex example:

class Course < ActiveRecord::Base
  def self.with_no_enrollments_by(student)
    includes(:enrollments).
      references(:enrollments).
      where.not(enrollments: { student_id: student.id })
  end
end

You can ignore the first two lines, which tell ActiveRecord that we’re going through the enrollments table (student has_many :courses, through: :enrollments). The method finds courses where the course has no enrollments by the student. It is the complement to student.courses.

Without where.not, it would look like this:

def with_no_enrollments_by(student)
  includes(:enrollments).
    references(:enrollments).
    where('enrollments.student_id != ?', student.id)
end

I prefer the pure-Ruby approach of the where.not version instead of the string SQL of the latter because it’s easier to read and it’s easier to change later.

What’s next

If you found this post helpful, I recommend our post on null relations or a close reading of the official ActiveRecord docs.