Prisma findMany排序：如何实现coalesce(category.parentId, categoryId)逻辑？

使用Prisma执行Item模型的findMany查询时，需要按特定规则排序：优先按Item关联的Category的parentId排序，若该parentId不存在，则按Item的categoryId排序。原生SQL可通过orderBy coalesce(c.parentId, categoryId)实现，想了解Prisma是否有原生实现方式。

数据模型如下：

model Item {
    id         Int        @id @default(autoincrement())
    title      String
    categoryId Int
    category   Category   @relation(fields: [categoryId], references: [id])
}

model Category {
    id            Int        @id @default(autoincrement())
    title         String
    parentId      Int?
    parent        Category?  @relation("categoryRelation", fields: [parentId], references: [id], onDelete: NoAction, onUpdate: NoAction)
    subCategories Category[] @relation("categoryRelation")
    items         Item[]
}

Prisma 4.16.0及以上版本支持在orderBy中使用表达式函数，可直接用coalesce实现该排序逻辑，无需切换到原生SQL查询。

原生Prisma实现代码 #

const items = await prisma.item.findMany({
  include: {
    category: true, // 必须关联查询Category，才能获取parentId字段
  },
  orderBy: [
    {
      _relevance: {
        sort: 'asc', // 可根据需求改为desc（降序）
        score: {
          // 用coalesce实现优先取parentId，不存在则取categoryId的排序规则
          expression: `coalesce("category"."parentId", "categoryId")`
        }
      }
    }
  ]
});

若希望用Prisma的sql函数保证语法兼容性，可改写为：

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

const items = await prisma.item.findMany({
  include: { category: true },
  orderBy: [
    {
      _relevance: {
        sort: 'asc',
        score: {
          expression: Prisma.sql`coalesce("category"."parentId", "categoryId")`
        }
      }
    }
  ]
});

低版本兼容方案（Prisma < 4.16.0） #

如果你的Prisma版本不支持表达式排序，可直接使用$queryRaw执行原生SQL：

const items = await prisma.$queryRaw`
  SELECT i.*
  FROM "Item" i
  JOIN "Category" c ON i."categoryId" = c.id
  ORDER BY coalesce(c."parentId", i."categoryId") ASC
`;

内容的提问来源于stack exchange，提问作者Michael Von Bargen