Add ability to order result set by COALESCE/MAX/MIN combination of columns.

[themrrobert]

Problem

We want to sort on the result of some function of columns.

In one specific case, we need to coalesce two date columns (1 of which will always be null), and we want the newest of either column.

We may also want to sort on the greater of two columns, like Personality, Humor, or the combined value of said columns.

Suggested solution

Add to the orderBy (OrderByWithRelationInput) type:
COALESCE, MIN, MAX, AVG, ?

In postgres, this could be:
ORDER BY ...
COALESCE(col1, col2)
LEAST(col1, col2)
GREATEST(col1,col2)
(col1 + col2) / 2 -- programmatically constructed?

Alternatives

• Changing the data model
• Using raw queries
• Running multiple queries to select X rows ordered in each way, and programmatically merging, sorting, and cutting to size. (ew)
• Allowing raw SQL in the orderBy (ew)
• Adding functionality to add virtual columns to the returned dataset, like
  { extraSelect: [ { name: 'recent', field: { coalesce: [ 'col1', 'col2' ] } } ], orderBy: { recent: 'desc' } }

Additional context

Listing Table:

id      fulfilled    cancelled
1       2024-01-01   null
2       null         2024-02-03
3       2024-03-02   null
4       null         2024-04-01

const result = prisma.listings.findMany({
    orderBy: {
        coalesce: ['fulfilled', 'cancelled']
    },
    take: 2
});

/* Returns:
4, null, 2024-04-01
3, 2024-03-02, null