如何在单个Prisma查询中实现LEFT JOIN与聚合操作

先补全Prisma Schema的关联关系 #

首先需要明确两个模型的外键关联，让Prisma可以识别两表的JOIN逻辑，调整后的Schema如下：

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)
  // 新增和ratings表的关联
  ratings           ratings[]
}

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)
  // 新增和posts表的关联
  post       posts     @relation(fields: [entry_id], references: [id])

  @@id([entry_id, user_id])
}

Prisma支持在查询时直接嵌套聚合计算，会自动生成LEFT JOIN + GROUP BY的SQL，不需要拆分两次查询，写法如下：

const postsWithRating = await prisma.posts.findMany({
  select: {
    // 按需选择posts表的所有字段
    id: true,
    user_id: true,
    movie_id: true,
    title: true,
    description: true,
    tags: true,
    created_at: true,
    image: true,
    year: true,
    submitted_by: true,
    tmdb_rating: true,
    tmdb_rating_count: true,
    // 嵌套聚合计算平均评分
    user_rating: {
      _avg: {
        rating: true,
      }
    }
  }
})

// 对结果做四舍五入处理，匹配原生SQL的ROUND逻辑
const processedPosts = postsWithRating.map(post => ({
  ...post,
  user_rating: post.user_rating?._avg.rating ? 
    Math.round(post.user_rating._avg.rating * 10) / 10 : 
    0
}))

该方案完全适配Prisma的类型系统，不需要手写SQL，适合1-3个关联表的简单聚合场景。

如果你的聚合逻辑更复杂、需要多表JOIN，或者需要更精细的SQL控制，可以直接用Prisma的$queryRaw方法执行原生SQL：

const postsWithRating = await prisma.$queryRaw`
  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;
`

该方案性能和手写原生SQL完全一致，支持任意复杂的JOIN、聚合逻辑，你可以手动给返回结果定义TS类型来适配类型校验。

如果这个带平均评分的posts查询是高频使用场景，你可以直接在数据库中创建一个视图：

CREATE VIEW posts_with_rating AS
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 Schema中新增对应的视图模型：

model posts_with_rating {
  id                Int      @id
  user_id           Int
  movie_id          Int      @unique
  title             String
  description       String?
  tags              Json?
  created_at        DateTime
  image             String?
  year              Int
  submitted_by      String
  tmdb_rating       Decimal?
  tmdb_rating_count Int?
  user_rating       Decimal?

  @@ignore // 如果你使用prisma migrate工具，加该参数避免迁移时自动生成表
}

之后你就可以像查询普通表一样直接查这个视图，代码最简洁，查询性能也最优，适合固定聚合逻辑的高频查询场景。

内容的提问来源于stack exchange，提问作者James