Selezione utilizzando Query Builder

Traduzione Beta Non Ufficiale

Questa pagina è stata tradotta da PageTurner AI (beta). Non ufficialmente approvata dal progetto. Hai trovato un errore? Segnala problema →

Cos'è un QueryBuilder?

QueryBuilder è una delle funzionalità più potenti di TypeORM: ti permette di costruire query SQL utilizzando una sintassi elegante e intuitiva, eseguirle e ottenere entità trasformate automaticamente.

Esempio semplice di QueryBuilder:

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

Costruisce la seguente query SQL:

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

e restituisce un'istanza di User:

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

Nota importante sull'utilizzo di `QueryBuilder`

Quando usi QueryBuilder, devi fornire parametri univoci nelle espressioni WHERE. Questo non funzionerà:

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 })

... ma questo sì:

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 })

Nota che abbiamo nominato in modo univoco :sheepId e :cowId invece di usare :id due volte per parametri diversi.

Come creare e utilizzare un QueryBuilder?

Esistono diversi modi per creare un Query Builder:

Utilizzo di DataSource:

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

Utilizzo dell'entity manager:

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

Utilizzo del repository:

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

Sono disponibili 5 tipi diversi di QueryBuilder:

SelectQueryBuilder - utilizzato per costruire ed eseguire query SELECT. Esempio:

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

InsertQueryBuilder - utilizzato per costruire ed eseguire query INSERT. Esempio:

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

UpdateQueryBuilder - utilizzato per costruire ed eseguire query UPDATE. Esempio:

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

DeleteQueryBuilder - utilizzato per costruire ed eseguire query DELETE. Esempio:

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

RelationQueryBuilder - utilizzato per operazioni specifiche sulle relazioni [TBD]. Esempio:

await dataSource
    .createQueryBuilder()
    .relation(User, "photos")
    .of(id)
    .loadMany()

Puoi passare da un tipo di query builder a un altro all'interno di uno di essi: quando lo fai, otterrai una nuova istanza del query builder (a differenza di tutti gli altri metodi).

Ottenere valori con `QueryBuilder`

Per ottenere un singolo risultato dal database, ad esempio per recuperare un utente tramite id o nome, devi usare getOne:

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

getOneOrFail ottiene un singolo risultato dal database, ma se nessun risultato esiste solleverà un EntityNotFoundError:

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

Per ottenere più risultati dal database, ad esempio per ottenere tutti gli utenti dal database, utilizza getMany:

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

Esistono due tipi di risultati ottenibili con il query builder di selezione: entità o risultati grezzi. Spesso è necessario selezionare entità reali dal database, come utenti. A questo scopo si utilizzano getOne e getMany. Tuttavia, talvolta servono dati specifici, ad esempio la somma di tutte le foto degli utenti. Questi dati non sono entità, ma dati grezzi. Per ottenere dati grezzi, si usano getRawOne e getRawMany. Esempi:

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 }, ...]

Ottenere un conteggio

È possibile ottenere il conteggio delle righe restituite da una query con getCount(). Questo restituirà il conteggio come numero anziché come risultato di entità.

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

Che produce la seguente query SQL:

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

A cosa servono gli alias?

Abbiamo usato createQueryBuilder("user"). Ma cos'è "user"? È semplicemente un alias SQL standard. Usiamo alias ovunque, tranne quando lavoriamo con dati selezionati.

createQueryBuilder("user") equivale a:

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

Che genererà la seguente query SQL:

SELECT ... FROM users user

In questa query SQL, users è il nome della tabella, mentre user è l'alias assegnato. Successivamente usiamo questo alias per accedere alla tabella:

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

Che produce la seguente query SQL:

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

Come vedi, abbiamo usato la tabella users tramite l'alias user assegnato alla creazione del query builder.

Un query builder non è limitato a un singolo alias, può averne multipli. Ogni selezione può avere il proprio alias, puoi selezionare da più tabelle ognuna con il proprio alias, e unire più tabelle ognuna con alias dedicato. Puoi usare questi alias per accedere alle tabelle (o ai dati) selezionati.

Uso dei parametri per proteggere i dati

