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

operator	name	description
$eq	equals	Matches values that are equal to a specified value.
$gt	greater	Matches values that are greater than a specified value.
$gte	greater or equal	Matches values that are greater than or equal to a specified value.
$in	contains	Matches any of the values specified in an array.
$lt	lower	Matches values that are less than a specified value.
$lte	lower or equal	Matches values that are less than or equal to a specified value.
$ne	not equal	Matches all values that are not equal to a specified value.
$nin	not contains	Matches none of the values specified in an array.
$like	like	Uses LIKE operator
$re	regexp	Uses REGEXP operator. See info below
$fulltext	full text	A driver specific full text search function. See requirements below
$ilike	ilike	(postgres only)
$overlap	&&	(postgres only)
$contains	@>	(postgres only)
$contained	<@	(postgres only)

Logical

operator	description
$and	Joins query clauses with a logical AND returns all documents that match the conditions of both clauses.
$not	Inverts the effect of a query expression and returns documents that do not match the query expression.
$or	Joins 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:

operator	description
$some	Finds collections that have some record matching the condition.
$none	Finds collections that have no records matching the condition.
$every	Finds 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 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). 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.

Using a column

./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' } }).

Using an index

./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(<column1>, <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' } })