Выборка с помощью Query Builder

Неофициальный Бета-перевод

Эта страница переведена PageTurner AI (бета). Не одобрена официально проектом. Нашли ошибку? Сообщить о проблеме →

Что такое QueryBuilder?

QueryBuilder — одна из самых мощных функций TypeORM, позволяющая строить SQL-запросы с помощью элегантного и удобного синтаксиса, выполнять их и получать автоматически преобразованные сущности.

Простой пример использования QueryBuilder:

const firstUser = await dataSource
    .getRepository(User)
    .createQueryBuilder("user")
    .where("user.id = :id", { id: 1 })
    .getOne()

Он создаёт следующий SQL-запрос:

SELECT
    user.id as userId,
    user.firstName as userFirstName,
    user.lastName as userLastName
FROM users user
WHERE user.id = 1

и возвращает вам экземпляр User:

User {
    id: 1,
    firstName: "Timber",
    lastName: "Saw"
}

Важное замечание при использовании `QueryBuilder`

При использовании QueryBuilder необходимо указывать уникальные параметры в выражениях WHERE. Такой подход не сработает:

const result = await dataSource
    .getRepository(User)
    .createQueryBuilder("user")
    .leftJoinAndSelect("user.linkedSheep", "linkedSheep")
    .leftJoinAndSelect("user.linkedCow", "linkedCow")
    .where("user.linkedSheep = :id", { id: sheepId })
    .andWhere("user.linkedCow = :id", { id: cowId })

... а такой сработает:

const result = await dataSource
    .getRepository(User)
    .createQueryBuilder("user")
    .leftJoinAndSelect("user.linkedSheep", "linkedSheep")
    .leftJoinAndSelect("user.linkedCow", "linkedCow")
    .where("user.linkedSheep = :sheepId", { sheepId })
    .andWhere("user.linkedCow = :cowId", { cowId })

Обратите внимание, что мы использовали уникальные имена :sheepId и :cowId вместо повторного использования :id для разных параметров.

Как создать и использовать QueryBuilder?

Существует несколько способов создания Query Builder:

Использование DataSource:

const user = await dataSource
    .createQueryBuilder()
    .select("user")
    .from(User, "user")
    .where("user.id = :id", { id: 1 })
    .getOne()

Использование Entity Manager:

const user = await dataSource.manager
    .createQueryBuilder(User, "user")
    .where("user.id = :id", { id: 1 })
    .getOne()

Использование репозитория:

const user = await dataSource
    .getRepository(User)
    .createQueryBuilder("user")
    .where("user.id = :id", { id: 1 })
    .getOne()

Доступно 5 типов QueryBuilder:

SelectQueryBuilder — используется для построения и выполнения SELECT-запросов. Пример:

const user = await dataSource
    .createQueryBuilder()
    .select("user")
    .from(User, "user")
    .where("user.id = :id", { id: 1 })
    .getOne()

InsertQueryBuilder — используется для построения и выполнения INSERT-запросов. Пример:

await dataSource
    .createQueryBuilder()
    .insert()
    .into(User)
    .values([
        { firstName: "Timber", lastName: "Saw" },
        { firstName: "Phantom", lastName: "Lancer" },
    ])
    .execute()

UpdateQueryBuilder — используется для построения и выполнения UPDATE-запросов. Пример:

await dataSource
    .createQueryBuilder()
    .update(User)
    .set({ firstName: "Timber", lastName: "Saw" })
    .where("id = :id", { id: 1 })
    .execute()

DeleteQueryBuilder — используется для построения и выполнения DELETE-запросов. Пример:

await dataSource
    .createQueryBuilder()
    .delete()
    .from(User)
    .where("id = :id", { id: 1 })
    .execute()

RelationQueryBuilder — используется для операций, специфичных для связей [TBD]. Пример:
```
await dataSource
    .createQueryBuilder()
    .relation(User, "photos")
    .of(id)
    .loadMany()
```

Вы можете переключаться между разными типами query builder внутри любого из них, и при этом будет создаваться новый экземпляр (в отличие от других методов).

Получение значений с помощью `QueryBuilder`

Для получения одного результата из базы данных, например, пользователя по id или имени, используйте getOne:

const timber = await dataSource
    .getRepository(User)
    .createQueryBuilder("user")
    .where("user.id = :id OR user.name = :name", { id: 1, name: "Timber" })
    .getOne()

getOneOrFail также получает один результат из базы, но если результат отсутствует, выбрасывает исключение EntityNotFoundError:

const timber = await dataSource
    .getRepository(User)
    .createQueryBuilder("user")
    .where("user.id = :id OR user.name = :name", { id: 1, name: "Timber" })
    .getOneOrFail()

