Mimic COALESCE behaviour using groups of conditions when querying with Prisma client

Question

I'm trying to replace a SQL query (Postgres) with a Prisma client query, and I'm struggling to implement this COALESCE logic:

COALESCE(date_1, date_2,'1970-01-01 00:00:00') <= CURRENT_TIMESTAMP

As fas as I can tell, Prisma client doesnt currently support COALESCE, so there's no simple way for me to do this. I think I have to group conditions together to replicate this. The logic would be along the lines of:

• ANY of the following
  • date_1 <= CURRENT_TIMESTAMP
  • ALL of the following
    • date_1 IS NULL
    • date_2 <= CURRENT_TIMESTAMP
  • ALL of the following
    • date_1 IS NULL
    • date_2 IS NULL

But I cant work out how to group conditions like this using the Prisma client, because you cant have multiple AND's within an OR (or vis versa):

const records = await client.thing.findMany({
  where: {
    OR: {
      date_1: { lte: new Date() },
      AND: {
        date_1: null, 
        date_2: { lte: new Date() }
      },
      AND: { // <--- Cant do this, because we've already used AND above
        date_1: null, 
        date_2: null
      }
    }
  },
});

We obviously cant repeat the same object key twice, so I cant have two AND statements at the same level, otherwise I get the error:

An object literal cannot have multiple properties with the same name

I'm probably missing something obvious, but I cannot work out how to achieve groups of conditions like this. If anyone can help I'd appreciate it!

---

I should mention that I'm using:

• nodejs v14.19.1
• prisma ^3.14.0

Answer 1

I was indeed missing something obvious. We can pass in an array of groups to an AND or OR group, like so:

const records = await client.thing.findMany({
  where: {
    OR: {
      date_1: { lte: new Date() },
      AND: [
       {
          date_1: null, 
          date_2: { lte: new Date() }
       },
       {
          date_1: null, 
          date_2: null
        }
      ]
    }
  },
});

https://www.prisma.io/docs/concepts/components/prisma-client/filtering-and-sorting#filter-conditions-and-operators