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

uwopmtnx 于 2023-06-28 发布在 Mysql

关注(0)|答案(2)|浏览(243)

我有一个包含多个表的数据库，经常需要使用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 s。
我如何在Prisma中创建查询/模型/东西来实现上述目标？

mysql

来源：https://stackoverflow.com/questions/68761366/left-joins-and-aggregation-in-a-single-prisma-query

2条答案

按热度按时间

ddhy6vgd1#

是的，这是可能的与棱镜！为了使其工作，您需要在“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 transactions。

赞(0）回复(0）举报 2023-06-28

w1jd8yoj2#

尽管有公认的答案，但实际的答案是：没有
为了让实际的高性能联接工作，他们必须解决一个问题，这个问题在撰写此回复时已经存在了大约一年半：https://github.com/prisma/prisma/issues/5184
目前，没有办法将表连接在一起。包含关系的查询通过使用单独的查询仅包含关系数据。

赞(0）回复(0）举报 2023-06-28

我来回答

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

2条答案

相关问题

热门标签

最新问答