---
title: ActiveRecord's where.not
teaser: Make clunky negation queries in ActiveRecord easier to read.
tags: web,rails
author: Gabe Berke-Williams
published_on: 2014-01-24
---

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

## Usage

This query:

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

is effectively the same as this:

```ruby
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:

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

But it works with arrays too:

```ruby
# 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:

```ruby
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:

```ruby
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][official docs].

[null relations]: https://thoughtbot.com/blog/handling-associations-on-null-objects
[official docs]: http://guides.rubyonrails.org/active_record_querying.html