I want to sort string column with Prisma not use $queryRaw

Question

I use MySQL.

A column called AGE contains numbers in the form of a string.

If I use prisma to sort, it's not 1-2-...-9

1-11-2-333-...-88-9 Sort like this.

I would like to know if there is any way to sort other than using $queryRaw.

Answer 1

Your model will look something like:

model Person {
  id Int @id
  name String
  age Int
}

The type definition Prisma generates for the orderBy field is:

export type PersonOrderByWithRelationInput = {
  id?: SortOrder
  name?: SortOrder
  age?: SortOrder
}

…and SortOrder is defined as:

export const SortOrder: {
  asc: 'asc',
  desc: 'desc'
};

export type SortOrder = (typeof SortOrder)[keyof typeof SortOrder]

Unfortunately this means it is not possible to get orderBy to do what you want here.

Your options here are:

• use queryRaw,
• sort the array returned by Prisma in JavaScript / TypeScript,
• use a materialized view.