はじめに
TypeORM のクエリビルダー（QueryBuilder）でサブクエリを使用する場合のサンプルコードの紹介です。
SQL 文で直接で書いた方が楽だったりしますが、クエリビルダーで書くべき場面も多いと思いますので、自分の備忘を兼ねて残しておきます。
動作確認のフレームワークは、NestJS を使っていますが、express の場合でも基本的に変わりません。
参考: Using subqueries
1. 一つのテーブルの場合
自己結合で、サブクエリを使用する場合です。
1-1. テーブルのイメージ
適当ですが、次のような item テーブルを対象とします。
| id | name | price |
|---|---|---|
| 1 | スマホ | 50000 |
| 2 | PC | 200000 |
| 3 | 車 | 300000 |
| 4 | 車 | 1000000 |
| 5 | スマホ | 80000 |
| 6 | PC | 50000 |
1-2. 指定したい SQL 文 （最大価格のレコードを取得する）
item テーブルに同じ名前の商品があり、そのうち一番価格の高い商品を抽出する例です。
SELECT * FROM item i1 WHERE i1.price = (
SELECT MAX(i2.price) FROM item i2 WHERE i2.name = i1.name
);
1-3. SQL を直書きする
getManager().query メソッドで直接SQLが書けます。
これで済むなら、これが一番楽だと思います。
const query = await getManager().query(
`SELECT * FROM item i1 WHERE i1.price = (
SELECT MAX(i2.price) FROM item i2 WHERE i2.name = i1.name
)`,
);
console.log(query);
1-4. クエリビルダーでサブクエリを記載する
1-4-1. Entity Manager で記述
次のように記載すれば、SQL の実行ができます。
const manager = getManager();
const query = await manager
.createQueryBuilder(Item, 'i1')
.where(
'i1.price = (' +
manager
.createQueryBuilder(Item, 'i2')
.select('Max(i2.price)')
.where('i2.name = i1.name')
.getQuery() +
')',
)
.getMany();
console.log(query);
① createQueryBuilder(Item, 'i1') の部分で、Item エンティティ（item テーブル）を i1 という別名で使用することを指定しています。
② getMany() で、複数レコードをエンティティのデータ型で取得することを指定しています（getRawMany() とすると、SQL で返されたデータがそのまま取得されます）。
③ サブクエリの部分は以下のとおりです。
サブクエリ部分の出力
サブクエリの部分は次のようになっています。
const sql = getManager()
.createQueryBuilder(Item, 'i2')
.select('Max(i2.price)')
.where('i2.name = i1.name')
.getQuery();
console.log(sql);
① createQueryBuilder(Item, 'i2') の部分で、Item エンティティ（item テーブル）を i2 という別名で使用することを指定しています。
② getQuery() で、生成された SQL 文が文字列として取得されます（出力結果は下記参照）。
サブクエリの部分をこの文字列に置き替えても、普通に動作します。
SELECT Max("i2"."price") FROM "item" "i2" WHERE "i2"."name" = i1.name
1-4-2. Repository で記述
次は、Repository からクエリビルダーを作成する例です。
const qb = getRepository(Item).createQueryBuilder('i1');
const query = await qb
.where(
'i1.price = ' +
qb.subQuery()
.select('Max(i2.price)')
.from(Item, 'i2')
.where('i2.name = i1.name')
.getQuery(),
)
.getMany();
console.log(query);
① getRepository(Item).createQueryBuilder('i1'); で、Item エンティティ（item テーブル）を i1 という別名で使用することを指定しています。
② qb.subQuery() でサブクエリを作成します（こちらの場合は、サブクエリを囲む丸括弧が自動で作成されます）。
③ from(Item, 'i2') で、サブクエリにおいて使用する Item エンティティ（item テーブル）を i2 という別名で指定しています。
なお、冒頭の const qb = getRepository(Item).createQueryBuilder('i1'); の部分は、リポジトリ内でコードを記載する場合は次のように簡略化して呼び出すことができます。
const qb = this.createQueryBuilder('i1');
2. テーブルが複数となる場合
テーブルが複数のクエリとなる場合の例も書いておきます。
2-1. テーブルのイメージ
以下のような２つのテーブルを対象とします。
| id | user_name | age |
|---|---|---|
| 1 | yamada | 20 |
| 2 | tanaka | 30 |
| 3 | saito | 40 |
| id | text | created_at | user_id |
|---|---|---|---|
| 1 | こんにちは | 2022-06-04T09:25:10.607Z | 1 |
| 2 | こんばんは | 2022-06-04T10:12:49.157Z | 3 |
| 3 | おはよう | 2022-06-04T10:12:55.367Z | 2 |
| 4 | さようなら | 2022-06-04T10:13:45.629Z | 1 |
| 5 | バイバイ | 2022-06-04T10:13:50.390Z | 3 |
| 6 | いい天気ですね | 2022-06-04T10:14:07.688Z | 3 |
| 7 | テストです | 2022-06-04T10:14:16.624Z | 1 |
| 8 | コメントです | 2022-06-04T10:14:21.134Z | 2 |
2-2. 結合テーブルとしてサブクエリを使用する
2-2-1. 指定したい SQL 文
次のような各ユーザーの「最新のコメント投稿時間」を取得する SQL 文を指定してみます。
意味は薄いですが、動作確認にはこれで良いかと思います。
SELECT * FROM user u
LEFT JOIN (SELECT MAX(created_at) last_created_at, user_id FROM comment GROUP BY user_id) c
ON u.id = c.user_id;
2-2-2. SQL を直書きする
const query = await getManager().query(
`SELECT * FROM user u
LEFT JOIN (SELECT MAX(created_at) last_created_at, user_id FROM comment GROUP BY user_id) c
ON u.id = c.user_id`,
);
console.log(query);
出力結果を表示
[
{
id: 1,
age: 20,
user_name: 'yamada',
last_created_at: '2022-06-04T10:14:16.624Z',
user_id: 1
},
{
id: 2,
age: 30,
user_name: 'tanaka',
last_created_at: '2022-06-04T10:14:21.134Z',
user_id: 2
},
{
id: 3,
age: 40,
user_name: 'saito',
last_created_at: '2022-06-04T10:14:07.688Z',
user_id: 3
}
]
2-2-3. クエリビルダー
クエリビルダーを使用すると、次のような記載になります。
データベースではスネークケースですが、キャメルケースで書いても大丈夫です（エンティティの設定によるかもしれませんが）。
2-2-3-1. Entity Manager で記述
const manager = getManager();
const query = await manager
.createQueryBuilder(User, 'u')
.leftJoinAndSelect(
'(' +
manager
.createQueryBuilder(Comment, 'c1')
.select('MAX(c1.createdAt)', 'lastCreatedAt')
.addSelect('c1.userId', 'userId')
.groupBy('c1.userId')
.getQuery() +
')',
'c', // テーブル名のエイリアス
'u.id = c.userId', // 結合条件
)
.getRawMany();
console.log(query);
見たとおりですが、createQueryBuilder(Comment, 'c1') でテーブルをもう一つ作って、getQuery() で文字列化した上で、単純に括弧内に当てはめているだけです。
出力結果を表示
[
{
u_id: 1,
u_user_name: 'yamada',
u_age: 20,
userId: 1,
lastCreatedAt: '2022-06-04T10:14:16.624Z'
},
{
u_id: 2,
u_user_name: 'tanaka',
u_age: 30,
userId: 2,
lastCreatedAt: '2022-06-04T10:14:21.134Z'
},
{
u_id: 3,
u_user_name: 'saito',
u_age: 40,
userId: 3,
lastCreatedAt: '2022-06-04T10:14:07.688Z'
}
]
2-2-3-2. Repository で記述
const qb = getRepository(User).createQueryBuilder('u');
const query = await qb
.leftJoinAndSelect(
qb
.subQuery()
.select('MAX(c1.createdAt)', 'lastCreatedAt')
.addSelect('c1.userId', 'userId')
.from(Comment, 'c1')
.groupBy('c1.userId')
.getQuery(),
'c',
'u.id = c.userId',
)
.getRawMany();
console.log(query);
qb には User リポジトリのクエリビルダーが格納されていますが、サブクエリの部分で from(Comment, 'c1') と指定することで異なるエンティティを呼び出すことができます。
subQuery() メソッドを使用すると、サブクエリを丸括弧で囲ってくれるので、見た目は比較的シンプルになります。
出力結果を表示
[
{
u_id: 1,
u_user_name: 'yamada',
u_age: 20,
userId: 1,
lastCreatedAt: '2022-06-04T10:14:16.624Z'
},
{
u_id: 2,
u_user_name: 'tanaka',
u_age: 30,
userId: 2,
lastCreatedAt: '2022-06-04T10:14:21.134Z'
},
{
u_id: 3,
u_user_name: 'saito',
u_age: 40,
userId: 3,
lastCreatedAt: '2022-06-04T10:14:07.688Z'
}
]
2-3. WHERE 文の条件としてサブクエリを使用する
2-3-1. 指定したい SQL 文
複数テーブルを使用して、次のようなサブクエリを指定してみます。
これは、ユーザーデータの取得時に、最新のコメントも一緒に拾うというものです。
SELECT * FROM user u
LEFT JOIN comment c1 ON u.id = c1.user_id
WHERE c1.created_at = (
SELECT MAX(c2.created_at) FROM comment c2 WHERE c2.user_id = c1.user_id
);
2-3-2. SQL を直書きする
次の指定で、直書きでも動作します。
const query = await getManager().query(
`SELECT * FROM user u
LEFT JOIN comment c1 ON u.id = c1.user_id
WHERE c1.created_at = (
SELECT MAX(c2.created_at) FROM comment c2 WHERE c2.user_id = c1.user_id
)`,
);
console.log(query);
（参考） パラメータを指定する場合
今回の記事では、SQL を直書きする際に、パラメータを指定する例がありませんでした。
パラメータを埋め込む必要がある場合は、次のように第２引数に配列の形で指定することになります。
const query = await manager.query(
`SELECT * FROM item WHERE name = $1 LIMIT $2;`,
[name, 2],
);
console.log(query);
query() メソッドの第２引数に、配列の形式で指定することで、パラメータの指定ができます。
SQL 文中の該当箇所には、$1、$2 の振り合いで記載します。
なお、IN 句の場合は、IN($1) のようにしてもうまく行かないので、文字列に直接埋め込むなどの対応が必要そうです（ただし、セキュリティ上の配慮が必要です）。
2-3-3. クエリビルダー
クエリビルダーを使用すると、次のような記載になります。
2-3-3-1. Entity Manager で記述
const manager = getManager();
const query = await manager
.createQueryBuilder(User, 'u')
.leftJoinAndSelect(Comment, 'c1', 'u.id = c1.userId')
.where(
'c1.createdAt = (' +
manager
.createQueryBuilder(Comment, 'c2')
.select('MAX(c2.createdAt)')
.where('c2.userId = c1.userId')
.getQuery() +
')',
)
.getRawMany();
console.log(query);
基本的にはテーブルが一つの場合と変わりありません。
なお、こちらでは getRawMany() で、SQL からの戻り値をそのまま取得しています。
（参考） パラメータを指定する場合
本例ではパラメータの指定を行っていませんが、指定したい場合は、次のような一般的な形式で指定することができます（当り前ですが）。
.where('u.name = :name', { name: name })
2-3-3-2. Repository で記述
こちらもテーブルが一つの場合と、基本的に同じです。
const qb = getRepository(User).createQueryBuilder('u');
const query = await qb
.leftJoinAndSelect(Comment, 'c1', 'u.id = c1.userId')
.where(
'c1.createdAt = ' +
qb.subQuery()
.select('MAX(c2.createdAt)')
.from(Comment, 'c2')
.where('c2.userId = c1.userId')
.getQuery(),
)
.getRawMany();
console.log(query);
qb には User リポジトリのクエリビルダーが格納されていますが、サブクエリの部分で from(Comment, 'c2') と指定することで異なるエンティティを呼び出すことができます。
さいごに
結論としては、SQL で直接書いた方がよっぽど楽だと思います。
セキュリティー面から考えれば、クエリビルダーの方が良さそうです。
しかし、SQL 直書きでもプレースホルダー（パラメーターの指定）さえ意識していれば、大丈夫ではないかと思います（深い確証はないですが...）。