Skip to main content

where

The where method returns an instance of a Query (tethered by type generics back to the originating Dream class) which can then be chained with many statements before final execution.

await User.where({ email: null }).all()
// [User{ email: null }, User{ email: null }, ...]

Basic usage​

where statements will by default compare with strict equivalence. However, it is also possible to pass special expressions. The example below demonstrates the application of an ops.ilike query being applied to your query.

await User.where({ email: ops.ilike('%burpcollaborator.net%') }).destroy()
// 3

For more info on ops expressions, see the ops guide.

Array values (IN clause)​

If an array is passed as a value to a non-array field, the query will select any of the values provided in the array (SQL IN clause):

await User.where({ email: ['hi@hi', 'bye@bye'] }).all()
// [User{ email: 'hi@hi' }, User{ email: 'bye@bye' }]

Associations​

BelongsTo associations can also be provided as arguments. Polymorphic associations will also have their type fields extracted into the query.

await Pet.where({ user }).count()

When you hold the associated instance, prefer filtering by it (where({ user })) over the foreign key (where({ userId: user.id })). For a polymorphic association this is a correctness fix, not just style: the id-only form omits the _type column, so it matches rows across every type that shares that id. Passing the instance sets both columns.

Referencing joined or preloaded columns​

After joining or preloading an association, you can reference its columns in where clauses using 'associationName.column' syntax:

const posts = await user
  .associationQuery('posts')
  .preload('comments')
  .whereAny([{ body: null }, { 'comments.body': null }])
  .all()

Array values on array columns​

A bare array passed to where always means IN, even against an array-typed column — it never becomes containment or array equality on its own. To check whether an array column contains an element, or is exactly equal to another array, use ops.any / ops.equal.

Bounded ranges​

For a single column with a natural lower and/or upper bound, prefer the range helper from @rvoh/dream/utils over two separate comparison operators — see the range guide. For two-column interval overlap checks, ops.lessThan / ops.greaterThan (and their OrEqualTo variants) are often clearer, since each boundary is visible at the call site.