---
title: Conditions
description: "Filter a query’s records by their attributes."
seo:
  description: "Filter Active Record queries with where, whereNot, or, comparisons like gte and between, sql fragments, rewhere, unscope, and excluding, on D1 or Postgres."
---

## At a glance

| To match                      | Write                                  | SQL                         |
| ----------------------------- | -------------------------------------- | --------------------------- |
| equal values                  | `where({ role: "admin" })`             | `role = ?`                  |
| NULL                          | `where({ role: null })`                | `role IS NULL`              |
| any value in a list           | `where({ role: ["admin", "editor"] })` | `role IN (…)`               |
| anything but a value          | `whereNot({ role: "admin" })`          | `NOT (role = ?)`            |
| either of two queries         | `where(a).or(User.where(b))`           | `(…) OR (…)`                |
| a comparison                  | `where({ createdAt: gte(date) })`      | `created_at >= ?`           |
| a range                       | `where({ createdAt: between(a, b) })`  | both ends included          |
| anything else                 | ``where(sql`…`)``                      | your SQL, with values bound |
| a replacement for a condition | `rewhere({ role: "admin" })`           | drops the old `role = ?`    |
| fewer parts of a query        | `unscope("order")`, `only("where")`    | drops the parts named       |
| all but some records          | `excluding(record)`                    | leaves those records out    |

## The Model

The examples use this Model, whose attributes come from its table through the schema `active-record schema` generates:

```ts user.ts
import { ApplicationRecord } from "./application-record";

export class User extends ApplicationRecord {}
```

```ts schema.ts
// Generated by `active-record schema` from migrations/ — don't edit.
// schema: 7ace010c47bc

import { defineSchema, type TableAttributes } from "@forestfuture/active-record";
import { d1 } from "@forestfuture/active-record/d1";

/** users — last changed by 0001_create_users.sql */
export const users = {
  id: { type: "string", null: false, generateId: true },
  email: { type: "string", null: false },
  role: "string",
  isActive: "boolean",
  createdAt: { type: "datetime", null: false },
  updatedAt: { type: "datetime", null: false },
} as const;

/** The database's adapter and binding, its tables (with their primary keys), and the Models built on them. */
export const schema = defineSchema({
  adapter: d1({ binding: "DB" }),
  tables: {
    users: { attributes: users, primaryKey: "id" },
  },
  models: { User: "users" },
});

declare module "../models/user" {
  interface User extends TableAttributes<typeof users, (typeof User)["attributes"]> {}
}
```

```sql D1
CREATE TABLE users (
  id TEXT PRIMARY KEY NOT NULL,
  email TEXT NOT NULL,
  role TEXT,
  is_active BOOLEAN,
  created_at DATETIME NOT NULL,
  updated_at DATETIME NOT NULL
);
```

```sql Postgres
CREATE TABLE users (
  id UUID PRIMARY KEY,
  email TEXT NOT NULL,
  role TEXT,
  is_active BOOLEAN,
  created_at TIMESTAMPTZ NOT NULL,
  updated_at TIMESTAMPTZ NOT NULL
);
```

## Matching values

Pass `where` an object of attributes that must all match:

```ts
import { User } from "./models/user";

const admins = await User.where({ role: "admin", isActive: true }).toArray();

User.where({ role: null }); // role IS NULL
User.where({ role: ["admin", "editor"] }); // role IN (…), any number of values
```

