在Prisma ORM的PostgreSQL中使用动态orderBy排序失效问题排查

Prisma的$queryRaw会将你拼接的orderBy字符串当作字符串字面量传入SQL，而非解析为排序语法。比如你传的"acceptedInvites" DESC会被转义成'\"acceptedInvites\" DESC'，数据库会把这个固定字符串作为排序依据，自然得不到预期的排序结果。

使用Prisma提供的sql模板标签和identifier()函数动态生成排序规则，它能正确处理SQL标识符（包括字段别名），同时避免SQL注入风险：

import { sql } from '@prisma/client'; // 需导入sql工具

export const getUsers = async (
  page: number = 1,
  pageSize: number = 10,
  sortField: string = "acceptedInvites",
  sortType: 'asc' | 'desc' = 'desc'
): Promise<any> => {
  try {
    const client = await getClient();
    const skip = (page - 1) * pageSize;
    const total = await client.user.count();

    // 校验合法排序字段，防止非法字段引发SQL错误
    const validSortFields = ['id', 'displayName', 'acceptedInvites', 'invitedBy', 'ranking'];
    if (!validSortFields.includes(sortField)) {
      sortField = 'acceptedInvites'; // 非法字段时用默认值
    }

    const users = await client.$queryRaw`
      SELECT
        "User".id,
        "User"."displayName",
        "User"."avatarUrl",
        "User"."isAvatar",
        COUNT("Referrals".id)::INTEGER as "acceptedInvites",
        "ReferredByUser"."displayName" as invitedBy,
        CAST(RANK() OVER (ORDER BY COUNT("Referrals".id) DESC, "User"."displayName" ASC) AS INTEGER) as ranking
      FROM
        "User"
      LEFT JOIN
        "User" as "Referrals" ON "User"."id" = "Referrals"."referredBy"
      LEFT JOIN
        "User" as "ReferredByUser" ON "User"."referredBy" = "ReferredByUser"."id"
      GROUP BY
        "User".id, "User"."displayName", "ReferredByUser"."displayName"
      ORDER BY ${sql.identifier(sortField)} ${sortType.toUpperCase()}
      OFFSET ${skip}
      LIMIT ${pageSize};
    `;

    return { users, total };
  } catch (error) {
    throw error;
  }
};

• sql.identifier(sortField)：将字段名（或别名）转义为合法的SQL标识符，支持带引号的别名如acceptedInvites。
• 拆分排序字段与方向：分别传入参数，避免拼接字符串导致的转义问题。
• 字段校验：过滤非法输入，防止SQL语法错误或注入风险。

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