Kysely•2mo ago

Dynamic expression builder using raw SQL for WHERE column LIKE string% OR

Hello! We switched over to using Kysely in our project and it's going great so far! But I found a bit of a hitch with writing an old dynamic WHERE LIKE query. I need to get the results of file paths stored in our system that start with a directory path. Previously in pure SQL we would generate an expression like this:

SELECT * FROM tableName WHERE 
file_path LIKE dirPath% OR 
file_path LIKE otherDirPath% OR... etc.

SELECT * FROM tableName WHERE 
file_path LIKE dirPath% OR 
file_path LIKE otherDirPath% OR... etc.

I have tried with Kysely and it looks something like this which works just fine:

const result = await db
.selectFrom(tableName)
.selectAll()
.where((eb) => {
    return eb.or(
      dirPaths.map((dir) =>
      sql<boolean>`file_path LIKE ${dir + '%'}`))
}).execute();

const result = await db
.selectFrom(tableName)
.selectAll()
.where((eb) => {
    return eb.or(
      dirPaths.map((dir) =>
      sql<boolean>`file_path LIKE ${dir + '%'}`))
}).execute();

My question is though... originally I tried to generate an sql expression like the following:

const condition = dirPaths
  .map((dir) => sql`${sql.identifier(['file_path'])} LIKE ${dir + '%'} COLLATE NOCASE`)
  .reduce((acc, condition) => sql`${acc} OR ${condition}`);

db.selectFrom(tableName).selectAll().where(condition).execute();

const condition = dirPaths
  .map((dir) => sql`${sql.identifier(['file_path'])} LIKE ${dir + '%'} COLLATE NOCASE`)
  .reduce((acc, condition) => sql`${acc} OR ${condition}`);

db.selectFrom(tableName).selectAll().where(condition).execute();

I got an error of the following: Argument of type RawBuilder<unknown> is not assignable to parameter of type. Could you explain this? If I can get an sql template string set up it would make my life much easier moving forward knowing how to do this properly. Please let me know!

Solution:

@Igal that works great. Leaving the full solution here for others who come looking

const condition = dirPaths.map((dir) => 
sql<boolean>`file_path LIKE ${dir + '%'}`)
.reduce((acc, condition) => sql<boolean>`${acc} OR ${condition}`);

const condition = dirPaths.map((dir) => 
sql<boolean>`file_path LIKE ${dir + '%'}`)
.reduce((acc, condition) => sql<boolean>`${acc} OR ${condition}`);

...

Jump to solution

3 Replies

Igal•2mo ago

Hey 👋 that where variant expects an expression that returns SqlBool (boolean | 1 | 0). You can pass a return type to sql template tag, e.g.:

sql<SqlBool>`...`
sql<boolean>`...`

sql<SqlBool>`...`
sql<boolean>`...`

Billy SwedishOP•2mo ago

Ok, I will give that a test and let you know. Thanks for ther quick response!

Solution

Billy Swedish•2mo ago

@Igal that works great. Leaving the full solution here for others who come looking

const condition = dirPaths.map((dir) => 
sql<boolean>`file_path LIKE ${dir + '%'}`)
.reduce((acc, condition) => sql<boolean>`${acc} OR ${condition}`);

const condition = dirPaths.map((dir) => 
sql<boolean>`file_path LIKE ${dir + '%'}`)
.reduce((acc, condition) => sql<boolean>`${acc} OR ${condition}`);

Gaming

Programming

Dynamic expression builder using raw SQL for WHERE column LIKE string% OR