- `null` matches NULL. An array matches any of its values, and is bound as one value, so it can be any length.
- Values are [cast to the attribute’s type](/attribute-types#casting-assigned-values), so text from a query string works as it is: `where({ isActive: "false" })` finds inactive users. A value that can’t be cast matches nothing: `findBy` resolves to `null`, and `find` throws `RecordNotFound`.
- `undefined` throws `TypeError`. Leave the key out, or use `null`.
- A value of an attribute with a [normalization](/models#normalizing-attributes) is normalized first, so `findBy({ email: " Jane@Example.com" })` finds `jane@example.com`.

Attribute names are checked when you compile, and a misspelled one that gets past the compiler throws `UnknownAttribute`:

```ts
import { User } from "./models/user";

User.where({ rol: "admin" });
```

## Negating conditions

Negate a condition with `whereNot`, or combine two queries with `or`:

```ts
import { User } from "./models/user";

const others = await User.whereNot({ role: "admin" }).toArray();
const either = await User.where({ role: "admin" })
  .or(User.where({ isActive: false }))
  .toArray();
```

- `whereNot` negates the whole condition. As in SQL, `whereNot({ role: "admin" })` also leaves out users whose role is NULL.
- `or` combines two Relations of the same Model that differ only in their conditions.

:::note
In a list, values that can’t be cast are left out. So `whereNot({ id: "abc" })` on an integer key excludes nothing.
:::

## Comparing values

Pass a comparison helper as a value:

```ts
import { between, gte, lt } from "@forestfuture/active-record";
import { User } from "./models/user";

const since = new Date("2026-01-01");
const until = new Date("2026-02-01");

User.where({ createdAt: gte(since) }); // created_at >= ?
User.where({ createdAt: lt(since) }); // created_at < ?
User.where({ createdAt: between(since, until) }); // both ends included
User.where({ createdAt: between(since, until, { excludeEnd: true }) });
```

`gt`, `gte`, `lt`, `lte`, and `between` work on number, string, and datetime attributes, and dates compare by time. `between` includes both ends, unless you pass `{ excludeEnd: true }`.

## Writing SQL fragments

Write anything else as an `sql` tagged template, with `column()` for an attribute’s column:

```ts
import { column, sql } from "@forestfuture/active-record";
import { User } from "./models/user";

const email = "Jane@Example.com";
const user = await User.findBy(sql`lower(${column("email")}) = lower(${email})`);
```

Values are always bound, as they are, so a `Date` is ISO text. To write a value the way its attribute stores it, use `column(name).value(v)`:

```ts
import { column, sql } from "@forestfuture/active-record";
import { User } from "./models/user";

const createdAt = column("createdAt");
const newcomers = await User.where(
  sql`${createdAt} >= ${createdAt.value(new Date("2026-01-01"))}`,
).toArray();
```

`value` works for any attribute, and writes a boolean as 0 or 1 and a JSON value as text. It casts as assigning would, so a value the attribute can’t hold throws `TypeError` naming the attribute, when `where` is given the fragment. A filtered attribute’s value is hidden from `onQuery`, as it is anywhere else.

:::warning[Datetimes stored as Unix time]
For a datetime [stored as a Unix time](/adopt-a-database#reading-unix-timestamps), a bare `Date`, or an ISO datetime string, beside its column throws `TypeError`. Inside an expression, such as `abs(${column("publishedAt")} - ${date})`, or an `IN` list, nothing can tell, and the integer is compared with text: `>` matches nothing and `<` matches every row. Use `value` there.
:::

## Replacing conditions

Replace the conditions on some attributes with `rewhere`:

```ts
import { User } from "./models/user";

const members = User.where({ role: "member", isActive: true });
const activeAdmins = await members.rewhere({ role: "admin" }).toArray(); // is_active = ? AND role = ?
```

Conditions on other attributes stay, and so do `sql` fragments. A default scope’s condition on the attribute is replaced too. A `whereNot` or `or` is replaced only if it names no other attributes.

Drop parts of a query with `unscope`, or keep only some with `only`:

```ts
import { User } from "./models/user";

const recent = User.where({ role: "admin" }).order({ createdAt: "desc" }).limit(10);
recent.unscope("order", "limit"); // every admin, unordered
recent.unscope({ where: "role" }); // the 10 newest users
recent.only("where"); // every admin
```

- The parts are `"where"`, `"order"`, `"limit"`, `"offset"`, `"select"`, `"distinct"`, `"includes"`, `"joins"`, `"leftJoins"`, and `"createWith"`. `{ where: "role" }` drops only the conditions on those attributes.
- A default scope’s parts are dropped too. To start without the default scope, use [`unscoped()`](/scopes#leaving-the-default-scope-out).
- On an association, the link to its owner stays: `user.posts().unscope("where")` is still the user’s posts, and so is `rewhere({ userId })`. Start from `Post` to read past the owner.
- Merging a Relation built with `unscope` drops those parts from the Relation it’s merged into.
- A join can’t be dropped while a merged condition or order uses it. That throws `IncompatibleRelation`.

## Leaving records out

Leave out records you already have with `excluding`:

```ts
import { User } from "./models/user";

const me = await User.findByOrThrow({ email: "jane@example.com" });
const everyoneElse = await User.excluding(me).toArray();
```

It takes records of the Relation’s Model, or arrays of them. A record that isn’t saved yet leaves nothing out.

> **Compared with Rails**
>
> `rewhere`, `unscope`, `only`, and `excluding` work as Rails’ do, with the parts named as strings. `group` and `having` aren’t parts, since a grouped Relation is a type of its own. `invert_where` and `without` aren’t offered, and scopes cover `extending`.

> **Beyond Rails**
>
> On an association, Rails’ `unscope(:where)` drops the link to its owner, so a read can reach records that `create` wouldn’t link to it. Here no rewrite drops the link, so reads and writes agree.
