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 columns​

After joining an association (innerJoin, leftJoin, leftJoinPreload), you can reference its columns in where clauses as 'associationName.column'. preload loads in a separate query, so its columns are not in scope:

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

Array values on array columns​

A bare array passed to where doesn't compile against an array-typed column. 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.