Filters and Operators

Mirror’s where supports both simple equality and operators for complex comparisons. All operators are functions imported from mirror-orm and are fully typed.

Simple Equality

Values passed directly generate an equality comparison (=):

await repo.find({
  where: {
    status: 'active',
    role:   'admin',
  }
  // WHERE status = $1 AND role = $2
});

AND / OR logic

AND — single object

Conditions within the same object are joined with AND:

where: { role: 'admin', active: true }
// WHERE role = $1 AND active = $2

OR — array of objects

Passing an array joins each object with OR. Within each object, the conditions continue to be AND:

where: [
  { role: 'admin' },
  { role: 'moderator', active: true },
]
// WHERE (role = $1) OR (role = $2 AND active = $3)

Comparison Operators

import { MoreThan, MoreThanOrEqual, LessThan, LessThanOrEqual, Not, Between } from 'mirror-orm';

Operator	Generated SQL	Example
`MoreThan(v)`	`col > $N`	`MoreThan(18)`
`MoreThanOrEqual(v)`	`col >= $N`	`MoreThanOrEqual(18)`
`LessThan(v)`	`col < $N`	`LessThan(65)`
`LessThanOrEqual(v)`	`col <= $N`	`LessThanOrEqual(65)`
`Not(v)`	`col != $N`	`Not('banned')`
`Between(a, b)`	`col BETWEEN $N AND $M`	`Between(18, 30)`

await repo.find({
  where: {
    age:   Between(18, 60),
    score: MoreThanOrEqual(70),
    role:  Not('banned'),
  }
});

String Operators

import { Like, ILike } from 'mirror-orm';

Operator	Generated SQL	Behavior
`Like(v)`	`col LIKE $N`	Case-sensitive, supports `%` and `_`
`ILike(v)`	`col ILIKE $N`	Case-insensitive — PostgreSQL only

await repo.find({ where: { name: Like('%silva%') } });
// WHERE name LIKE $1  →  $1 = '%silva%'

await repo.find({ where: { email: ILike('%@GMAIL.COM') } });
// WHERE email ILIKE $1

Array Operators

import { In } from 'mirror-orm';

Operator	Generated SQL
`In([...])`	`col IN ($1, $2, ...)`

await repo.find({
  where: { status: In(['active', 'pending', 'review']) }
});
// WHERE status IN ($1, $2, $3)

