单个 Prisma 查询中的 LEFT JOINS 和聚合

我有一个包含多个表的数据库，经常需要查询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 个联接或者SELECT来自其他表的。

如何在 Prisma 中进行查询/模型/某些内容来实现上述目标？

是的，Prisma! 可以实现这一点。为了使其工作，您需要在“schema.prisma”文件中指定模型如何related与彼此。这样，代码生成将设置可能的查询/操作。

改成这样：

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。这样，您就可以在帖子类型中添加虚拟字段。任何时候单独或在列表中请求帖子时，都会计算平均值。以同样的方式，您可以灵活地从其他模型请求数据，并且“JOINS”将自动为您处理。

最后但并非最不重要的一点是，如果您想同时执行大量查询，您可以利用Prisma 交易.