DT
Join ServerDrizzle Team
help
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:
the full code of my remix loader:
the non expected weird thing is that only searching by the
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() });
};
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 onEnable logging in drizzle and view the raw sql query that it executes, then you can see whether or not its correct
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