Abbiamo usato where("user.name = :name", { name: "Timber" }). Cosa rappresenta { name: "Timber" }? È un parametro per prevenire SQL injection. Avremmo potuto scrivere: where("user.name = '" + name + "'), ma sarebbe insicuro perché esposto a SQL injection. Il metodo sicuro è usare questa sintassi speciale: where("user.name = :name", { name: "Timber" }), dove :name è il nome del parametro e il valore è specificato nell'oggetto: { name: "Timber" }.

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

è una scorciatoia per:

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

Nota: non usare lo stesso nome parametro per valori diversi nel query builder. I valori verrebbero sovrascritti se impostati più volte.

Puoi fornire un array di valori trasformandoli in una lista SQL con la sintassi di espansione speciale:

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

Che diventa:

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

Aggiungere un'espressione `WHERE`

Aggiungere un'espressione WHERE è semplice come:

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

Che produrrà:

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

Puoi aggiungere AND a un'espressione WHERE esistente:

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

Che produrrà la seguente query SQL:

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

Puoi aggiungere OR a un'espressione WHERE esistente:

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

Che produrrà la seguente query SQL:

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

Puoi eseguire una query IN con l'espressione WHERE:

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

Che produrrà la seguente query SQL:

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

Puoi aggiungere un'espressione WHERE complessa a un WHERE esistente usando 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" })
        }),
    )

Che produrrà la seguente query SQL:

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

Puoi aggiungere un'espressione WHERE complessa negata a un WHERE esistente usando 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" })
        }),
    )

Che produrrà la seguente query SQL:

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

Puoi combinare tutte le espressioni AND e OR di cui hai bisogno. Se usi .where più di una volta, sovrascriverai tutte le precedenti espressioni WHERE.

Nota: fai attenzione con orWhere - se usi espressioni complesse con sia AND che OR, tieni presente che vengono combinate senza precedenze. A volte sarà necessario creare una stringa where invece di usare orWhere.

Aggiungere un'espressione `HAVING`

Aggiungere un'espressione HAVING è semplice come:

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

Che produrrà la seguente query SQL:

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

Puoi aggiungere AND a un'espressione HAVING esistente:

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

Che produrrà la seguente query SQL:

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

Puoi aggiungere OR a un'espressione HAVING esistente:

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

Che produrrà la seguente query SQL:

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

Puoi combinare tutte le espressioni AND e OR di cui hai bisogno. Se usi .having più di una volta, sovrascriverai tutte le precedenti espressioni HAVING.

Aggiungere un'espressione `ORDER BY`

Aggiungere un'espressione ORDER BY è semplice come:

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

Che produrrà:

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

Puoi cambiare l'ordinamento da ascendente a discendente (o viceversa):

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

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

Puoi aggiungere più criteri di ordinamento:

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

Puoi anche usare una mappa di campi per l'ordinamento:

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

Se usi .orderBy più di una volta, sovrascriverai tutte le precedenti espressioni ORDER BY.

Aggiungere un'espressione `DISTINCT ON` (solo Postgres)

Quando usi DISTINCT-ON con un'espressione ORDER-BY, l'espressione DISTINCT-ON deve corrispondere alla ORDER-BY più a sinistra. Le espressioni DISTINCT-ON sono interpretate con le stesse regole di ORDER-BY. Nota che usare DISTINCT-ON senza ORDER-BY significa che la prima riga di ogni gruppo è imprevedibile.

Aggiungere un'espressione DISTINCT ON è semplice come:

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

Che produrrà:

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

Aggiungere un'espressione `GROUP BY`

Aggiungere un'espressione GROUP BY è semplice come:

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

Che produrrà la seguente query SQL:

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

Per aggiungere più criteri di raggruppamento usa addGroupBy:

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

Se usi .groupBy più di una volta, sovrascriverai tutte le precedenti espressioni GROUP BY.

Aggiungere un'espressione `LIMIT`

Aggiungere un'espressione LIMIT è semplice come:

createQueryBuilder("user").limit(10)

Che produrrà la seguente query SQL:

SELECT ... FROM users user LIMIT 10

