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

Kysely•16mo ago•

5 replies

Billy Swedish

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

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();

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

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

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

Similar Threads

Similar Threads

Similar Threads