Для получения нескольких записей из базы данных, например, всех пользователей, используйте getMany:

const users = await dataSource
    .getRepository(User)
    .createQueryBuilder("user")
    .getMany()

Существует два типа результатов, которые можно получить с помощью построителя запросов: сущности или сырые данные. В большинстве случаев вам нужно выбирать реальные сущности из базы данных, например пользователей. Для этого используются getOne и getMany. Но иногда требуется выбрать специфические данные, например сумму всех фотографий пользователей. Такие данные не являются сущностью — это сырые данные. Для их получения используйте getRawOne и getRawMany. Примеры:

const { sum } = await dataSource
    .getRepository(User)
    .createQueryBuilder("user")
    .select("SUM(user.photosCount)", "sum")
    .where("user.id = :id", { id: 1 })
    .getRawOne()

const photosSums = await dataSource
    .getRepository(User)
    .createQueryBuilder("user")
    .select("user.id")
    .addSelect("SUM(user.photosCount)", "sum")
    .groupBy("user.id")
    .getRawMany()

// result will be like this: [{ id: 1, sum: 25 }, { id: 2, sum: 13 }, ...]

Получение количества записей

Вы можете получить количество строк, которые вернёт запрос, используя getCount(). Этот метод возвращает число вместо результата в виде сущности.

const count = await dataSource
    .getRepository(User)
    .createQueryBuilder("user")
    .where("user.name = :name", { name: "Timber" })
    .getCount()

Что создаст следующий SQL-запрос:

SELECT count(*) FROM users user WHERE user.name = 'Timber'

Для чего нужны псевдонимы (aliases)?

Мы использовали createQueryBuilder("user"). Но что такое "user"? Это обычный SQL-псевдоним. Псевдонимы используются повсеместно, кроме случаев работы с выбираемыми данными.

createQueryBuilder("user") эквивалентно:

createQueryBuilder().select("user").from(User, "user")

Что сгенерирует следующий SQL-запрос:

SELECT ... FROM users user

В этом запросе users — имя таблицы, а user — присвоенный ей псевдоним. В дальнейшем мы используем этот псевдоним для обращения к таблице:

createQueryBuilder()
    .select("user")
    .from(User, "user")
    .where("user.name = :name", { name: "Timber" })

Что создаст следующий SQL-запрос:

SELECT ... FROM users user WHERE user.name = 'Timber'

Обратите внимание: мы обращаемся к таблице users через псевдоним user, заданный при создании построителя запросов.

Один построитель запросов может использовать несколько псевдонимов. Каждый оператор SELECT может иметь собственный псевдоним, можно выбирать из нескольких таблиц с разными псевдонимами, присоединять таблицы с уникальными псевдонимами. Эти псевдонимы используются для доступа к таблицам или выбираемым данным.

Использование параметров для экранирования данных

