Prisma按关联记录数排序后如何获取指定记录的排名

Prisma 目前没有内置排名计算的封装方法，可以根据项目数据量和数据库类型选以下几种实现方式：

方案1：原生SQL+窗口函数（性能最优，推荐生产环境用） #

直接通过数据库的窗口函数计算排名，不需要拉取全表数据，性能最高。

排名函数选择 #

• 用RANK()：邀请数相同的用户获得相同排名，后续排名会跳号（比如2个用户并列第1，下一个用户直接排第3）
• 用DENSE_RANK()：邀请数相同的用户获得相同排名，后续排名不跳号（比如2个用户并列第1，下一个用户排第2）

PostgreSQL 实现代码 #

/**
 * 查询指定用户的等候列表排名
 * @param userId 目标用户ID
 */
const getUserWaitlistRank = async (userId: number) => {
  const rankResult = await prisma.$queryRaw<{ rank: number }[]>`
    SELECT RANK() OVER (
      ORDER BY (SELECT COUNT(*) FROM "User" invited WHERE invited."createdById" = u.id) DESC
    ) as rank
    FROM "User" u
    WHERE u.id = ${userId}
  `
  return rankResult[0]?.rank ?? 0
}

MySQL 8.0+ 实现代码 #

语法和PG基本一致，只需要去掉表名/字段名的双引号即可。

MySQL 5.x 兼容写法（不支持窗口函数版本） #

const getUserWaitlistRankMySQL5 = async (userId: number) => {
  const rankResult = await prisma.$queryRaw<{ rank: number }[]>`
    SELECT (
      SELECT COUNT(DISTINCT inviter.id) + 1
      FROM "User" inviter
      WHERE (
        SELECT COUNT(*) FROM "User" invited WHERE invited."createdById" = inviter.id
      ) > (
        SELECT COUNT(*) FROM "User" currentInvited WHERE currentInvited."createdById" = u.id
      )
    ) as rank
    FROM "User" u
    WHERE u.id = ${userId}
  `
  return rankResult[0]?.rank ?? 0
}

方案2：内存计算（适合小数据量场景） #

如果等候列表用户量在万级以内，可以直接拉取排序后的全量用户列表，在内存中计算排名，不需要写原生SQL：

const getUserWaitlistRankInMemory = async (userId: number) => {
  // 拉取按邀请数降序排序的用户列表
  const sortedUserList = await prisma.user.findMany({
    select: {
      id: true,
      _count: { select: { createdUsers: true } }
    },
    orderBy: { createdUsers: { _count: "desc" } }
  })

  // 顺序排名（相同邀请数按列表顺序区分名次）
  const sequentialRank = sortedUserList.findIndex(user => user.id === userId) + 1

  // 如果需要实现相同邀请数并列排名，用下面的逻辑
  let denseRank = 0
  let lastInviteCount = -1
  for (const [index, user] of sortedUserList.entries()) {
    if (user._count.createdUsers !== lastInviteCount) {
      denseRank = index + 1
      lastInviteCount = user._count.createdUsers
    }
    if (user.id === userId) break
  }

  return { sequentialRank, denseRank }
}

这个方案的缺点是用户量超过10万后，全表拉取的IO和内存开销会明显升高，不适合大体量项目。 #

长期优化方案 #

如果等候列表功能会长期运营，建议给User表增加冗余计数字段，避免每次排名都做关联统计：

model User {
  // 原有字段不变
  inviteCount Int @default(0) // 累计邀请人数，每次有新用户通过邀请注册时+1
}

加了冗余字段后，不管是列表排序还是排名计算，都不需要关联createdUsers表做count，查询性能会提升数倍，排名查询的SQL也可以简化为：

const getRankWithOptimizedField = async (userId: number) => {
  const rankResult = await prisma.$queryRaw<{ rank: number }[]>`
    SELECT RANK() OVER (ORDER BY "inviteCount" DESC) as rank
    FROM "User"
    WHERE id = ${userId}
  `
  return rankResult[0]?.rank ?? 0
}

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