Prisma中关联模型字段聚合与扩展：解决groupBy查询无法直接包含关联信息的挑战

理解Prisma的聚合与关联查询

在使用prisma进行数据库操作时，我们经常需要对数据进行聚合，例如计算某个字段的总和、平均值或计数。prisma提供了强大的groupby功能来实现这一目标。考虑以下两个模型：admins（管理员）和payment（支付），其中payment模型通过admin_id与admins模型关联：

model admins {
  id        Int       @id @default(autoincrement())
  name      String
  last_name String
  phone     String    @unique
  email     String?   @unique
  nic       String?   @unique
  image     String?
  payments  payment[]
}

model payment {
  id          Int       @id @default(autoincrement())
  amount      Int
  description String?
  date        DateTime? @db.Date
  admin_id    Int
  admins      admins    @relation(fields: [admin_id], references: [id])
}

我们的目标是获取每个管理员的总支付金额。一个直观的Prisma查询方式是使用groupBy：

const data = await prisma.payment.groupBy({
  by: ["admin_id"],
  _sum: {
    amount: true,
  },
});
console.log(data);

这段代码能够正确地返回每个admin_id对应的总支付金额，结果类似于：{ _sum: { amount: 1650 }, admin_id: 1 }。然而，实际需求往往更进一步：我们希望在聚合结果中直接包含管理员的name和last_name，例如：

{ 
   _sum: { amount: 1650 },
   admin_id: 1,
   name: "admin-name",
   last_name: "admin-last-name"
}

Prisma groupBy的局限性

Prisma目前的设计决定是，groupBy查询不支持与include或select操作符同时使用来获取关联模型的字段。这意味着，你无法在一次groupBy查询中直接聚合数据并同时获取关联表的额外信息。这种限制是出于设计复杂性和性能考量，因为在数据库层面，聚合操作和关联查询通常是不同的执行路径。

解决方案：分步查询与数据合并

鉴于groupBy的这一限制，最直接且推荐的解决方案是执行两次查询并手动合并数据。这种方法分为以下几个步骤：

1. 执行第一次查询进行聚合： 使用groupBy获取你所需的聚合数据，例如每个admin_id的总支付金额。
2. 遍历聚合结果并执行第二次查询： 对第一次查询返回的每个聚合结果，使用其关联ID（例如admin_id）去查询关联模型的详细信息（例如admins表的name和last_name）。
3. 合并数据： 将第二次查询获取到的关联信息合并到对应的聚合结果中。

以下是实现此方案的示例代码：

微信 WeLM

WeLM不是一个直接的对话机器人，而是一个补全用户输入信息的生成模型。

33

查看详情

import { PrismaClient } from '@prisma/client';

const prisma = new PrismaClient();

async function getAdminPaymentTotalsWithDetails() {
  // 步骤1: 执行第一次查询进行聚合
  const paymentData = await prisma.payment.groupBy({
    by: ["admin_id"],
    _sum: {
      amount: true,
    },
  });

  // 步骤2 & 3: 遍历聚合结果，执行第二次查询并合并数据
  const dataWithAdminInfo = await Promise.all(paymentData.map(async (item) => {
    // 根据admin_id查询对应的管理员信息
    const admin = await prisma.admins.findUnique({
      where: { id: item.admin_id },
      select: { // 仅选择需要的字段以优化性能
        name: true,
        last_name: true,
      }
    });

    // 合并数据：将管理员的name和last_name添加到聚合结果中
    return {
      ...item, // 展开聚合结果，包含_sum和admin_id
      name: admin?.name, // 使用可选链操作符以防admin为null
      last_name: admin?.last_name
    };
  }));

  console.log(dataWithAdminInfo);
  return dataWithAdminInfo;
}

getAdminPaymentTotalsWithDetails()
  .catch(e => {
    console.error(e);
    process.exit(1);
  })
  .finally(async () => {
    await prisma.$disconnect();
  });

代码解析：

• prisma.payment.groupBy(...): 这是第一步，它按照admin_id对支付金额进行求和。
• Promise.all(paymentData.map(async (item) => {...})): 这一部分是解决方案的核心。
  • paymentData.map(...): 遍历groupBy返回的每个聚合结果（即每个管理员的总支付信息）。
  • async (item) => {...}: 对于每个聚合结果item，执行一个异步操作。
  • prisma.admins.findUnique({ where: { id: item.admin_id } }): 这是第二次查询。它利用聚合结果中的admin_id去admins表中查找对应的管理员记录。为了性能考虑，我们使用select只获取name和last_name字段。
  • return { ...item, name: admin?.name, last_name: admin?.last_name };: 使用ES6的展开运算符（...item）将原始聚合结果的所有字段复制过来，然后添加或覆盖name和last_name字段。admin?.name使用了可选链操作符，以防万一findUnique没有找到对应的管理员（尽管在通常情况下这不应该发生）。
• Promise.all(...): 确保所有内部的findUnique查询都完成后，才返回最终的数据数组。这使得这些查询可以并行执行，提高了效率。

注意事项与性能考量

虽然上述分步查询的方法能够有效解决问题，但在处理大量数据时，需要考虑其潜在的性能影响：

• N+1 查询问题： 如果groupBy的结果集非常大（例如，有成千上万个不同的admin_id），那么Promise.all内部的map操作将导致执行相同数量的findUnique查询。这被称为“N+1查询问题”，可能导致数据库连接池耗尽或响应时间过长。
• 优化策略：
  • 批量查询： 如果可能，可以考虑将所有需要查询的admin_id收集起来，然后执行一次prisma.admins.findMany查询，使用where: { id: { in: [adminIds] } }来一次性获取所有管理员信息，而不是逐个查询。然后，在内存中将这些信息与聚合数据进行匹配。
  • 数据库视图/自定义SQL： 对于极其复杂的聚合和关联需求，或者当N+1问题变得不可接受时，直接使用Prisma的$queryRaw或$queryRawUnsafe执行原始SQL查询，甚至在数据库中创建视图（View），可能是更高效的选择。原始SQL能够利用数据库自身的强大聚合和连接能力，在一次操作中完成所有工作。
  • 数据量评估： 在决定采用哪种方案之前，评估预期的groupBy结果集大小。对于小到中等规模的数据集（例如，几百到几千个分组），分步查询的方法通常是可接受的。

总结

Prisma的groupBy功能在数据聚合方面表现出色，但其不直接支持include/select关联字段的特性要求开发者采用分步策略。通过先进行聚合，然后利用聚合结果中的ID进行第二次查询并手动合并数据，可以有效地获取包含关联模型信息的聚合结果。在实施此方案时，务必根据数据规模和性能要求，权衡N+1查询问题，并在必要时考虑批量查询或原始SQL等更高级的优化方法。

以上就是Prisma中关联模型字段聚合与扩展：解决groupBy查询无法直接包含关联信息的挑战的详细内容，更多请关注php中文网其它相关文章！