Мы применили where("user.name = :name", { name: "Timber" }). Что означает { name: "Timber" }? Это параметр для защиты от SQL-инъекций. Можно было написать: where("user.name = '" + name + "'), но это небезопасно, так как открывает уязвимость для инъекций. Безопасный способ — использовать специальный синтаксис: where("user.name = :name", { name: "Timber" }), где :name — имя параметра, а значение задаётся в объекте: { name: "Timber" }.

.where("user.name = :name", { name: "Timber" })

является сокращённой записью для:

.where("user.name = :name")
.setParameter("name", "Timber")

Важно: не используйте одинаковые имена параметров для разных значений в рамках одного построителя запросов. Значения будут перезаписываться.

Также можно передать массив значений, которые преобразуются в список в SQL-запросе, используя специальный синтаксис расширения:

.where("user.name IN (:...names)", { names: [ "Timber", "Crystal", "Lina" ] })

Что преобразуется в:

WHERE user.name IN ('Timber', 'Crystal', 'Lina')

Добавление условий `WHERE`

Добавить условие WHERE просто:

createQueryBuilder("user").where("user.name = :name", { name: "Timber" })

В результате получится:

SELECT ... FROM users user WHERE user.name = 'Timber'

Можно добавить AND к существующему условию WHERE:

createQueryBuilder("user")
    .where("user.firstName = :firstName", { firstName: "Timber" })
    .andWhere("user.lastName = :lastName", { lastName: "Saw" })

Что создаст следующий SQL-запрос:

SELECT ... FROM users user WHERE user.firstName = 'Timber' AND user.lastName = 'Saw'

Можно добавить OR к существующему условию WHERE:

createQueryBuilder("user")
    .where("user.firstName = :firstName", { firstName: "Timber" })
    .orWhere("user.lastName = :lastName", { lastName: "Saw" })

Что создаст следующий SQL-запрос:

SELECT ... FROM users user WHERE user.firstName = 'Timber' OR user.lastName = 'Saw'

Можно выполнить запрос IN в условии WHERE:

createQueryBuilder("user").where("user.id IN (:...ids)", { ids: [1, 2, 3, 4] })

Что создаст следующий SQL-запрос:

SELECT ... FROM users user WHERE user.id IN (1, 2, 3, 4)

Сложное условие WHERE можно добавить к существующему WHERE с помощью Brackets

createQueryBuilder("user")
    .where("user.registered = :registered", { registered: true })
    .andWhere(
        new Brackets((qb) => {
            qb.where("user.firstName = :firstName", {
                firstName: "Timber",
            }).orWhere("user.lastName = :lastName", { lastName: "Saw" })
        }),
    )

Что создаст следующий SQL-запрос:

SELECT ... FROM users user WHERE user.registered = true AND (user.firstName = 'Timber' OR user.lastName = 'Saw')

Вы можете добавить инвертированное сложное условие WHERE в существующее условие WHERE, используя NotBrackets

createQueryBuilder("user")
    .where("user.registered = :registered", { registered: true })
    .andWhere(
        new NotBrackets((qb) => {
            qb.where("user.firstName = :firstName", {
                firstName: "Timber",
            }).orWhere("user.lastName = :lastName", { lastName: "Saw" })
        }),
    )

Что создаст следующий SQL-запрос:

SELECT ... FROM users user WHERE user.registered = true AND NOT((user.firstName = 'Timber' OR user.lastName = 'Saw'))

Вы можете комбинировать сколько угодно выражений AND и OR. Если вы используете .where несколько раз, это перезапишет все предыдущие условия WHERE.

Важно: будьте осторожны с orWhere — при работе со сложными выражениями, содержащими как AND, так и OR, учитывайте, что они объединяются без явных приоритетов. Иногда вместо использования orWhere следует создавать строку условия вручную.

Добавление выражения `HAVING`

Добавить выражение HAVING просто:

createQueryBuilder("user").having("user.name = :name", { name: "Timber" })

Который сгенерирует следующий SQL-запрос:

SELECT ... FROM users user HAVING user.name = 'Timber'

Вы можете добавить AND в существующее выражение HAVING:

createQueryBuilder("user")
    .having("user.firstName = :firstName", { firstName: "Timber" })
    .andHaving("user.lastName = :lastName", { lastName: "Saw" })

Что создаст следующий SQL-запрос:

SELECT ... FROM users user HAVING user.firstName = 'Timber' AND user.lastName = 'Saw'

Вы можете добавить OR в существующее выражение HAVING:

createQueryBuilder("user")
    .having("user.firstName = :firstName", { firstName: "Timber" })
    .orHaving("user.lastName = :lastName", { lastName: "Saw" })

Что создаст следующий SQL-запрос:

SELECT ... FROM users user HAVING user.firstName = 'Timber' OR user.lastName = 'Saw'

Вы можете комбинировать столько выражений AND и OR, сколько вам нужно. Если вы используете .having более одного раза, вы перезапишете все предыдущие выражения HAVING.

Добавление выражения `ORDER BY`

createQueryBuilder("user").orderBy("user.id")

В результате получится:

SELECT ... FROM users user ORDER BY user.id

Вы можете изменить направление сортировки с возрастающего на убывающее (или наоборот):

createQueryBuilder("user").orderBy("user.id", "DESC")

createQueryBuilder("user").orderBy("user.id", "ASC")

Вы можете добавить несколько критериев сортировки:

createQueryBuilder("user").orderBy("user.name").addOrderBy("user.id")

Вы также можете использовать объект для указания порядка сортировки полей:

createQueryBuilder("user").orderBy({
    "user.name": "ASC",
    "user.id": "DESC",
})

Если использовать .orderBy более одного раза, вы перезапишете все предыдущие выражения ORDER BY.

Добавление выражения `DISTINCT ON` (только для Postgres)

При использовании distinct-on с order-by выражением, выражение distinct-on должно соответствовать самому левому order-by. Выражения distinct-on интерпретируются по тем же правилам, что и order-by. Отметим, что использование distinct-on без order-by означает непредсказуемость первой строки в каждом наборе.

Добавить DISTINCT ON просто:

createQueryBuilder("user").distinctOn(["user.id"]).orderBy("user.id")

В результате получится:

SELECT DISTINCT ON (user.id) ... FROM users user ORDER BY user.id

Добавление выражения `GROUP BY`

createQueryBuilder("user").groupBy("user.id")

Что создаст следующий SQL-запрос:

SELECT ... FROM users user GROUP BY user.id

Чтобы добавить дополнительные критерии группировки, используйте addGroupBy:

createQueryBuilder("user").groupBy("user.name").addGroupBy("user.id")

Если вы используете .groupBy более одного раза, вы перезапишете все предыдущие выражения GROUP BY.

Добавление выражения `LIMIT`

createQueryBuilder("user").limit(10)

Что создаст следующий SQL-запрос:

SELECT ... FROM users user LIMIT 10

Результат зависит от типа СУБД (SQL, MySQL, Postgres и др.). Примечание: LIMIT может работать некорректно в сложных запросах с джойнами или подзапросами. Для пагинации рекомендуется использовать take.

Добавление выражения `OFFSET`

Добавить OFFSET просто:

createQueryBuilder("user").offset(10)

Что создаст следующий SQL-запрос:

SELECT ... FROM users user OFFSET 10

Результат зависит от типа СУБД (SQL, MySQL, Postgres и т.д.). Примечание: OFFSET может работать неожиданным образом при использовании сложных запросов с объединениями или подзапросами. Для пагинации рекомендуется использовать skip.

Объединение связей (Joining relations)

Предположим, у вас есть следующие сущности:

import { Entity, PrimaryGeneratedColumn, Column, OneToMany } from "typeorm"
import { Photo } from "./Photo"

@Entity()
export class User {
    @PrimaryGeneratedColumn()
    id: number

    @Column()
    name: string

    @OneToMany((type) => Photo, (photo) => photo.user)
    photos: Photo[]
}

import { Entity, PrimaryGeneratedColumn, Column, ManyToOne } from "typeorm"
import { User } from "./User"

@Entity()
export class Photo {
    @PrimaryGeneratedColumn()
    id: number

    @Column()
    url: string

    @ManyToOne((type) => User, (user) => user.photos)
    user: User
}

Загрузка пользователя "Timber" со всеми его фотографиями:

const user = await dataSource
    .createQueryBuilder("user")
    .leftJoinAndSelect("user.photos", "photo")
    .where("user.name = :name", { name: "Timber" })
    .getOne()

Вы получите следующий результат:

{
    id: 1,
    name: "Timber",
    photos: [{
        id: 1,
        url: "me-with-chakram.jpg"
    }, {
        id: 2,
        url: "me-with-trees.jpg"
    }]
}

Как видите, leftJoinAndSelect автоматически загрузил все фотографии Тимбера. Первый аргумент — это отношение, которое вы хотите загрузить, а второй — псевдоним, назначаемый таблице этого отношения. Этот псевдоним можно использовать в любом месте построителя запросов. Например, возьмём все фотографии Тимбера, которые не были удалены.

const user = await dataSource
    .createQueryBuilder("user")
    .leftJoinAndSelect("user.photos", "photo")
    .where("user.name = :name", { name: "Timber" })
    .andWhere("photo.isRemoved = :isRemoved", { isRemoved: false })
    .getOne()

Это сгенерирует следующий SQL-запрос:

SELECT user.*, photo.* FROM users user
    LEFT JOIN photos photo ON photo.user = user.id
    WHERE user.name = 'Timber' AND photo.isRemoved = FALSE

Также можно добавить условия непосредственно в выражение JOIN вместо использования "WHERE":

const user = await dataSource
    .createQueryBuilder("user")
    .leftJoinAndSelect("user.photos", "photo", "photo.isRemoved = :isRemoved", {
        isRemoved: false,
    })
    .where("user.name = :name", { name: "Timber" })
    .getOne()

Это сгенерирует следующий SQL-запрос:

SELECT user.*, photo.* FROM users user
    LEFT JOIN photos photo ON photo.user = user.id AND photo.isRemoved = FALSE
    WHERE user.name = 'Timber'

Внутренние и левые соединения

Чтобы использовать INNER JOIN вместо LEFT JOIN, просто примените innerJoinAndSelect:

const user = await dataSource
    .createQueryBuilder("user")
    .innerJoinAndSelect(
        "user.photos",
        "photo",
        "photo.isRemoved = :isRemoved",
        { isRemoved: false },
    )
    .where("user.name = :name", { name: "Timber" })
    .getOne()

Это сгенерирует:

SELECT user.*, photo.* FROM users user
    INNER JOIN photos photo ON photo.user = user.id AND photo.isRemoved = FALSE
    WHERE user.name = 'Timber'

Разница между LEFT JOIN и INNER JOIN в том, что INNER JOIN не вернёт пользователя, если у него нет фотографий. LEFT JOIN вернёт пользователя даже при отсутствии фотографий. Подробнее о разных типах соединений см. в документации по SQL.

Соединение без выборки данных

Можно объединять данные без их выборки. Для этого используйте leftJoin или innerJoin:

const user = await dataSource
    .createQueryBuilder("user")
    .innerJoin("user.photos", "photo")
    .where("user.name = :name", { name: "Timber" })
    .getOne()

Это сгенерирует:

SELECT user.* FROM users user
    INNER JOIN photos photo ON photo.user = user.id
    WHERE user.name = 'Timber'

Такой запрос выберет Тимбера, если у него есть фотографии, но не вернёт сами фотографии.

Соединение с любыми сущностями или таблицами

Можно объединять не только отношения, но и другие несвязанные сущности или таблицы. Примеры:

const user = await dataSource
    .createQueryBuilder("user")
    .leftJoinAndSelect(Photo, "photo", "photo.userId = user.id")
    .getMany()

const user = await dataSource
    .createQueryBuilder("user")
    .leftJoinAndSelect("photos", "photo", "photo.userId = user.id")
    .getMany()

Функциональность соединения и отображения

Добавьте свойство profilePhoto в сущность User, и вы сможете сопоставлять данные с этим свойством через QueryBuilder:

export class User {
    /// ...
    profilePhoto: Photo
}

const user = await dataSource
    .createQueryBuilder("user")
    .leftJoinAndMapOne(
        "user.profilePhoto",
        "user.photos",
        "photo",
        "photo.isForProfile = TRUE",
    )
    .where("user.name = :name", { name: "Timber" })
    .getOne()

Это загрузит фотографию профиля Тимбера и установит её в user.profilePhoto. Для загрузки и сопоставления одиночной сущности используйте leftJoinAndMapOne. Для нескольких сущностей — leftJoinAndMapMany.

Получение сгенерированного запроса

Иногда может потребоваться получить SQL-запрос, сгенерированный QueryBuilder. Для этого используйте getSql:

const sql = dataSource
    .createQueryBuilder("user")
    .where("user.firstName = :firstName", { firstName: "Timber" })
    .orWhere("user.lastName = :lastName", { lastName: "Saw" })
    .getSql()

Для целей отладки вы можете проверить сгенерированный SQL с помощью getSql() или getQuery():

const qb = dataSource
    .createQueryBuilder("user")
    .where("user.firstName = :firstName", { firstName: "Timber" })
    .orWhere("user.lastName = :lastName", { lastName: "Saw" })

console.log(qb.getSql())
// SELECT ... WHERE "user"."firstName" = $1 OR "user"."lastName" = $2

const users = await qb.getMany()

getSql() возвращает SQL с плейсхолдерами параметров. getQuery() возвращает ту же SQL-строку. Чтобы увидеть значения параметров, используйте getQueryAndParameters(), который возвращает кортеж [sql, parameters].

Все выполненные запросы также автоматически логируются через настроенный логгер при включении логирования запросов в опциях DataSource:

new DataSource({
    // ...
    logging: ["query"],
})

Получение сырых результатов

Существует два типа результатов при использовании построителя запросов: сущности и сырые данные. В большинстве случаев нужно выбирать реальные сущности (например, пользователей) через getOne и getMany. Но иногда требуются специфичные данные, например сумма фотографий всех пользователей. Такие данные не являются сущностью — это сырые данные. Для их получения используйте getRawOne и getRawMany. Примеры:

const { sum } = await dataSource
    .getRepository(User)
    .createQueryBuilder("user")
    .select("SUM(user.photosCount)", "sum")
    .where("user.id = :id", { id: 1 })
    .getRawOne()

const photosSums = await dataSource
    .getRepository(User)
    .createQueryBuilder("user")
    .select("user.id")
    .addSelect("SUM(user.photosCount)", "sum")
    .groupBy("user.id")
    .getRawMany()

// result will be like this: [{ id: 1, sum: 25 }, { id: 2, sum: 13 }, ...]

Потоковая передача результатов

Можно использовать stream, который возвращает поток данных. Потоковая передача возвращает сырые данные, и преобразование в сущности нужно выполнять вручную:

const stream = await dataSource
    .getRepository(User)
    .createQueryBuilder("user")
    .where("user.id = :id", { id: 1 })
    .stream()

Использование пагинации

В большинстве приложений требуется функциональность пагинации — например, для постраничной навигации, слайдеров или бесконечной прокрутки.

const users = await dataSource
    .getRepository(User)
    .createQueryBuilder("user")
    .leftJoinAndSelect("user.photos", "photo")
    .take(10)
    .getMany()

Это вернёт первых 10 пользователей с их фотографиями.

const users = await dataSource
    .getRepository(User)
    .createQueryBuilder("user")
    .leftJoinAndSelect("user.photos", "photo")
    .skip(10)
    .getMany()

Это вернёт всех пользователей, кроме первых 10, вместе с их фотографиями. Методы можно комбинировать:

const users = await dataSource
    .getRepository(User)
    .createQueryBuilder("user")
    .leftJoinAndSelect("user.photos", "photo")
    .skip(5)
    .take(10)
    .getMany()

Это пропустит первых 5 пользователей и возьмёт следующие 10.

Может показаться, что take и skip аналогичны limit и offset, но это не так. limit и offset могут работать некорректно в сложных запросах с соединениями или подзапросами. take и skip предотвращают эти проблемы.

Установка блокировок

Построитель запросов поддерживает оптимистичные и пессимистичные блокировки.

Режимы блокировок

Поддерживаемые режимы блокировок и соответствующие SQL-инструкции перечислены в таблице ниже (пустые ячейки означают отсутствие поддержки). Если указанный режим блокировки не поддерживается, будет выброшена ошибка LockNotSupportedOnGivenDriverError.

	pessimistic_read	pessimistic_write	dirty_read	for_no_key_update	for_key_share
MySQL, MariaDB	`FOR SHARE` (MySQL 8+), `LOCK IN SHARE MODE`	`FOR UPDATE`	(nothing)
Oracle	`FOR UPDATE`	`FOR UPDATE`	(nothing)
Postgres, CockroachDB	`FOR SHARE`	`FOR UPDATE`	(nothing)	`FOR NO KEY UPDATE`	`FOR KEY SHARE`
SAP HANA	`FOR SHARE LOCK`	`FOR UPDATE`	(nothing)
SQL Server	`WITH (HOLDLOCK, ROWLOCK)`	`WITH (UPDLOCK, ROWLOCK)`	`WITH (NOLOCK)`

Для пессимистической блокировки на чтение используйте:

const users = await dataSource
    .getRepository(User)
    .createQueryBuilder("user")
    .setLock("pessimistic_read")
    .getMany()

Для пессимистической блокировки на запись используйте:

const users = await dataSource
    .getRepository(User)
    .createQueryBuilder("user")
    .setLock("pessimistic_write")
    .getMany()

Для чтения "грязных" данных (dirty read) используйте:

const users = await dataSource
    .getRepository(User)
    .createQueryBuilder("user")
    .setLock("dirty_read")
    .getMany()

Для оптимистической блокировки используйте:

const users = await dataSource
    .getRepository(User)
    .createQueryBuilder("user")
    .setLock("optimistic", existUser.version)
    .getMany()

Оптимистическая блокировка работает совместно с декораторами @Version и @UpdatedDate.

Блокировка таблиц

Таблицы можно блокировать с помощью:

const users = await dataSource
    .getRepository(Post)
    .createQueryBuilder("post")
    .leftJoin("post.author", "user")
    .setLock("pessimistic_write", undefined, ["post"])
    .getMany()

При указании аргумента Lock Tables явно указывается только таблица, заблокированная в предложении FOR UPDATE OF. Поддержка Lock Tables доступна только в PostgreSQL / CockroachDB.

setOnLocked

Позволяет управлять поведением при блокировке строки. По умолчанию СУБД ожидает снятия блокировки. Это поведение можно изменить через setOnLocked:

Не ожидать:

const users = await dataSource
    .getRepository(User)
    .createQueryBuilder("user")
    .setLock("pessimistic_write")
    .setOnLocked("nowait")
    .getMany()

Пропускать строку:

const users = await dataSource
    .getRepository(User)
    .createQueryBuilder("user")
    .setLock("pessimistic_write")
    .setOnLocked("skip_locked")
    .getMany()

Поддержка setOnLocked в различных СУБД для режимов блокировки:

MySQL < 8, MariaDB: pessimistic_write
MySQL 8+: pessimistic_read, pessimistic_write
Oracle: pessimistic_write
Postgres, Cockroach: pessimistic_read, pessimistic_write, for_no_key_update, for_key_share
SAP HANA: pessimistic_read, pessimistic_write

Использование кастомных индексов

В некоторых случаях можно указать определённый индекс для использования СУБД. Поддерживается только в MySQL.

const users = await dataSource
    .getRepository(User)
    .createQueryBuilder("user")
    .useIndex("my_index") // name of index
    .getMany()

Максимальное время выполнения

Медленные запросы можно прерывать, чтобы избежать перегрузки сервера.

const users = await dataSource
    .getRepository(User)
    .createQueryBuilder("user")
    .maxExecutionTime(1000) // milliseconds.
    .getMany()

Выборка отдельных полей

Для выборки определённых свойств сущности используйте синтаксис:

const users = await dataSource
    .getRepository(User)
    .createQueryBuilder("user")
    .select(["user.id", "user.name"])
    .getMany()

Это выберет только id и name из User.

Использование подзапросов

Подзапросы легко создаются. Они поддерживаются в выражениях FROM, WHERE и JOIN. Пример:

const qb = await dataSource.getRepository(Post).createQueryBuilder("post")

const posts = qb
    .where(
        "post.title IN " +
            qb
                .subQuery()
                .select("user.name")
                .from(User, "user")
                .where("user.registered = :registered")
                .getQuery(),
    )
    .setParameter("registered", true)
    .getMany()

Более элегантная реализация:

const posts = await dataSource
    .getRepository(Post)
    .createQueryBuilder("post")
    .where((qb) => {
        const subQuery = qb
            .subQuery()
            .select("user.name")
            .from(User, "user")
            .where("user.registered = :registered")
            .getQuery()
        return "post.title IN " + subQuery
    })
    .setParameter("registered", true)
    .getMany()

Альтернативно: создать отдельный QueryBuilder и использовать его SQL:

const userQb = await dataSource
    .getRepository(User)
    .createQueryBuilder("user")
    .select("user.name")
    .where("user.registered = :registered", { registered: true })

const posts = await dataSource
    .getRepository(Post)
    .createQueryBuilder("post")
    .where("post.title IN (" + userQb.getQuery() + ")")
    .setParameters(userQb.getParameters())
    .getMany()

Подзапросы в FROM:

const userQb = await dataSource
    .getRepository(User)
    .createQueryBuilder("user")
    .select("user.name", "name")
    .where("user.registered = :registered", { registered: true })

const posts = await dataSource
    .createQueryBuilder()
    .select("user.name", "name")
    .from("(" + userQb.getQuery() + ")", "user")
    .setParameters(userQb.getParameters())
    .getRawMany()

Или более кратко:

const posts = await dataSource
    .createQueryBuilder()
    .select("user.name", "name")
    .from((subQuery) => {
        return subQuery
            .select("user.name", "name")
            .from(User, "user")
            .where("user.registered = :registered", { registered: true })
    }, "user")
    .getRawMany()

Для добавления подзапроса как "второго FROM" используйте addFrom.

Подзапросы в SELECT:

const posts = await dataSource
    .createQueryBuilder()
    .select("post.id", "id")
    .addSelect((subQuery) => {
        return subQuery.select("user.name", "name").from(User, "user").limit(1)
    }, "name")
    .from(Post, "post")
    .getRawMany()

Скрытые колонки

Если в модели есть колонка с параметром select: false, для её выборки используйте addSelect.

Пример сущности:

import { Entity, PrimaryGeneratedColumn, Column } from "typeorm"

@Entity()
export class User {
    @PrimaryGeneratedColumn()
    id: number

    @Column()
    name: string

    @Column({ select: false })
    password: string
}

Стандартные find или запросы не включат свойство password. Но при использовании:

const users = await dataSource
    .getRepository(User)
    .createQueryBuilder()
    .select("user.id", "id")
    .addSelect("user.password")
    .getMany()

Свойство password будет включено в результат.

Запрос удалённых строк

Если в модели есть колонка с декоратором @DeleteDateColumn, QueryBuilder автоматически запрашивает строки с "мягким удалением" (soft deleted).

Пример сущности:

import {
    Entity,
    PrimaryGeneratedColumn,
    Column,
    DeleteDateColumn,
} from "typeorm"

@Entity()
export class User {
    @PrimaryGeneratedColumn()
    id: number

    @Column()
    name: string

    @DeleteDateColumn()
    deletedAt?: Date
}

При использовании стандартного find или запроса вы не получите строки, содержащие значение в этой колонке. Однако если выполнить следующее:

const users = await dataSource
    .getRepository(User)
    .createQueryBuilder()
    .select("user.id", "id")
    .withDeleted()
    .getMany()

Вы получите все строки, включая удалённые.

Обобщённые табличные выражения

Экземпляры QueryBuilder поддерживают обобщённые табличные выражения, если минимальная поддерживаемая версия вашей СУБД включает эту функциональность. Для Oracle обобщённые табличные выражения пока не поддерживаются.

const users = await dataSource
    .getRepository(User)
    .createQueryBuilder("user")
    .select("user.id", "id")
    .addCommonTableExpression(
        `
      SELECT "userId" FROM "post"
    `,
        "post_users_ids",
    )
    .where(`user.id IN (SELECT "userId" FROM 'post_users_ids')`)
    .getMany()

Результирующие значения InsertQueryBuilder или UpdateQueryBuilder можно использовать в Postgres:

const insertQueryBuilder = dataSource
    .getRepository(User)
    .createQueryBuilder()
    .insert({
        name: "John Smith",
    })
    .returning(["id"])

const users = await dataSource
    .getRepository(User)
    .createQueryBuilder("user")
    .addCommonTableExpression(insertQueryBuilder, "insert_results")
    .where(`user.id IN (SELECT "id" FROM 'insert_results')`)
    .getMany()

Запросы путешествия во времени

Запросы путешествия во времени в настоящее время поддерживаются только в СУБД CockroachDB.

const repository = dataSource.getRepository(Account)

// create a new account
const account = new Account()
account.name = "John Smith"
account.balance = 100
await repository.save(account)

// imagine we update the account balance 1 hour after creation
account.balance = 200
await repository.save(account)

// outputs { name: "John Smith", balance: "200" }
console.log(account)

// load account state on 1 hour back
account = await repository
    .createQueryBuilder("account")
    .timeTravelQuery(`'-1h'`)
    .getOneOrFail()

// outputs { name: "John Smith", balance: "100" }
console.log(account)

По умолчанию timeTravelQuery() использует функцию follower_read_timestamp() при отсутствии аргументов. Для других поддерживаемых параметров временных меток и дополнительной информации обратитесь к документации CockroachDB.

Отладка

Сгенерированный SQL можно получить из построителя запросов через вызов getQuery() или getQueryAndParameters().

Если нужен только запрос, используйте getQuery()

const sql = await dataSource
    .getRepository(User)
    .createQueryBuilder("user")
    .where("user.id = :id", { id: 1 })
    .getQuery()

Результат:

SELECT `user`.`id` as `userId`, `user`.`firstName` as `userFirstName`, `user`.`lastName` as `userLastName` FROM `users` `user` WHERE `user`.`id` = ?

Если нужны и запрос, и параметры, получите массив через getQueryAndParameters()

const queryAndParams = await dataSource
    .getRepository(User)
    .createQueryBuilder("user")
    .where("user.id = :id", { id: 1 })
    .getQueryAndParameters()

Результат:

;[
    "SELECT `user`.`id` as `userId`, `user`.`firstName` as `userFirstName`, `user`.`lastName` as `userLastName` FROM `users` `user` WHERE `user`.`id` = ?",
    [1],
]

Что такое QueryBuilder?​

Важное замечание при использовании QueryBuilder​

Как создать и использовать QueryBuilder?​

Получение значений с помощью QueryBuilder​

Получение количества записей​

Для чего нужны псевдонимы (aliases)?​

Использование параметров для экранирования данных​

Добавление условий WHERE​

Добавление выражения HAVING​

Добавление выражения ORDER BY​

Добавление выражения ORDER BY​

Добавление выражения DISTINCT ON (только для Postgres)​

Добавление выражения GROUP BY​

Добавление выражения GROUP BY​

Добавление выражения LIMIT​

Добавление выражения LIMIT​

Добавление выражения OFFSET​

Объединение связей (Joining relations)​

Внутренние и левые соединения​

Соединение без выборки данных​

Соединение с любыми сущностями или таблицами​

Функциональность соединения и отображения​

Получение сгенерированного запроса​

Получение сырых результатов​

Потоковая передача результатов​

Использование пагинации​

Установка блокировок​

Режимы блокировок​

Блокировка таблиц​

setOnLocked​

Использование кастомных индексов​

Максимальное время выполнения​

Выборка отдельных полей​

Использование подзапросов​

Скрытые колонки​

Запрос удалённых строк​

Обобщённые табличные выражения​

Запросы путешествия во времени​

Запросы путешествия во времени​

Отладка​

Что такое QueryBuilder?

Важное замечание при использовании `QueryBuilder`

Как создать и использовать QueryBuilder?

Получение значений с помощью `QueryBuilder`

Получение количества записей

Для чего нужны псевдонимы (aliases)?

Использование параметров для экранирования данных

Добавление условий `WHERE`

Добавление выражения `HAVING`

Добавление выражения `ORDER BY`

Добавление выражения `ORDER BY`

Добавление выражения `DISTINCT ON` (только для Postgres)

Добавление выражения `GROUP BY`

Добавление выражения `GROUP BY`

Добавление выражения `LIMIT`

Добавление выражения `LIMIT`

Добавление выражения `OFFSET`

Объединение связей (Joining relations)

Внутренние и левые соединения

Соединение без выборки данных

Соединение с любыми сущностями или таблицами

Функциональность соединения и отображения

Получение сгенерированного запроса

Получение сырых результатов

Потоковая передача результатов

Использование пагинации

Установка блокировок

Режимы блокировок

Блокировка таблиц

setOnLocked

Использование кастомных индексов

Максимальное время выполнения

Выборка отдельных полей

Использование подзапросов

Скрытые колонки

Запрос удалённых строк

Обобщённые табличные выражения

Запросы путешествия во времени

Запросы путешествия во времени

Отладка