左连接和聚合在单个Prisma查询中

Question

我有一个包含多个表的数据库，经常需要使用LEFT JOIN进行查询，以便结果包含来自其他表的聚合数据。我的Prisma模式的片段:

model posts {
id                Int      @id @unique @default(autoincrement())
user_id           Int
movie_id          Int      @unique
title             String   @db.Text
description       String?  @db.Text
tags              Json?
created_at        DateTime @default(now()) @db.DateTime(0)
image             String?  @default("https://picsum.photos/400/600/?blur=10") @db.VarChar(256)
year              Int
submitted_by      String   @db.Text
tmdb_rating       Decimal? @default(0.0) @db.Decimal(3, 1)
tmdb_rating_count Int?     @default(0)
}
model ratings {
id         Int       @unique @default(autoincrement()) @db.UnsignedInt
entry_id   Int       @db.UnsignedInt
user_id    Int       @db.UnsignedInt
rating     Int       @default(0) @db.UnsignedTinyInt
created_at DateTime  @default(now()) @db.DateTime(0)
updated_at DateTime? @db.DateTime(0)
@@id([entry_id, user_id])
}

如果我想在查询posts时返回平均评级，我可以使用如下查询:

SELECT 
p.*, ROUND(AVG(rt.rating), 1) AS user_rating
FROM
posts AS p
LEFT JOIN
ratings AS rt ON rt.entry_id = p.id
GROUP BY p.id;

我不完全确定如何/是否我可以实现类似的东西与Prisma，因为它站在现在，这似乎需要两个单独的查询，这不是最优的，因为有时需要2或3连接或SELECTs从其他表。

我如何在Prisma中创建查询/模型/某些东西来实现上述目标?

Answer 1

是的，这是可能的Prisma!为了使其工作，您需要在您的schema上指定。记录模型是如何相互关联的。这样，代码生成将设置可能的查询/操作。

改成:

model Post {
id              Int      @id @unique @default(autoincrement()) @map("id")
userId          Int      @map("user_id")
movieId         Int      @unique @map("movie_id")
title           String   @map("title") @db.Text
description     String?  @map("description") @db.Text
tags            Json?    @map("tags")
createdAt       DateTime @default(now()) @map("created_at") @db.DateTime(0)
image           String?  @default("https://picsum.photos/400/600/?blur=10") @map("image") @db.VarChar(256)
year            Int      @map("year")
submittedBy     String   @map("submitted_by") @db.Text
tmdbRating      Decimal? @default(0.0) @map("tmdb_rating") @db.Decimal(3, 1)
tmdbRatingCount Int?     @default(0) @map("tmdb_rating_count")
ratings         Rating[]
@@map("posts")
}
model Rating {
id        Int       @unique @default(autoincrement()) @map("id") @db.UnsignedInt
userId    Int       @map("user_id") @db.UnsignedInt
rating    Int       @default(0) @map("rating") @db.UnsignedTinyInt
entryId   Int
entry     Post      @relation(fields: [entryId], references: [id])
createdAt DateTime  @default(now()) @map("created_a") @db.DateTime(0)
updatedAt DateTime? @map("updated_a") @db.DateTime(0)
@@id([entryId, userId])
@@map("ratings")
}

注意:请遵循命名约定(单数形式，PascalCase)。我在上面的模式中为您做了这些更改。@@map允许您设置在数据库表上使用的名称。

然后，在生成客户端之后，您将获得对关系操作的访问权。

// All posts with ratings data
const postsWithRatings = await prisma.post.findMany({
include: {
// Here you can keep including data from other models
ratings: true
},
// you can also "select" specific properties
});
// Calculate on your API
const ratedPosts = postsWithRatings.map( post => {
const ratingsCount = post.ratings.length;
const ratingsTotal = post.ratings.reduce((acc, b) => acc + b.rating, 0)
return {
...post,
userRating: ratingsTotal / ratingsCount
}
})
// OR...

// Get avg from db
const averages = await prisma.rating.groupBy({
by: ["entryId"],
_avg: {
rating: true
},
orderBy: {
entryId: "desc"
}
})
//  Get just posts
const posts = await prisma.post.findMany({
orderBy: {
id: "desc"
}
});
// then match the ratings with posts
const mappedRatings = posts.map( (post, idx) => {
return {
...post,
userRating: averages[idx]._avg.rating
}
})

您还可以创建一个带有方法的类来简化此操作。但是我强烈建议你在API上实现GraphQL。这样，您就可以在您的帖子类型中添加一个虚拟字段。任何时候一个帖子被单独请求或在一个列表中请求，都会计算平均值。以同样的方式，您可以灵活地从其他模型和"join"请求数据。会自动为您办理。

最后但并非最不重要的是，如果你想在同一时间做很多查询，你可以利用Prisma事务。

Answer 2

尽管接受了答案，但实际答案是:不。

对于实际的性能连接工作，他们必须解决一个问题，这个问题在写这个响应时已经存在了大约一年半:https://github.com/prisma/prisma/issues/5184

目前，没有办法将表连接在一起。包含关系的查询通过使用单独的查询只包含关系数据。