Is it possible in prisma to filter by string length?
For example, I would like to receive records whose name is 10 characters long.
Is it possible in prisma to filter by string length?
For example, I would like to receive records whose name is 10 characters long.
You would need to use Raw Query to filter the records by string length.
Here's an example of how you could achieve it. Considering this model
generator client {
provider = "prisma-client-js"
}
datasource db {
provider = "postgresql"
url = env("DATABASE_URL")
}
model Employee {
employeeId Int @id @default(autoincrement())
first_name String
hire_date DateTime
}
This query would filter out the records which have first_name length less than 5 characters
import { Prisma, PrismaClient } from '@prisma/client';
const prisma = new PrismaClient({
log: ['query', 'info', 'warn'],
});
// A `main` function so that you can use async/await
async function main() {
const result =
await prisma.$queryRaw`SELECT * FROM "public"."Employee" WHERE LENGTH(first_name) <= 5`;
console.log(result);
}
main()
.catch((e) => {
throw e;
})
.finally(async () => {
await prisma.$disconnect();
});
Here's the output
prisma:info Starting a postgresql pool with 13 connections.
prisma:query SELECT * FROM "public"."Employee" WHERE LENGTH(first_name) <= 5
[
{
employeeId: 1,
first_name: 'John',
hire_date: '2022-03-10T00:00:00+00:00'
}
]