Prisma - Sorting by function applied on a column

Question

I want to use a function in my orderBy clause. To my great surprise I can't find a way in the official documentation to do this without executing raw SQL in Prisma. This is similar to what Python or SQL offer.

This is the query I have now:

photos.findMany({
  where: { createdAt: { gte: from, lte: to } },
  take: 1,
  orderBy: { createdAt: 'desc' },
})

This query is fine, however now it fetches just the most recent photos.

I would like to upgrade my query to return the photos that are the closest to a given date, something like this:

const dayTime = new Date('2025-01-01T00:00:00Z')
photos.findMany({
  where: { createdAt: { gte: from, lte: to } },
  take: 1,
  orderBy: { abs(createdAt-dayTime): 'desc' },
})

Is this still not supported by Prisma or am I missing something?

sweethuman · Accepted Answer · 2025-11-02 22:39:33Z

This is not supported by Prisma. Please see orderBy docs. Your best option of doing this is using Raw Queries or using the experimental TypedSQL api.

They both have their drawbacks.

Raw Queries don't offer any type safety and you have to be careful to avoid SQL injection.

TypedSQL makes your life easier by providing typings and some type safety but when generating them Prisma requires an active connection to a database that has all the migrations applied.

Specifically for your example, you could rethink your query to keep it all in javascript. You could sort on the client or get the closest photo before, get the closest after and then compare to find the absolute closest one.

const dayTime = new Date('2025-01-01T00:00:00Z');

const photoBefore = await prisma.photos.findFirst({
  where: {
    createdAt: { gte: from, lte: dayTime },
  },
  orderBy: { createdAt: 'desc' },
});

const photoAfter = await prisma.photos.findFirst({
  where: {
    createdAt: { gt: dayTime, lte: to },
  },
  orderBy: { createdAt: 'asc' },
});

const photos = [photoBefore, photoAfter]
  .filter(Boolean)
  .sort((a, b) => {
    const diffA = Math.abs(a!.createdAt.getTime() - dayTime.getTime());
    const diffB = Math.abs(b!.createdAt.getTime() - dayTime.getTime());
    return diffA - diffB;
  });

const closestPhoto = photos[0];

Collectives™ on Stack Overflow

Prisma - Sorting by function applied on a column

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related