Mysql Question posted in
The official MySQL documentation can be found here.

Mysql – Prisma ORM how to order by GREATEST()

I have a database table with two columns updated_at and children_updated_at and I would like to show the latest modified records first based on the SQL function GREATEST(updated_at, children_updated_at).

The SQL query I’d like to generate is:

select * from Account ORDER BY GREATEST(updated_at, children_updated_at);

The output should be ordered with the highest value of either updated_at or children_updated_at first.

An input table like this:

name updated_at children_updated_at
User 1 2024-05-07 10:00:00.000 2024-05-07 13:00:00.000
User 2 2024-05-07 11:00:00.000 2024-05-07 09:00:00.000
User 3 2024-05-07 12:00:00.000 2024-05-07 10:00:00.000

Should generate an output like this:

[
  { "name": "User 1",
    "updated_at": "2024-05-07 10:00:00.000",
    "children_updated_at": "2024-05-07 13:00:00.000"
  },
  { "name": "User 3",
    "updated_at": "2024-05-07 12:00:00.000",
    "children_updated_at": "2024-05-07 10:00:00.000"
  },
  { "name": "User 2",
    "updated_at": "2024-05-07 11:00:00.000",
    "children_updated_at": "2024-05-07 09:00:00.000"
  }
]

I could not find anything about using GREATEST() or other SQL functions that combine columns in the Prisma documentation.

The actual query is a lot more complex, and because of that a raw sql query will not work for me.
Is there a way to achieve this requirement using Prisma ORM?

Tags:

2

Answers


  1. 0

    First create a indicator column and do order by on that column

    MySQL query:

    select *
from inputTable
order by GREATEST(COALESCE(updated_at, 0), COALESCE(children_updated_at, 0)) desc

    or

    With T as(
  select *,
  GREATEST(updated_at, children_updated_at) as order_by
from inputTable
)
select name, updated_at, children_updated_at
from T
order by order_by desc

    or

    select name, updated_at, children_updated_at
from (
  select *,
  GREATEST(updated_at, children_updated_at) as order_by
  from inputTable
) T
order by order_by desc

    View Result: db<>fiddle

    Output:

    enter image description here

    GREATEST(dt1, dt2) Will work. But if any date is null, then it will not work.

    So, better use GREATEST(COALESCE(dt1, 0), COALESCE(dt2, 0))

    Login or Signup to reply.
  2. 0

    Prisma does not directly support using functions like GREATEST. However, you can achieve similar functionality by using a combination of Prisma’s .orderBy() method wit js

    const accounts = await prisma.account.findMany({
  orderBy: {
    updatedAt: 'desc', 
    childrenUpdatedAt: 'desc',
  },
});


const result = accounts.map(account => ({
  name: account.name,
  updated_at: account.updatedAt.toISOString(), // Convert to ISO string
  children_updated_at: account.childrenUpdatedAt.toISOString(), // Convert to ISO string
}));

console.log(result);

    This example will fetch the records from the database ordered first by updatedAt in descending order, and then by childrenUpdatedAt in descending order.

    Hope this works for you…

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search