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

2023-11-22

我有一个包含多个表的数据库,经常需要查询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 交易.

本文内容由网友自发贡献,版权归原作者所有,本站不承担相应法律责任。如您发现有涉嫌抄袭侵权的内容,请联系:hwhale#tublm.com(使用前将#替换为@)

单个 Prisma 查询中的 LEFT JOINS 和聚合 的相关文章

随机推荐