QueryBuilder

QueryBuilderとは

typeormの機能の一つで複雑なSQLクエリを簡単に作成して実行するための機能です。

QueryBuilderの例

const firstUser = await connection
    .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"
}

クエリを作成する方法

クエリービルダーで作成する3つの方法。

connectionを使用

import {getConnection} from "typeorm";

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

EntityManagerを使用

import {getManager} from "typeorm";

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

Repositoryを使用

import {getRepository} from "typeorm";

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

5つのタイプ

SELECT,INSERT,UPDATE,DELETE,Relationを使用することができる。

1.SELECT

import {getConnection} from "typeorm";

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

2.INSERT

import {getConnection} from "typeorm";

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

3.UPDATE

import {getConnection} from "typeorm";

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

4.DELETE

import {getConnection} from "typeorm";

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

5.RelationQueryBuilder

関係固有の操作を構築して実行するために使用

値を取得する方法

データベースから一つ結果を取得するには「getOne」を使用する。

以下はIDまたは名前でユーザー

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

データベースから複数の結果を取得するには「getMany」を使用。

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

selectを使用して取得できる結果には、エンティティーとロウの2種類がある。 ほとんどの場合はデータベースから実際のエンティティを選択する必要がありのでgetOneとgetManyを使用する。 しかし特定のデータを選択する場合、例えばすべてのユーザー写真の合計はデータの実体ではなく、ロウデータを取得する。取得するには、getRawOneとgetRawManyを使用する。

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

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

エイリアスとは?

createQueryBuilder("user")の"user"がSQLエイリアスである。選択したデータを扱うとき以外はどこでもエイリアスを使用する。

createQueryBuilder("user")と下記は同等である。

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

結果は以下のSQLlクエリのようになる。

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テーブルを使用した。

1つのクエリービルダーは1つのエイリアスに限定されず、複数のエイリアスを持つことができる。各”select”はそれ自身の別名を持つことができ、それぞれそれ独自の別名を持つ複数のテーブルから選択することができる。それぞれ独自の別名を持つ複数のテーブルを結合することができ、それらの別名を使用して選択しているテーブルにアクセスできる。

データをエスケープするためのパラメータの使用

where("user.name =:name"、{name: "Timber"})の {name: "Timber"}はSQLインジェクションのためのパラメータである。

where("user.name = '" + name + "")と書くこともできるが、SQLインジェクションのコードは安全ではない。安全な方法はインジェクション対策された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", "Cristal", "Lina" ] })

これは次のようになる。

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

WHERE式の追加

WHERE式は以下の通り。

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

次のSQLクエリが生成される。

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

存在するWHERE式にANDを追加。

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'

既存のWHERE式にORを追加できる。

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'

大括弧を使用して、複雑なWHERE式を既存のWHEREに追加できる。

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

必要なだけAND式とOR式を組み合わせることができる。「.where」を複数回使用すると、それ以前のすべてのWHERE式がオーバーライドされる。

※orWhereには注意。AND式とOR式の両方を含む複雑な式を使用する場合は、それらが偽りなくスタックされることに注意する。 場合によっては、代わりにwhere文字列を作成しorWhereを使用しないようにする必要がある。

HAVING式の追加

HAVING式を追加は以下の通り。

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

次のSQLクエリが生成される。

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

既存のHAVING式にANDを追加も可能。

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'

既存のHAVING式にORを追加できる。

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式を追加する

ORDER BY式を追加するのは以下の通り。

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

次のSQLクエリが生成される。

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");

order-byフィールドのマップを使うこともできる。

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

.orderByを複数回使用すると、それまでのすべてのORDER BY式がオーバーライドされる。

GROUP BY式の追加

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式の追加

LIMIT式の追加は以下の通り。

createQueryBuilder("user")
    .limit(10)

次のSQLクエリが生成される。

SELECT ... FROM users user LIMIT 10

SQLクエリの結果はデータベースの種類(SQLmySQL、Postgresなど)によって異なる。

※結合または副照会で複雑な照会を使用している場合、LIMITは期待どおりに機能しない可能性がある。 ページネーションを使用している場合は、代わりにtakeを使用することをお勧めする。

OFFSET式の追加

OFFSET式の追加は以下の通り。

