英文:
Prisma: Create-if-not-exists multiple explicit many-to-many relations in one query
问题
我有 Playlist 和 Video 模型，它们之间有一个 显式 的多对多关系（用于存储额外数据）。在我的服务器代码中，假设我有一个 Video 主键的数组，如果它们尚未存在于播放列表中，我该如何将这些视频添加到播放列表中？
我最接近的工作示例是这样的。据我理解，upsert 实际上是一个 "如果不存在则创建，否则更新" 的操作，但对于 update 使用了一个空对象，因此在某种程度上类似于我想要的操作（尽管稍微复杂一些）。然而，在这里我一次只添加一个视频到播放列表中。是否有一种方法可以一次添加 多个 视频（即在 Playlist 和 Video 之间的关联表中创建多个多对多关系），最好是在一个查询中？
const id = /* 播放列表的ID */
const videoUrl = /* 视频的URL，这是它的主键 */
// 我想要使用视频URL的数组，而不仅仅是一个
const updatedPlaylist = await prisma.playlist.update({
where: { id },
include: { videos: { include: { video: true } } },
data: {
videos: {
upsert: {
where: {
playlistId_videoUrl: {
playlistId: id,
videoUrl: videoUrl,
},
},
create: {
addedAt: new Date(),
video: {
connect: { url: videoUrl },
},
},
update: {},
},
},
},
});
我尝试阅读了 Prisma 文档，但有太多不同的 API，我感到困惑，甚至关于从哪里开始查询（我是在update Playlist 模型并创建多个关联关系吗？还是我在 Video 模型上使用 updateMany，然后为每个视频创建关联关系？还是我首先要create 关联关系，然后以某种方式将它们连接到 Playlist 和 Video 模型的两侧？）
英文:
I have Playlist and Video models, with an explicit many-to-many relationship (to hold extra data). Say in my server code, I have an array of Video primary keys, how do I add these videos to a playlist, if they aren't already in it?
My closest working example is this. To my understanding, upsert is actually a "create-if-not-exists, otherwise update" operation, but with an empty object for update thus working similarly to what I want (albeit slightly more complicated). However, here I am only adding 1 video to the playlist at once. Is there a way to add multiple videos (aka creating multiple many-to-many relations to the join table between Playlist & Video), ideally in one query?
const id = /* Playlist's ID */
const videoUrl = /* Video's Url, which is its primary key */
// I want to use an array of videoUrls instead, not just 1
const updatedPlaylist = await prisma.playlist.update({
where: { id },
include: { videos: { include: { video: true } } },
data: {
videos: {
upsert: {
where: {
playlistId_videoUrl: {
playlistId: id,
videoUrl: videoUrl,
},
},
create: {
addedAt: new Date(),
video: {
connect: { url: videoUrl },
},
},
update: {},
},
},
},
});
I've tried reading Prisma docs, but there's too many different APIs and I got confused, even regarding where to start the query from (do I update the Playlist model and create multiple join relations? Do I updateMany the Video model and for each also create a join relation? Or do I create the join relation first, then somehow connect them to the Playlist and Video models on both sides?)
答案1
得分: 2
我曾经遇到过相同的问题，我的解决方案是删除联接表中的所有行，然后再次添加它们。
const create = [];
data.videos.forEach((video) => {
create.push({
video: {
connect: {
url: videos.url,
},
},
});
});
const playlist = await this.prisma.playlist.update({
where: { id: data.id },
data: {
...data,
videos: {
deleteMany: {
playlist_id: id,
},
create,
},
updated_at: new Date(),
},
});
在前面的代码中，你可以更新联接表行中的额外数据，并且也可以从播放列表中删除视频。
英文:
I had the same problem once, my solution was deleting all the rows in the join table and then add them again.
const create = [];
data.videos.forEach((video) => {
create.push({
video: {
connect: {
url: videos.url,
},
},
});
});
const playlist = await this.prisma.playlist.update({
where: { id: data.id },
data: {
...data,
videos: {
deleteMany: {
playlist_id: id,
},
create,
},
updated_at: new Date(),
},
});
in the previous code you can update the extra data in the join table row and remove video from the playlist too.
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论