La query SQL risultante dipende dal tipo di database (SQL, MySQL, Postgres, ecc.). Nota: LIMIT potrebbe non funzionare come previsto in query complesse con join o subquery. Se stai implementando l'impaginazione, è consigliato usare take invece.

Aggiungere un'espressione `OFFSET`

Aggiungere un'espressione SQL OFFSET è semplice come:

createQueryBuilder("user").offset(10)

Che produrrà la seguente query SQL:

SELECT ... FROM users user OFFSET 10

La query SQL risultante dipende dal tipo di database (SQL, MySQL, Postgres, ecc.). Nota: OFFSET potrebbe non funzionare come previsto in query complesse con join o subquery. Se stai implementando l'impaginazione, è consigliato usare skip invece.

Effettuare join di relazioni

Supponiamo di avere le seguenti entità:

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
}

Ora supponiamo di voler caricare l'utente "Timber" con tutte le sue foto:

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

Otterrai il seguente risultato:

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

Come puoi vedere, leftJoinAndSelect ha caricato automaticamente tutte le foto di Timber. Il primo argomento è la relazione che desideri caricare e il secondo è un alias assegnato alla tabella di questa relazione. Puoi usare questo alias ovunque nel query builder. Ad esempio, prendiamo tutte le foto di Timber che non sono state rimosse.

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

Questo genererà la seguente query SQL:

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

Puoi anche aggiungere condizioni all'espressione di join invece di usare "where":

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

Puoi anche aggiungere condizioni direttamente nell'espressione di join invece di usare "where":

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 e left join

Se vuoi usare INNER JOIN invece di LEFT JOIN, usa semplicemente innerJoinAndSelect:

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

Questo genererà:

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

La differenza tra LEFT JOIN e INNER JOIN è che INNER JOIN non restituirà un utente se non ha foto. LEFT JOIN invece restituirà l'utente anche senza foto. Per approfondire i diversi tipi di join, consulta la documentazione SQL.

Join senza selezione

Puoi unire dati senza selezionarli. Usa leftJoin o innerJoin:

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

Questo genererà:

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

Questo selezionerà Timber se ha delle foto, ma non restituirà le sue foto.

Questo selezionerà Timber se ha foto, ma non restituirà le foto stesse.

Unire qualsiasi entità o tabella

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

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

Funzionalità di join e mapping

Aggiungi profilePhoto all'entità User, e potrai mappare qualsiasi dato in quella proprietà utilizzando QueryBuilder:

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

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

Questo caricherà la foto profilo di Timber impostandola in user.profilePhoto. Per mappare una singola entità usa leftJoinAndMapOne. Per mappare più entità usa leftJoinAndMapMany.

Ottenere la query generata

Per ottenere la query SQL generata da QueryBuilder, usa getSql:

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

Per debug puoi usare printSql:

const users = await createQueryBuilder("user")
    .where("user.firstName = :firstName", { firstName: "Timber" })
    .orWhere("user.lastName = :lastName", { lastName: "Saw" })
    .printSql()
    .getMany()

Questa query restituirà gli utenti e stamperà l'istruzione SQL nella console.

Ottenere risultati grezzi

Con il query builder di selezione puoi ottenere due tipi di risultati: entità e risultati grezzi. Solitamente si selezionano entità complete (es. utenti) con getOne/getMany. Ma per dati specifici come la somma delle foto utente (dati non strutturati come entità), usa getRawOne e getRawMany. Esempi:

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 }, ...]

Streaming dei dati risultanti

Puoi usare stream che ti restituisce uno stream. Lo streaming restituisce dati grezzi e devi gestire manualmente la trasformazione dell'entità:

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

Utilizzo della paginazione

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

La paginazione è essenziale per componenti come pager, slider di pagine o scroll infinito nelle applicazioni.

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

Questo ti darà tutti gli utenti tranne i primi 10 con le loro foto. Puoi combinare questi metodi:

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

Questo salterà i primi 5 utenti e prenderà i successivi 10 utenti.

take e skip potrebbero sembrare equivalenti a limit e offset, ma non lo sono. limit e offset potrebbero non funzionare come previsto in query complesse con join o subquery. take e skip evitano tali problemi.

Impostare il locking

QueryBuilder supporta sia il locking ottimistico che quello pessimistico.

