Skip to main content
Version: 6.4

Query Conditions

When you want to make complex queries, we can easily end up with a lot of boilerplate code full of curly brackets:

const res = await orm.em.find(Author, { $and: [
{ id: { $in: [1, 2, 7] }, },
{ id: { $nin: [3, 4] }, },
{ id: { $gt: 5 }, },
{ id: { $lt: 10 }, },
{ id: { $gte: 7 }, },
{ id: { $lte: 8 }, },
{ id: { $ne: 9 }, },
] });

For AND condition with single field, we can also do this:

const res = await orm.em.find(Author, {
id: {
$in: [1, 2, 7],
$nin: [3, 4],
$gt: 5,
$lt: 10,
$gte: 7,
$lte: 8,
$ne: 9,
},
});

There is also shortcut for $in - simply provide array as value, and it will be converted automatically:

const res = await orm.em.find(Author, { favouriteBook: [1, 2, 7] });

For primary key lookup, we can provide the array directly to em.find():

const res = await orm.em.find(Author, [1, 2, 7]);

List of supported operators

Comparison

operatornamedescription
$eqequalsMatches values that are equal to a specified value.
$gtgreaterMatches values that are greater than a specified value.
$gtegreater or equalMatches values that are greater than or equal to a specified value.
$incontainsMatches any of the values specified in an array.
$ltlowerMatches values that are less than a specified value.
$ltelower or equalMatches values that are less than or equal to a specified value.
$nenot equalMatches all values that are not equal to a specified value.
$ninnot containsMatches none of the values specified in an array.
$likelikeUses LIKE operator
$reregexpUses REGEXP operator. See info below
$fulltextfull textA driver specific full text search function. See requirements below
$ilikeilike(postgres only)
$overlap&&(postgres only)
$contains@>(postgres only)
$contained<@(postgres only)
$hasKey?(postgres only)
$hasSomeKeys?|(postgres only)
$hasKeys?&(postgres only)

Logical

operatordescription
$andJoins query clauses with a logical AND returns all documents that match the conditions of both clauses.
$notInverts the effect of a query expression and returns documents that do not match the query expression.
$orJoins query clauses with a logical OR returns all documents that match the conditions of either clause.

Collection

In addition to the regular operators that translate to a real SQL operator expression (e.g. >=), you can also use the following collection operators:

operatordescription
$someFinds collections that have some record matching the condition.
$noneFinds collections that have no records matching the condition.
$everyFinds collections where every record is matching the condition.

This will be resolved as a subquery condition:

// finds all authors that have some book called `Foo`
const res1 = await em.find(Author, {
books: { $some: { title: 'Foo' } },
});

// finds all authors that have no books called `Foo`
const res2 = await em.find(Author, {
books: { $none: { title: 'Foo' } },
});

// finds all authors that have every book called `Foo`
const res3 = await em.find(Author, {
books: { $every: { title: 'Foo' } },
});

The condition object can be also empty:

// finds all authors that have at least one book
const res1 = await em.find(Author, {
books: { $some: {} },
});

// finds all authors that have no books
const res2 = await em.find(Author, {
books: { $none: {} },
});

Regular Expressions

The $re operator takes a string as input value, and by default uses the case-sensitive operator. If you would like to use a RegExp object, i.e. to be able to set flags, then search directly on the field name without using the operator:

const res = await em.find(Painter, {
lastName: /m[oa]net/i, // or `new RegExp('m[oa]net', 'i')`
});

Full text searching

Full-text search refers to searching some text inside extensive text data stored and returning results that contain some or all of the words from the query. In contrast, traditional search would return exact matches.

The implementation and requirements differs per driver so it's important that fields are set up correctly.

PostgreSQL

PosgreSQL allows to execute queries (pg-query) on the type pg-vector. The pg-vector type can be a column (more performant) or be created in the query (no excess columns in the database). When using a column, advanced functionality such as a custom regconfig or setweight (the default regconfig is simple) is also supported.

Refer to the PostgreSQL documentation for possible queries.

./entities/Book.ts
import { FullTextType, WeightedFullTextValue } from '@mikro-orm/postgresql';

@Entity()
export class Book {

@Property()
title!: string;

// example when using default settings
@Index({ type: 'fulltext' })
@Property({ type: FullTextType, onUpdate: (book) => book.title })
searchableTitle!: string;

// example when using a custom regconfig
@Index({ type: 'fulltext' })
@Property({ type: new FullTextType('english'), onUpdate: (book) => book.title })
searchableTitle!: string;

// example when using weights
@Index({ type: 'fulltext' })
@Property({ type: FullTextType, onUpdate: (book) => ({ A: book.title, B: book.description }) })
searchableTitle!: WeightedFullTextValue;
}

And to find results: repository.findOne({ searchableTitle: { $fulltext: 'query' } }).

MySQL, MariaDB

MySQL and MariaDB allow full text searches on all columns with a fulltext index.

Refer to the MySQL documentation or MariaDB documentation for possible queries.

./entities/Book.ts
@Entity()
export class Book {

@Index({ type: 'fulltext' })
@Property()
title!: string;

}

And to find results: repository.findOne({ title: { $fulltext: 'query' } })

MongoDB

MongoDB allows full text searches on all columns with a text index. However, when executing a full text search, it selects matches based on all fields with a text index: it's only possible to add one query and only on the top-level of the query object. Refer to the MongoDB documentation for more information on this behavior.

Refer to the MongoDB documentation for possible queries.

./entities/Book.ts
@Entity()
export class Book {

@Index({ type: 'fulltext' })
@Property()
title!: string;

}

SQLite

In SQLite, full text searches can only be executed on FTS5 virtual tables. MikroORM can't create this table, and has to be done manually. Simple tables can be created with this query:

CREATE VIRTUAL TABLE <table name> USING fts5(<column1>, <column2>, ...);

Afterwards an entity can create normally for the structure of this table. The @Index is not necessary for full text searches in SQLite.

Refer to the SQLite documentation for possible queries.

./entities/Book.ts
@Entity()
export class Book {

@PrimaryKey()
id!: number;

@Property()
title!: string;

}

And to find results: repository.findOne({ title: { $fulltext: 'query' } })