There is no NotIn. For the opposite effect, use Raw: Raw(col => \${col} NOT IN (‘a’, ‘b’)`)`

Null Operators

import { IsNull, IsNotNull } from 'mirror-orm';

Operator	Generated SQL
`IsNull()`	`col IS NULL`
`IsNotNull()`	`col IS NOT NULL`

await repo.find({ where: { deletedAt: IsNull() } });
// WHERE deleted_at IS NULL

await repo.find({ where: { verifiedAt: IsNotNull() } });
// WHERE verified_at IS NOT NULL

JSON Operators — PostgreSQL

Only available for JSONB columns in PostgreSQL.

import { JsonContains, JsonHasKey, JsonHasAllKeys, JsonHasAnyKey } from 'mirror-orm';

Operator	Generated SQL	Description
`JsonContains(obj)`	`col @> $N::jsonb`	Document contains partial object
`JsonHasKey(key)`	`col ? $N`	Document has the key
`JsonHasAllKeys([...])`	`col ?& $N`	Document has all keys
`JsonHasAnyKey([...])`	`col ?	$N`

// Busca usuários com role 'admin' dentro do JSON
await repo.find({ where: { meta: JsonContains({ role: 'admin' }) } });
// WHERE meta @> $1::jsonb   →   $1 = '{"role":"admin"}'

// Busca usuários cujo JSON tem a chave 'preferences'
await repo.find({ where: { meta: JsonHasKey('preferences') } });

// Busca usuários cujo JSON tem ambas as chaves 'theme' e 'lang'
await repo.find({ where: { meta: JsonHasAllKeys(['theme', 'lang']) } });

Using JSON operators with MySQL, SQLite or SQL Server throws an error at runtime.

`Raw` — Custom SQL

For any condition that operators do not cover. Takes a function that accepts the qualified column name and returns the complete SQL expression.

import { Raw } from 'mirror-orm';

// Comparação com subquery
await repo.find({
  where: {
    score: Raw(col => `${col} > (SELECT AVG(score) FROM results)`),
  }
});
// WHERE "results"."score" > (SELECT AVG(score) FROM results)

// Expressão de data nativa do banco
await repo.find({
  where: {
    createdAt: Raw(col => `${col} > NOW() - INTERVAL '7 days'`),
  }
});

Attention: Raw does not accept external parameters — values must be interpolated directly into the string. If you need to pass dynamic values safely, use QueryBuilder. See Query Builder.

Combining Operators

Multiple operators on the same object are composed with AND:

await repo.find({
  where: {
    age:       Between(18, 60),
    name:      ILike('%ana%'),
    deletedAt: IsNull(),
    status:    In(['active', 'verified']),
  }
});
// WHERE age BETWEEN $1 AND $2
//   AND name ILIKE $3
//   AND deleted_at IS NULL
//   AND status IN ($4, $5)

Combining with OR into groups:

await repo.find({
  where: [
    { role: 'admin', deletedAt: IsNull() },
    { role: 'superuser' },
  ]
});
// WHERE (role = $1 AND deleted_at IS NULL)
//    OR (role = $2)

Named Filters

Reusable filters defined directly in @Entity. Useful for conditions that repeat throughout the application, such as multi-tenancy or publishing status.

import { IsNull, MoreThan } from 'mirror-orm';

@Entity('posts', {
  filters: {
    published: { status: 'published', deletedAt: IsNull() },
    recent:    { createdAt: MoreThan(new Date(Date.now() - 7 * 86400_000)) },
  }
})
class Post {
  // ...
}

Activated by name in the query:

// Ativa apenas 'published'
await repo.find({ filters: ['published'] });

// Ativa os dois — combinados com AND
await repo.find({ filters: ['published', 'recent'] });

// Filtros nomeados podem ser combinados com where normal
await repo.find({
  filters: ['published'],
  where:   { authorId: 42 },
});
// WHERE status = 'published' AND deleted_at IS NULL AND author_id = $1

Quick Reference| Operator | Import from | PostgreSQL | MySQL | SQLite | MSSQL |

|---|---|---|---|---|---| | MoreThan | mirror-orm | ✓ | ✓ | ✓ | ✓ | | MoreThanOrEqual | mirror-orm | ✓ | ✓ | ✓ | ✓ | | LessThan | mirror-orm | ✓ | ✓ | ✓ | ✓ | | LessThanOrEqual | mirror-orm | ✓ | ✓ | ✓ | ✓ | | Not | mirror-orm | ✓ | ✓ | ✓ | ✓ | | Between | mirror-orm | ✓ | ✓ | ✓ | ✓ | | Like | mirror-orm | ✓ | ✓ | ✓ | ✓ | | ILike | mirror-orm | ✓ | ✗ | ✗ | ✗ | | In | mirror-orm | ✓ | ✓ | ✓ | ✓ | | IsNull | mirror-orm | ✓ | ✓ | ✓ | ✓ | | IsNotNull | mirror-orm | ✓ | ✓ | ✓ | ✓ | | JsonContains | mirror-orm | ✓ | ✗ | ✗ | ✗ | | JsonHasKey | mirror-orm | ✓ | ✗ | ✗ | ✗ | | JsonHasAllKeys | mirror-orm | ✓ | ✗ | ✗ | ✗ | | JsonHasAnyKey | mirror-orm | ✓ | ✗ | ✗ | ✗ | | Raw | mirror-orm | ✓ | ✓ | ✓ | ✓ |