Kysely•3mo ago

ilike with reference

Hey there, I'm looking to perform the following SQL operation:

WHERE groups.group_name ILIKE '%' || users.first_name || '%'

WHERE groups.group_name ILIKE '%' || users.first_name || '%'

but unfortunately I'm having trouble doing this in Kysely. Here is my current attempt:

eb(
  "groups.group_name",
  "ilike",
  eb.fn<string>("concat", [
    eb.val("%"),
    eb.ref("users.first_name"),
    eb.val("%"),
  ]),
)

eb(
  "groups.group_name",
  "ilike",
  eb.fn<string>("concat", [
    eb.val("%"),
    eb.ref("users.first_name"),
    eb.val("%"),
  ]),
)

which will compile fine, but unfortunately pg throws a error: could not determine data type of parameter $1 error when running. I printed the sql query compiled by Kysely and got:

sql: 'where ("groups"."group_name" ilike concat($1, "users"."first_name", $2) ',
parameters: [ '%', '%' ]

sql: 'where ("groups"."group_name" ilike concat($1, "users"."first_name", $2) ',
parameters: [ '%', '%' ]

which Pg can't seem to handle. Does anyone have any suggestions? I recognize that this problem might not necessarily be with Kysely.

Solution:

Using

sql`'%'`,

sql`'%'`,

Seemed to do the trick, instead of eb.val("%"). Any other ways to make this more ergonomic?...

Jump to solution

2 Replies

OverHashOP•3mo ago

Oh actually I think this is Postgres itself throwing the error? Not pg. I guess I need a way to embed % directly into my query?

Solution

OverHash•3mo ago

Using

sql`'%'`,

sql`'%'`,

Seemed to do the trick, instead of eb.val("%"). Any other ways to make this more ergonomic?

Gaming

Programming

ilike with reference