注意
結論として、Relation queriesで示される関連テーブルのまとめて取得の方法では、JOIN句が生成されずクエリが2つに分けられます。
クエリを1つにまとめる方法は私の力不足で解決できませんでしたので書いておりません。
ご了承の上お読みください。
はじめに
Prismaで inner join と left outer join を行いたいと思いました。
環境
ts-nodeを用いて動作させます。
DBはMySQLを用います。
prisma schema
以下条件で3つテーブルを作成します
- userテーブルが存在
- user_configテーブルはuserテーブルに対して1対1に紐づく
- postテーブルはuserテーブルに対して1対多で紐づく
generator client {
provider = "prisma-client-js"
}
datasource db {
provider = "mysql"
url = env("DATABASE_URL")
}
model User {
id Int @id @default(autoincrement())
createdAt DateTime @default(now())
updateAt DateTime @updatedAt
name String
Post Post[]
UserConfig UserConfig?
}
model UserConfig {
id Int @id @default(autoincrement())
createdAt DateTime @default(now())
updateAt DateTime @updatedAt
userId Int @unique
user User @relation(fields: [userId], references: [id], onDelete: Cascade)
category String @db.VarChar(2)
}
model Post {
id Int @id @default(autoincrement())
createdAt DateTime @default(now())
userId Int
user User @relation(fields: [userId], references: [id], onDelete: Cascade)
message String @db.Text
}
Relation queriesに沿ってinner joinを試す
userとuser_configをinner joinさせます。
理想的は下記クエリです。
select
user.id,
user.name,
uc.category
from
user
inner join user_config uc
on uc.user_id = user.id
コードを書いてみます。
出力されるクエリもほしいので、 log: ['query'] オプションをつけます。
import { PrismaClient } from '@prisma/client';
(async () => {
const prisma = new PrismaClient({
log: ['query'],
});
const params = await prisma.user.findMany({
select: {
id: true,
name: true,
UserConfig: {
select: {
category: true
}
}
}
});
console.log(params);
})();
出力結果を見てみます。
[
{ id: 1, name: 'red', UserConfig: { category: '01' } },
{ id: 2, name: 'white', UserConfig: { category: '02' } },
{ id: 3, name: 'blue', UserConfig: { category: '03' } },
{ id: 4, name: 'green', UserConfig: { category: '04' } }
]
できてそう。
じゃあ次は出力されたクエリを見てみましょう。(冗長な部分を削ってわかりやすく整形します)
-- prisma:query
SELECT
id,
name
FROM
User;
-- prisma:query
SELECT
id,
category
userId
FROM
UserConfig
WHERE
userId IN (1, 2, 3, 4);
ええ……クエリが2つ走っているんですが……
クエリをまとめたい
調べていたらこんな記事を発見しました。
joinがクエリにくっついているケースを見るに、joinされるテーブルのカラムが条件が指定されていればinner joinとして扱われていそう。
やってみます。
import { PrismaClient } from '@prisma/client';
(async () => {
const prisma = new PrismaClient({
log: ['query'],
});
const params = await prisma.user.findMany({
select: {
id: true,
name: true,
UserConfig: {
select: {
category: true
}
}
},
where: {
UserConfig: {
category: {
in: ['01', '02']
}
}
}
});
console.log(params);
})();
取得結果は想定通り。
[
{ id: 1, name: 'red', UserConfig: { category: '01' } },
{ id: 2, name: 'white', UserConfig: { category: '02' } }
]
クエリを見てみます。
-- prisma:query
SELECT
User.id,
User.name
FROM
User
WHERE
User.id IN (
SELECT
User.id
FROM
User
INNER JOIN
UserConfig
ON UserConfig.userId = User.id
WHERE (
UserConfig.category IN ('01', '02') AND
User.id IS NOT NULL
)
)
-- prisma:query
SELECT
UserConfig.id,
UserConfig.category,
UserConfig.userId
FROM
UserConfig
WHERE
UserConfig.userId IN ('01', '02')
userテーブルの絞り込みのためにjoinは使われているけど、selectでuser_configを取得するには結局クエリ分けなきゃいけないのね。とほほ。
つまりは取得はクエリを分けて、条件にはjoinが使われるというわけですか。
それならouter joinであっても、N+1問題は起きずに済みそうですね。
prisma/prisma#5745でもN+1問題は回避していると述べられています。
outer join
一応確認だけしてみます。
import { PrismaClient } from '@prisma/client';
(async () => {
const prisma = new PrismaClient({
log: ['query'],
});
const params = await prisma.user.findMany({
select: {
id: true,
name: true,
Post: {
select: {
message: true
}
}
},
where: {
UserConfig: {
category: '01'
}
}
});
console.log(JSON.stringify(params, null , ' '));
})();
出力結果はよさげ。
[
{
id: 1,
name: 'red',
Post: [
{
message: '01-1'
},
{
message: '01-2'
},
{
message: '01-3'
}
]
}
]
1対多なのでleft outer joinで取りたくなる条件を書いてみたのですが、やはりクエリ2つで取ってきていました。そしてクエリが分割されているのでouter joinですらありませんでした。
-- prisma:query
SELECT
User.id,
User.name
FROM
User
WHERE
User.id IN (
SELECT
User.id
FROM
User
INNER JOIN UserConfig ON UserConfig.userId = User.id
WHERE (
UserConfig.category IN ('01') AND
User.id IS NOT NULL
)
-- prisma:query
SELECT
Post.id,
Post.message,
Post.userId
FROM
Post
WHERE
Post.userId IN (1)
私の力不足で、そもそもouter joinをどうやって発生させるかが思いつかないのでここで終わります。
Are LEFT OUTER JOIN's Possible?? 「left outer joinは可能ですか？」
こんなタイトルのディスカッションが生まれる一端をみました。
おわりに
prismaでのjoinの生成されるクエリと挙動を確認しました。
現状、想定通りのクエリを発行してもらうにはraw queryしかなさそうですね。
結局、本記事では何も解決していません。
issueを見ていると
なんてのもあるので、もしかしたら今後はORMで書いた内容が一つのクエリで出力されるかもしれませんね。
今後に期待です。終わり。