QueryBuilder
QueryBuilder
is one of the most powerful features of TypeORM - it allows you to build SQL queries using elegant and convenient syntax, execute them and get automatically transformed entities.QueryBuilder
:User
:QueryBuilder
QueryBuilder
, you need to provide unique parameters in your WHERE
expressions. This will not work::sheepId
and :cowId
instead of using :id
twice for different parameters.QueryBuilder
Query Builder
:QueryBuilder
types available:SelectQueryBuilder
- used to build and execute SELECT
queries. Example:InsertQueryBuilder
- used to build and execute INSERT
queries. Example:UpdateQueryBuilder
- used to build and execute UPDATE
queries. Example:DeleteQueryBuilder
- used to build and execute DELETE
queries. Example:RelationQueryBuilder
- used to build and execute relation-specific operations [TBD].QueryBuilder
getOne
:getOneOrFail
will get a single result from the database, but if no result exists it will throw an EntityNotFoundError
:getMany
:getOne
and getMany
. But sometimes you need to select some specific data, let's say the sum of all user photos. This data is not an entity, it's called raw data. To get raw data, you use getRawOne
and getRawMany
. Examples:createQueryBuilder("user")
. But what is "user"? It's just a regular SQL alias. We use aliases everywhere, except when we work with selected data.createQueryBuilder("user")
is equivalent to:users
is the table name, and user
is an alias we assign to this table. Later we use this alias to access the table:user
alias we assigned when we created a query builder.where("user.name = :name", { name: "Timber" })
. What does { name: "Timber" }
stand for? It's a parameter we used to prevent SQL injection. We could have written: where("user.name = '" + name + "')
, however this is not safe, as it opens the code to SQL injections. The safe way is to use this special syntax: where("user.name = :name", { name: "Timber" })
, where :name
is a parameter name and the value is specified in an object: { name: "Timber" }
.WHERE
expressionWHERE
expression is as easy as:AND
into an existing WHERE
expression:OR
into an existing WHERE
expression:IN
query with the WHERE
expression:WHERE
expression into an existing WHERE
using Brackets
WHERE
expression into an existing WHERE
using NotBrackets
AND
and OR
expressions as you need. If you use .where
more than once you'll override all previous WHERE
expressions.orWhere
- if you use complex expressions with both AND
and OR
expressions, keep in mind that they are stacked without any pretences. Sometimes you'll need to create a where string instead, and avoid using orWhere
.HAVING
expressionHAVING
expression is easy as:AND
into an exist HAVING
expression:OR
into a exist HAVING
expression:AND
and OR
expressions as you need. If you use .having
more than once you'll override all previous HAVING
expressions.ORDER BY
expressionORDER BY
expression is easy as:.orderBy
more than once you'll override all previous ORDER BY
expressions.DISTINCT ON
expression (Postgres only)DISTINCT ON
expression is easy as:GROUP BY
expressionGROUP BY
expression is easy as:addGroupBy
:.groupBy
more than once you'll override all previous GROUP BY
expressions.LIMIT
expressionLIMIT
expression is easy as:take
instead.OFFSET
expressionOFFSET
expression is easy as:skip
instead.leftJoinAndSelect
automatically loaded all of Timber's photos. The first argument is the relation you want to load and the second argument is an alias you assign to this relation's table. You can use this alias anywhere in query builder. For example, let's take all Timber's photos which aren't removed.INNER JOIN
instead of LEFT JOIN
just use innerJoinAndSelect
instead:LEFT JOIN
and INNER JOIN
is that INNER JOIN
won't return a user if it does not have any photos. LEFT JOIN
will return you the user even if it doesn't have photos. To learn more about different join types, refer to the SQL documentation.leftJoin
or innerJoin
:profilePhoto
to User
entity and you can map any data into that property using QueryBuilder
:user.profilePhoto
. If you want to load and map a single entity use leftJoinAndMapOne
. If you want to load and map multiple entities use leftJoinAndMapMany
.QueryBuilder
. To do so, use getSql
:printSql
:getOne
and getMany
. However, sometimes you need to select specific data, like the sum of all user photos. Such data is not a entity, it's called raw data. To get raw data, you use getRawOne
and getRawMany
. Examples:stream
which returns you a stream. Streaming returns you raw data and you must handle entity transformation manually:take
and skip
may look like we are using limit
and offset
, but they aren't. limit
and offset
may not work as you expect once you have more complicated queries with joins or subqueries. Using take
and skip
will prevent those issues.