Equivalent of SQL `CASE WHEN...THEN` for custom sort column? (Prisma / GraphQL)

Question

Scenario:

1. Let's assume a model of Events:

   model Event {
     id           Int         @id @default(autoincrement())
     title        String
     happeningAt  DateTime?   //  nullable
     createdAt    DateTime    @default(now())
   }
   

2. Let's assume this is the data:

   id	title	happeningAt	createdAt
   1	ArchCon	2022-07-01 12:00:00Z	2022-06-29 11:53:00Z
   2	BestCon	2022-08-03 12:00:00Z	2022-06-29 11:54:00Z
   3	ComiCon	null	2022-07-02 10:11:00Z
   4	DevCon	2022-07-05 12:00:00Z	2022-07-22 10:33:00Z

3. The requirement:
   Sort ascending by event date (happeningAt), or if unscheduled yet, use creationAt instead. To illustrate, this would be the required query result:

   id	title	sort_date
   1	ArchCon	2022-07-01 12:00:00Z
   3	ComiCon	2022-07-02 10:11:00Z
   4	DevCon	2022-07-05 12:00:00Z
   2	BestCon	2022-08-03 12:00:00Z

Now i remember back in the days i would have just used the CASE WHEN … THEN …-construct to get custom sort_date column (or maybe ISNULL) out of the database.

Can this be achieved with Prisma ORM functionality?

---

XY-Problem disclaimer: Our project uses RedwoodJS, which has a layer of GraphQL on top of this. I did a quick research, but it doesn't seem that GraphQL even remotely is made for what i'm after – but i'd also accept a GraphQL-approach as an answer should i be happily proven wrong.

Of course one can always "manually" transform the query result in typescript, but the question here is whether Prisma or GraphQL (or any other mechanism included in RedwoodJS) do support this natively.

Answer 1

The prisma docs don't mention the ability for using CASE in the orderBy clause.

I have found some issues which seem to discuss this functionality or workaround.

1. This issue seems to be what you're searching for however it's not so clear from the title. https://github.com/prisma/prisma/issues/10335
2. This is in the archived prisma repo with a more precise title https://github.com/prisma/specs/issues/128
3. Computed fields may be able to solve this. It's not possible yet but it looks like it's on the top of the Prisma Client Extension epic https://github.com/prisma/client-planning/issues/25. You would then be able to add this computed field- which would pull in the happeningAt date or if not yet scheduled then the creationAt- and sort on it. https://github.com/prisma/prisma/issues/3394
4. I guess using a prisma raw query would be another option https://github.com/prisma/client-planning/issues/25.
5. If you're not paginating you can sort with javascript after pulling the records from the database.
6. Even with paginating you can get all the records and follow option 5, but this may add a lot to your execution time (the reason you may be doing paginating to begin with)