How to rewrite the sql statement using prisma syntax?

[zwl1619]

How to rewrite the sql statement using prisma syntax?
I read the document, but I couldn't find the appropriate content,because it has ${%${query}%}.

    const invoices = await sql`
      SELECT
        invoices.id,
        invoices.amount,
        invoices.date,
        invoices.status,
        customers.name,
        customers.email,
        customers.image_url
      FROM invoices
      JOIN customers ON invoices.customer_id = customers.id
      WHERE
        customers.name ILIKE ${`%${query}%`} OR
        customers.email ILIKE ${`%${query}%`} OR
        invoices.amount::text ILIKE ${`%${query}%`} OR
        invoices.date::text ILIKE ${`%${query}%`} OR
        invoices.status ILIKE ${`%${query}%`}
      ORDER BY invoices.date DESC
      LIMIT ${ITEMS_PER_PAGE} OFFSET ${offset}
    `;

[yudin-s]

Most of that query maps to Prisma Client, but the amount::text ILIKE ... and date::text ILIKE ... parts are the awkward bits. Prisma can filter strings case-insensitively, but it does not really have an equivalent for casting arbitrary columns to text inside a where filter.

The Prisma shape would be something like this:

const search = req.query.search?.trim()

const payments = await prisma.payment.findMany({
  where: search
    ? {
        OR: [
          { transactionId: { contains: search, mode: "insensitive" } },
          { status: { contains: search, mode: "insensitive" } },
          { method: { contains: search, mode: "insensitive" } },
          {
            customer: {
              OR: [
                { firstName: { contains: search, mode: "insensitive" } },
                { lastName: { contains: search, mode: "insensitive" } },
                { email: { contains: search, mode: "insensitive" } },
              ],
            },
          },
        ],
      }
    : undefined,
  orderBy: { date: "desc" },
  skip,
  take: pageSize,
  select: {
    id: true,
    transactionId: true,
    amount: true,
    status: true,
    method: true,
    date: true,
    customer: {
      select: { firstName: true, lastName: true, email: true },
    },
  },
})

For amount, I would parse the search term and add an exact/range numeric filter only when it is actually a number. For date, same idea: parse the input and search a date range instead of comparing the formatted date string.

If you specifically need the PostgreSQL behavior of casting amount and date to text and doing ILIKE, $queryRaw is still the right tool for that part. Prisma Client is better when the filters stay typed.