Using Query Builder
info
Since v4, we need to make sure we are working with correctly typed EntityManager
or EntityRepository
to have access to createQueryBuilder()
method.
When you need to execute some SQL query without all the ORM stuff involved, you can either
compose the query yourself, or use the QueryBuilder
helper to construct the query for you:
QueryBuilder
also supports smart query conditions.
#
Using Knex.jsUnder the hood, QueryBuilder
uses Knex.js
to compose and run queries.
You can access configured knex
instance via qb.getKnexQuery()
method:
You can also get clear and configured knex instance from the connection via getKnex()
method.
As this method is not available on the base Connection
class, you will need to either manually
type cast the connection to AbstractSqlConnection
(or the actual implementation you are using,
e.g. MySqlConnection
), or provide correct driver type hint to your EntityManager
instance,
which will be then automatically inferred in em.getConnection()
method.
Driver and connection implementations are not directly exported from
@mikro-orm/core
module. You can import them from the driver packages (e.g.import { PostgreSqlDriver } from '@mikro-orm/postgresql'
).
#
Running Native SQL QueryYou can run native SQL via underlying connection
#
Executing the QueryYou can use execute(method = 'all', mapResults = true)
's parameters to control form of result:
Second argument can be used to disable mapping of database columns to property names (which
is enabled by default). In following example, Book
entity has createdAt
property defined
with implicit underscored field name created_at
:
To get entity instances from the QueryBuilder result, you can use getResult()
and getSingleResult()
methods:
You can also use
qb.getResultList()
which is alias toqb.getResult()
.
#
Mapping Raw Results to EntitiesAnother way to create entity from raw results (that are not necessarily mapped to entity properties)
is to use map()
method of EntityManager
, that is basically a shortcut for mapping results
via IDatabaseDriver.mapResult()
(which converts field names to property names - e.g. created_at
to createdAt
) and merge()
which converts the data to entity instance and makes it managed.
This method comes handy when you want to use 3rd party query builders, where the result is not mapped to entity properties automatically:
#
Implicit JoiningQueryBuilder
supports automatic joining based on entity metadata:
This also works for multiple levels of nesting:
This is currently available only for filtering (where
) and sorting (orderBy
), only
the root entity will be selected. To populate its relationships, you can use em.populate()
.
#
Explicit JoiningAnother way is to manually specify join property via join()
/leftJoin()
methods:
#
Mapping joined resultsTo select multiple entities and map them from QueryBuilder
, we can use
joinAndSelect
or leftJoinAndSelect
method:
#
Complex Where ConditionsThere are multiple ways to construct complex query conditions. You can either write parts of SQL
manually, use andWhere()
/orWhere()
, or provide condition object:
#
Using custom SQL fragmentsIt is possible to use any SQL fragment in your WHERE
query or ORDER BY
clause:
This will produce following query:
#
Custom SQL in where#
andWhere() and orWhere()#
Conditions Object#
Using sub-queriesYou can filter using sub-queries in where conditions:
For sub-queries in selects, use the qb.as(alias)
method:
The dynamic property (
booksTotal
) needs to be defined at the entity level (aspersist: false
).
When you want to filter by sub-query on the left-hand side of a predicate, you will need to register it first via qb.withSubquery()
:
The dynamic property (
booksTotal
) needs to be defined at the entity level (aspersist: false
). You always need to use prefix in theqb.withSchema()
(soa.booksTotal
).
#
Referring to column in update queriesYou can use qb.raw()
to insert raw SQL snippets like this: