Prisma: Finding items where two fields have the same value

Question

I would like to find items in a Prisma db where the values for two columns are the same. The use case is to compare the 'created_at' and 'updated_at' fields to find items that have never been updated after their initial creation. In raw SQL I would do something like:

select updated_at,
       cast(sign(sum(case when updated_at = created_at then 
          1 
       else
          0
       end)) as int) as never_modified
  from tab
 group by updated_at

Is it possible to achieve this in Prisma?

Answer 1

You would need to use Raw Queries to compare time values from the same table.

Here's an example of how you could achieve this, assuming a PostgreSQL database for the following query.

import { PrismaClient } from '@prisma/client'

const prisma = new PrismaClient()

async function initiateDatesComparisonRawQuery() {
  const response =
    await prisma.$queryRaw`SELECT * FROM "public"."Project" WHERE "created_at" = "updated_at";`;

  console.log(response);
}

await initiateDatesComparisonRawQuery();