在单个Prisma查询中使用LEFT JOIN和聚合

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 AVG(rating) FROM 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中创建一个查询/模型/某些东西来实现上述要求？

- James

Answer 1

是的，使用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 transactions。

- Victor Iris

---

我认为问题希望直接解决这个问题，也就是生成一个SQL语句同时完成两个操作。虽然两个查询“可行”，但并不是一种解决方案。 - Jayson Minard

Answer 2

尽管有一个被接受的答案，但实际上的答案是：没有。

要使实际的高性能连接起作用，必须解决一个在撰写此回复时已经存在了一年半左右的问题：https://github.com/prisma/prisma/issues/5184

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

- Fmstrat