Modalità di Lock

Il supporto per le modalità di lock e le corrispondenti istruzioni SQL è riportato nella tabella seguente (le celle vuote indicano mancato supporto). Se viene specificata una modalità non supportata, verrà sollevato un errore LockNotSupportedOnGivenDriverError.

|                 | pessimistic_read                  | pessimistic_write       | dirty_read    | pessimistic_partial_write (Deprecated, use onLocked instead)   | pessimistic_write_or_fail (Deprecated, use onLocked instead)   | for_no_key_update   | for_key_share |
| --------------- | --------------------------------- | ----------------------- | ------------- | -------------------------------------------------------------- | -------------------------------------------------------------- | ------------------- | ------------- |
| MySQL           | FOR SHARE (8+)/LOCK IN SHARE MODE | FOR UPDATE              | (nothing)     | FOR UPDATE SKIP LOCKED                                         | FOR UPDATE NOWAIT                                              |                     |               |
| Postgres        | FOR SHARE                         | FOR UPDATE              | (nothing)     | FOR UPDATE SKIP LOCKED                                         | FOR UPDATE NOWAIT                                              | FOR NO KEY UPDATE   | FOR KEY SHARE |
| Oracle          | FOR UPDATE                        | FOR UPDATE              | (nothing)     |                                                                |                                                                |                     |               |
| SQL Server      | WITH (HOLDLOCK, ROWLOCK)          | WITH (UPDLOCK, ROWLOCK) | WITH (NOLOCK) |                                                                |                                                                |                     |               |
| AuroraDataApi   | LOCK IN SHARE MODE                | FOR UPDATE              | (nothing)     |                                                                |                                                                |                     |               |
| CockroachDB     |                                   | FOR UPDATE              | (nothing)     |                                                                | FOR UPDATE NOWAIT                                              | FOR NO KEY UPDATE   |               |

Per utilizzare il pessimistic read locking:

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

Per utilizzare il pessimistic write locking:

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

Per utilizzare il dirty read locking:

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

Per utilizzare l'optimistic locking:

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

L'optimistic locking funziona in combinazione con i decoratori @Version e @UpdatedDate.

Bloccare le tabelle

Puoi anche bloccare le tabelle utilizzando:

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

Se viene fornito l'argomento Lock Tables, viene specificata solo la tabella bloccata nella clausola FOR UPDATE OF.

setOnLocked

Consente di controllare il comportamento quando una riga è bloccata. Per impostazione predefinita, il database attende lo sblocco. Puoi modificare questo comportamento con setOnLocked.

Per non attendere:

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

Per saltare la riga:

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

Supporto del database per setOnLocked basato sulle modalità di lock:

Postgres: pessimistic_read, pessimistic_write, for_no_key_update, for_key_share
MySQL 8+: pessimistic_read, pessimistic_write
MySQL < 8, MariaDB: pessimistic_write
CockroachDB: pessimistic_write (solo nowait)

Utilizzare un indice personalizzato

In alcuni casi è possibile specificare un indice particolare per il server di database. Questa funzionalità è supportata solo in MySQL.

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

Tempo massimo di esecuzione

Possiamo interrompere query lente per evitare il crash del server.

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

Selezione parziale

Per selezionare solo alcune proprietà dell'entità:

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

Verranno selezionati solo id e name di User.

Utilizzo di sottoquery

È possibile creare facilmente sottoquery, supportate nelle espressioni FROM, WHERE e JOIN. Esempio:

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()

Metodo più elegante per ottenere lo stesso risultato:

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()

In alternativa, puoi creare un QueryBuilder separato e utilizzare la SQL generata:

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()

Puoi creare sottoquery in FROM così:

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()

o con una sintassi più elegante:

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()

Per aggiungere una sottoquery come "secondo from" usa addFrom.

Puoi utilizzare sottoquery anche nelle istruzioni 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()

Colonne nascoste

Se il modello interrogato ha una colonna con select: false, devi usare addSelect per recuperarne i dati.

Supponiamo di avere questa entità:

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

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

    @Column()
    name: string

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

Con find o query standard non riceverai la proprietà password. Tuttavia, con:

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

Otterrai la proprietà password nella query.

