Find records with a value greater than another column-CodePudding

I need to find rows using Prisma in Postgresql that match certain criteria where one property has a greater value than certain other properties in the same record.

Something along the lines of this (please forgive my rusty SQL):

SELECT * 
FROM Analysis 
WHERE property_1 > property_2;

Here is the model:

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

So for example, a query could be to return all records where property_1 is greater than property_2; or where property_2 is greater than property_3 and property_4.

If I had a constant value (like 10, for example), it could be relatively simple; something like this:

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

But obviously this is different, because it needs to be a variable property in the record itself. In this example, how would we replace 10 with property_2?

I've tried gt: analysis.property_2, but of course that doesn't work, because analysis is undefined.

Is this even possible with Prisma's API?

CodePudding user response：

Prisma version >=4.3.0 only.

add this to client generator

generator client {
  provider        = "..."
  previewFeatures = ["fieldReference"]
}

Now you can reference fields in where object.

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