Drizzle Team•9mo ago

Need Help: `ilike` Function Not Returning Expected Results in Drizzle ORM

Hello Drizzle ORM Community! I'm currently working with Drizzle ORM in a TypeScript environment using PostgreSQL (specifically, Neon PostgreSQL) and I've encountered a small issue with querying user data using the ilike function for case-insensitive matching. I'm attempting to query tbl_users based on partial user names, but I'm not getting the expected results. Here's the snippet of my code where I implement the query:

const usersList = await db.query.tbl_users.findMany({
  where: ilike(tbl_users.full_name, input.query), // todo: need to fix.
  columns: {
    full_name: true,
    email: true,
  },
});

const usersList = await db.query.tbl_users.findMany({
  where: ilike(tbl_users.full_name, input.query), // todo: need to fix.
  columns: {
    full_name: true,
    email: true,
  },
});

I pass the query parameter as jack expecting to retrieve users with names like Jackson Kasi, but instead, I receive an empty array. According to the Drizzle ORM documentation, the ilike should work for PostgreSQL like so:

import { ilike } from "drizzle-orm";
db.select().from(table).where(ilike(table.column, "%llo wor%"));

import { ilike } from "drizzle-orm";
db.select().from(table).where(ilike(table.column, "%llo wor%"));

However, my current implementation doesn't seem to function as expected. Does anyone have experience with this function or can spot what I might be doing wrong? Any suggestions on how to properly use ilike with findMany or corrections to my approach would be greatly appreciated! Thank you in advance for your help!

1 Reply

Jackson KasiOP•9mo ago

Hello Drizzle ORM Community! I’ve managed to resolve the issue with the ilike function not returning the expected results when querying user data in a TypeScript environment with PostgreSQL. The problem was that I needed to include wildcard characters % around the input string to correctly perform the case-insensitive partial search. Here's the updated code snippet that fixed the issue:

import { db, ilike } from "@repo/db";
import { z } from "zod";

import { createTRPCRouter, protectedProcedure } from "../trpc";
import { tbl_users } from "@repo/db/src/schema";

export const userEmailRouter = createTRPCRouter({
  getAll: protectedProcedure
    .input(z.object({ query: z.string().optional().default("") }))
    .query(async ({ ctx, input }) => {
      // Wrap the input query with '%' for partial matching
      const searchQuery = `%${input.query}%`; 

      const usersList = await db.query.tbl_users.findMany({
        where: ilike(tbl_users.full_name, searchQuery), // Correctly format the query
        columns: {
          full_name: true,
          email: true,
        },
      });

      console.log({ usersList: usersList || [] });

      return {
        success: true,
        message: "Successfully fetched users",
        data: { usersList: usersList || [] },
      };
    }),
});

import { db, ilike } from "@repo/db";
import { z } from "zod";

import { createTRPCRouter, protectedProcedure } from "../trpc";
import { tbl_users } from "@repo/db/src/schema";

export const userEmailRouter = createTRPCRouter({
  getAll: protectedProcedure
    .input(z.object({ query: z.string().optional().default("") }))
    .query(async ({ ctx, input }) => {
      // Wrap the input query with '%' for partial matching
      const searchQuery = `%${input.query}%`; 

      const usersList = await db.query.tbl_users.findMany({
        where: ilike(tbl_users.full_name, searchQuery), // Correctly format the query
        columns: {
          full_name: true,
          email: true,
        },
      });

      console.log({ usersList: usersList || [] });

      return {
        success: true,
        message: "Successfully fetched users",
        data: { usersList: usersList || [] },
      };
    }),
});

Key Changes 1. Wildcard Usage: I added % symbols around the input.query to create searchQuery. This modification tells PostgreSQL to look for any records where the full_name contains the provided string, regardless of its position. 2. Tested Query: I tested this by passing 'jack' as the query parameter and successfully retrieved users with names like 'Jackson Kasi', confirming that the partial and case-insensitive search was working as expected. This solution adheres to the ilike function usage as described in the Drizzle ORM documentation but applies it within the context of the findMany method, ensuring that it correctly interprets the partial search strings. Thanks to everyone who took the time to look into this, and I hope this solution helps anyone else facing a similar issue!

Gaming

Programming

Need Help: `ilike` Function Not Returning Expected Results in Drizzle ORM