Is it possible in prisma to filter by string length?-CodePudding

Is it possible in prisma to filter by string length?

For example, I would like to receive records whose name is 10 characters long.

CodePudding user response：

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'
  }
]