Prisma一对多关系下如何单次查询指定条件的最低分记录

你的需求完全可以通过单次数据库交互实现，不需要多次网络往返。

优先推荐：Prisma原生客户端批量查询（可维护性优先） #

使用Prisma的批量事务接口，可以将4条目标查询在一次网络请求中发送到数据库执行，没有额外的往返开销，代码可读性和可维护性最高，适合绝大多数业务场景：

// 替换为实际业务入参
const targetChallengeAddress = "你的目标挑战地址";
const targetUsername = "要查询的用户名";

const [
  userGasMinScore,
  userByteMinScore,
  globalGasTop5,
  globalByteTop5
] = await prisma.challengeScore.$transaction([
  // 查询指定用户Gas类最低分
  prisma.challengeScore.findFirst({
    where: {
      challengeAddress: targetChallengeAddress,
      username: targetUsername,
      isGasScore: true
    },
    orderBy: { score: "asc" }
  }),
  // 查询指定用户Byte类最低分
  prisma.challengeScore.findFirst({
    where: {
      challengeAddress: targetChallengeAddress,
      username: targetUsername,
      isByteScore: true
    },
    orderBy: { score: "asc" }
  }),
  // 查询全用户Gas类最低分前5条
  prisma.challengeScore.findMany({
    where: {
      challengeAddress: targetChallengeAddress,
      isGasScore: true
    },
    orderBy: { score: "asc" },
    take: 5
  }),
  // 查询全用户Byte类最低分前5条
  prisma.challengeScore.findMany({
    where: {
      challengeAddress: targetChallengeAddress,
      isByteScore: true
    },
    orderBy: { score: "asc" },
    take: 5
  })
]);

极致性能方案：单条原生SQL查询 #

如果你有严格的性能要求，需要真正的单条SQL执行，可以通过CTE语法将所有查询逻辑合并为一条SQL，用Prisma原生查询接口执行：

const queryResult = await prisma.$queryRaw`
WITH user_gas_min AS (
  SELECT * FROM "ChallengeScore"
  WHERE "challengeAddress" = ${targetChallengeAddress}
    AND "username" = ${targetUsername}
    AND "isGasScore" = true
  ORDER BY "score" ASC
  LIMIT 1
),
user_byte_min AS (
  SELECT * FROM "ChallengeScore"
  WHERE "challengeAddress" = ${targetChallengeAddress}
    AND "username" = ${targetUsername}
    AND "isByteScore" = true
  ORDER BY "score" ASC
  LIMIT 1
),
global_gas_top5 AS (
  SELECT * FROM "ChallengeScore"
  WHERE "challengeAddress" = ${targetChallengeAddress}
    AND "isGasScore" = true
  ORDER BY "score" ASC
  LIMIT 5
),
global_byte_top5 AS (
  SELECT * FROM "ChallengeScore"
  WHERE "challengeAddress" = ${targetChallengeAddress}
    AND "isByteScore" = true
  ORDER BY "score" ASC
  LIMIT 5
)
SELECT
  (SELECT row_to_json(user_gas_min) FROM user_gas_min) AS "userGasMinScore",
  (SELECT row_to_json(user_byte_min) FROM user_byte_min) AS "userByteMinScore",
  (SELECT json_agg(global_gas_top5) FROM global_gas_top5) AS "globalGasTop5",
  (SELECT json_agg(global_byte_top5) FROM global_byte_top5) AS "globalByteTop5"
`;

• 绝对不要拉取全量ChallengeScore记录到前端做筛选，这是典型的性能反模式：
  • 数据量上涨后，全量传输会占用大量带宽，接口响应时间会指数级上升
  • 前端JS执行排序、筛选的效率远低于数据库引擎，很容易造成页面卡顿
  • 全量拉取会泄露非必要的用户分数数据，存在数据安全风险
• 数据库侧完成筛选的方案开销远低于全量拉取方案，是性能更优的选择：
  • 数据库引擎天生为筛选、排序、分页操作做了优化，只要添加对应联合索引，上述查询的执行时间可以稳定在毫秒级，不会给数据库造成额外压力
  • 你只需要在ChallengeScore模型中添加如下索引即可完成性能优化：

    model ChallengeScore {
      id               Int        @id @default(autoincrement())
      user             User       @relation(fields: [username], references: [username])
      username         String
      challenge        Challenge  @relation(fields: [challengeAddress], references: [address])
      challengeAddress String
      isGasScore       Boolean
      isByteScore      Boolean
      score            Int
    
      // 新增查询优化索引
      @@index([challengeAddress, username, isGasScore, score])
      @@index([challengeAddress, username, isByteScore, score])
      @@index([challengeAddress, isGasScore, score])
      @@index([challengeAddress, isByteScore, score])
    }
    

• 日常开发优先选择第一种Prisma原生批量事务写法，可维护性高，性能足够覆盖绝大多数业务场景；只有遇到明确的性能瓶颈时，再考虑用原生SQL做极致优化。

内容的提问来源于stack exchange，提问作者maxime