如何用Prisma在PostgreSQL中查询同记录属性满足大小关系的数据？

问题描述 #

需要使用Prisma在PostgreSQL中查询满足同一条记录内某个属性值大于其他指定属性值的行，类似如下原生SQL：

SELECT * 
FROM Analysis 
WHERE property_1 > property_2;

对应的Prisma模型定义如下：

model Analysis {
  id                    Int             @id @default(autoincrement())
  property_1            Int
  property_2            Int
  property_3            Int
  property_4            Int
}

常见的查询需求示例：

• 返回所有property_1大于property_2的记录
• 返回所有property_2同时大于property_3和property_4的记录

已知与常量值比较的写法（比如property_1 > 10）很简单：

const analyses = await prisma.analysis.findMany({
  where: {
    property_1: {
      gt: 10
    }
  }
})

但尝试用gt: analysis.property_2的写法无法生效（因analysis未定义），需要了解这种同属性比较的需求能否通过Prisma API实现。

解决方案 #

这种需求完全可以实现，有两种常用方式：

1. 使用Prisma Client的字段比较语法（推荐） #

从Prisma 2.16版本开始，官方支持在查询条件中直接引用同表的其他字段，写法简洁：

示例1：查询property_1 > property_2的记录

const analyses = await prisma.analysis.findMany({
  where: {
    property_1: {
      gt: { field: 'property_2' }
    }
  }
})

示例2：查询property_2同时大于property_3和property_4的记录

const analyses = await prisma.analysis.findMany({
  where: {
    AND: [
      { property_2: { gt: { field: 'property_3' } } },
      { property_2: { gt: { field: 'property_4' } } }
    ]
  }
})

2. 使用Prisma Raw查询执行原生SQL #

如果需要更灵活的SQL逻辑，或者使用的Prisma版本较低不支持字段比较语法，可以直接用$queryRaw执行原生SQL：

const analyses = await prisma.$queryRaw`
  SELECT * 
  FROM Analysis 
  WHERE property_1 > property_2;
`

这种方式完全复刻原生SQL的写法，适合复杂的查询场景。

内容的提问来源于stack exchange，提问作者Jackson Holiday Wheeler