createQueryBuilder("user")
    .offset(10)

次のSQLクエリが生成される。

SELECT ... FROM users user OFFSET 10

SQLクエリの結果はデータベースの種類(SQLmySQL、Postgresなど)によって異なる。

※結合またはサブクエリを含む複雑なクエリを使用していると、期待したとおりにOFFSETが機能しないことがある。 ページネーションを使用している場合は、代わりにskipを使用することをお勧めする。

テーブル結合

次のエンティティがある場合。

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 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」が自動的に「Timber」の写真をすべてロードした。 最初の引数はロードしたいリレーション、2番目の引数はこのリレーションのテーブルに割り当てるエイリアスである。この別名は、クエリービルダーのどこでも使用できる。例えば、削除されていないすべてのTimberの写真をとる場合は以下の通り。

const user = await 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

「where」を使用し結合式に条件を追加することもできる。

const user = await 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

LEFT JOINの代わりにINNER JOINを使いたい場合はinnerJoinAndSelectを使用する。

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

これは以下のSQLと同等です。

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の場合はユーザーを返す。

選択せずに結合

選択せずにデータを結合するにはleftJoinまたはinnerJoinを使用する。

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

次のSQLクエリが生成される。

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

彼が写真を持っているならば「Timber」を選択しますが、写真は返さない。

いくつかのエンティティまたはテーブルを結合

関係のあるエンティティやテーブルだけでなく、他の関係のないエンティティまたはテーブルも結合することができる。

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

結合とマッピング機能

ユーザーエンティティにprofilePhotoを追加すると、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();

これでTimberのプロフィール写真が読み込まれ、それがuser.profilePhotoに設定される。 一つのエンティティをロードしてマップする場合は、leftJoinAndMapOneを使用する。複数のエンティティを読み込んでマップしたい場合は、leftJoinAndMapManyを使用する。

生成されたクエリを取得する

QueryBuilderで生成されたSQLクエリを取得したい場合には「getSql」を使用する。

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

デバッグ目的では「printSql」を使用する。

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

ストリーミング結果データ

ストリームを返す「stream」を使うことができる。 ストリーミングによって生データが返されるので、エンティティの変換を手動で処理する必要がある。

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

ページネーションの使用

ほとんどの場合、アプリケーションを開発するときにページ付け機能が必要である。 アプリケーションにページネーション、ページスライダ、または無限スクロールコンポーネントがある場合に使用する。

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

これにより、最初の10人のユーザーに写真が付く。

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

これにより、最初の10人のユーザー以外の写真が付く。それらの方法を組み合わせることができる。

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

これは最初の5人のユーザーをスキップし、それらの後に10人のユーザーを取得する。

takeとskipはlimitとoffsetを使っているように見えるかもしれないが、結合やサブクエリを使用したより複雑なクエリが発生した場合に、limitおよびoffsetは期待どおりに機能しない可能性がある。その場合にtake and skipを使うとこれらの問題を防ぐことができる。

セットロック

QueryBuilderは、楽観的ロックと悲観的ロックの両方をサポートしている。 悲観的読み取りロックを使用する場合は次の方法を使用する。

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

悲観的書き込みロックを使用するには、次の方法を使用する。

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

悲観的書き込みロックを使用するには、次の方法を使用する。楽観的ロックを使用するには、次の方法を使用する。

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

楽観的ロックは@Versionおよび@UpdatedDateデコレータの両方と連携して機能する。

部分選択

一部のエンティティプロパティのみを選択したい場合は、次の構文を使用できる。

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

これはユーザーのIDと名前のみを選択する。

サブクエリを使用する

サブクエリは以下のように使用する。 FROM、WHERE、およびJOIN式でサポートされている。

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

あるいは、別のクエリービルダーを作成してその生成されたSQLを使用することもできる。

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

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

FROMにサブクエリを作成できる。

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

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

またはより洗練された構文を使用する

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

サブクエリ選択を「2番目から」として追加する場合は、addFromを使用する。

SELECTステートメントでサブクエリ選択を使用することもできます。

const posts = await connection
    .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;
}

標準の検索またはクエリを使用しても、モデルのパスワードプロパティは表示されない。 ただし、次のようにしたとすると。

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

クエリでプロパティパスワードを取得できる。