Interrogare righe eliminate

Se il modello interrogato ha una colonna con attributo @DeleteDateColumn, il Query Builder selezionerà automaticamente le righe "soft deleted".

Supponiamo di avere questa entità:

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

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

    @Column()
    name: string

    @DeleteDateColumn()
    deletedAt?: Date
}

Utilizzando un find standard o una query, non riceverai le righe che hanno un valore in quella colonna. Tuttavia, se esegui quanto segue:

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

Otterrai tutte le righe, incluse quelle che sono state eliminate.

Espressioni di tabella comuni (CTE)

Le istanze di QueryBuilder supportano le espressioni di tabella comuni , se la versione minima supportata del tuo database le include. Le CTE non sono ancora supportate per Oracle.

const users = await connection
    .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()

I valori risultanti di InsertQueryBuilder o UpdateQueryBuilder possono essere utilizzati in Postgres:

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

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

Query di viaggio nel tempo attualmente supportate solo nel database CockroachDB.

const repository = connection.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)

Per impostazione predefinita timeTravelQuery() utilizza la funzione follower_read_timestamp() se non vengono passati argomenti. Per altri argomenti temporali supportati e informazioni aggiuntive, consultare la documentazione CockroachDB.

Debug

Puoi ottenere l'SQL generato dal query builder chiamando getQuery() o getQueryAndParameters().

Se vuoi solo la query, puoi usare getQuery()

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

Il che risulta in:

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

Oppure, se desideri la query e i parametri, puoi ottenere un array utilizzando getQueryAndParameters()

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

Il che risulta in:

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

Cos'è un QueryBuilder?​

Nota importante sull'utilizzo di QueryBuilder​

Come creare e utilizzare un QueryBuilder?​

Ottenere valori con QueryBuilder​

Ottenere un conteggio​

A cosa servono gli alias?​

Uso dei parametri per proteggere i dati​

Aggiungere un'espressione WHERE​

Aggiungere un'espressione HAVING​

Aggiungere un'espressione ORDER BY​

Aggiungere un'espressione DISTINCT ON (solo Postgres)​

Aggiungere un'espressione GROUP BY​

Aggiungere un'espressione LIMIT​

Aggiungere un'espressione OFFSET​

Effettuare join di relazioni​

Inner join e left join​

Join senza selezione​

Unire qualsiasi entità o tabella​

Funzionalità di join e mapping​

Ottenere la query generata​

Ottenere risultati grezzi​

Streaming dei dati risultanti​

Utilizzo della paginazione​

Utilizzo della paginazione​

Impostare il locking​

Modalità di Lock​

Bloccare le tabelle​

setOnLocked​

Utilizzare un indice personalizzato​

Tempo massimo di esecuzione​

Selezione parziale​

Utilizzo di sottoquery​

Colonne nascoste​

Interrogare righe eliminate​

Espressioni di tabella comuni (CTE)​

Debug​

Cos'è un QueryBuilder?

Nota importante sull'utilizzo di `QueryBuilder`

Come creare e utilizzare un QueryBuilder?

Ottenere valori con `QueryBuilder`

Ottenere un conteggio

A cosa servono gli alias?

Uso dei parametri per proteggere i dati

Aggiungere un'espressione `WHERE`

Aggiungere un'espressione `HAVING`

Aggiungere un'espressione `ORDER BY`

Aggiungere un'espressione `DISTINCT ON` (solo Postgres)

Aggiungere un'espressione `GROUP BY`

Aggiungere un'espressione `LIMIT`

Aggiungere un'espressione `OFFSET`

Effettuare join di relazioni

Inner join e left join

Join senza selezione

Unire qualsiasi entità o tabella

Funzionalità di join e mapping

Ottenere la query generata

Ottenere risultati grezzi

Streaming dei dati risultanti

Utilizzo della paginazione

Utilizzo della paginazione

Impostare il locking

Modalità di Lock

Bloccare le tabelle

setOnLocked

Utilizzare un indice personalizzato

Tempo massimo di esecuzione

Selezione parziale

Utilizzo di sottoquery

Colonne nascoste

Interrogare righe eliminate

Espressioni di tabella comuni (CTE)

Debug