Skip to main content
Version: Next

Smart 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
$fulltextfull textA driver specific full text search function. See requirements below
$ilikeilike(postgres only)
$overlap&&(postgres only)
$contains@>(postgres only)
$contained<@(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.

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 setup 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).

Refer to the PostgreSQL documentation for possible queries.

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

@Entity()
export class Book {

@Property()
title!: string;

@Index({ type: 'fulltext' })
@Property({ type: FullTextType, onUpdate: (book) => book.title })
searchableTitle!: string;

}

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

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

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

}

And to find results: repository.findOne({ title: { $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(<colum1>, <column2>, ...);

Afterwards an entity can created 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' } })