Drizzle Team•2y ago

weird ilike postgres behavior

hi, I have a very weird issue when trying to filter using ilike in postgres, both my local instance which is running in docker and fly.io postgres, which suggests this is a drizzle issue, here is the schema of my table:

export const points = pgTable("points", {
  ...defaultCols,
  name: text("name").notNull(),
  grouping: text("grouping").notNull(),
  statusId: integer("status_id")
    .references(() => statuses.id)
    .notNull(),
  latitude: real("latitude").notNull(),
  longitude: real("longitude").notNull(),
  projectId: integer("project_id")
    .references(() => projects.id)
    .notNull(),
  meta: json("meta"),
});

export const points = pgTable("points", {
  ...defaultCols,
  name: text("name").notNull(),
  grouping: text("grouping").notNull(),
  statusId: integer("status_id")
    .references(() => statuses.id)
    .notNull(),
  latitude: real("latitude").notNull(),
  longitude: real("longitude").notNull(),
  projectId: integer("project_id")
    .references(() => projects.id)
    .notNull(),
  meta: json("meta"),
});

the full code of my remix loader:

export const loader = async (args: LoaderArgs) => {
  const { request, params } = args;

  const { searchParams } = new URL(request.url);

  const { phrase, projectId } = paramsSchema.parse(searchParams);
  const like = `%${phrase}%`;

  const query = db
    .select()
    .from(points)
    .where(
      and(
        eq(points.projectId, projectId),
        phrase
          ? or(ilike(points.grouping, like), ilike(points.name, like))
          : undefined,
      ),
    )
    .limit(500);

  const results = await query.execute();

  return json({ results, sql: query.toSQL() });
};

export const loader = async (args: LoaderArgs) => {
  const { request, params } = args;

  const { searchParams } = new URL(request.url);

  const { phrase, projectId } = paramsSchema.parse(searchParams);
  const like = `%${phrase}%`;

  const query = db
    .select()
    .from(points)
    .where(
      and(
        eq(points.projectId, projectId),
        phrase
          ? or(ilike(points.grouping, like), ilike(points.name, like))
          : undefined,
      ),
    )
    .limit(500);

  const results = await query.execute();

  return json({ results, sql: query.toSQL() });
};

the non expected weird thing is that only searching by the name column works, the grouping is ignored, if I exclude grouping from where clause, like so: phrase ? ilike(points.name, like) : undefined,, search by name is working, but, if I do the same with the grouping column phrase ? ilike(points.grouping, like) : undefined, no results are matched, even though .toSQL output works in the db explorer console, what could be the issue? I attached some screens of the feature I'm working on

2 Replies

volks•2y ago

Enable logging in drizzle and view the raw sql query that it executes, then you can see whether or not its correct

arekmazOP•2y ago

I have and the issue is, the sql is correct, but when I run it in db tool it gives a different result than drizzle never mind, I just found out that it's actually correct, it's react-select that doesn't display the results, thanks for help

Gaming

Programming

weird ilike postgres behavior