Kysely•8mo ago

Using raw SQL with `or` where

I have been using the raw SQL template tag to work with certain JSON columns and wanted to add a orWhere clause using it. Here is a simple example of what I am trying to achieve.

const result = await db
  .selectFrom('users')
  .where((eb) => {
    return eb.or([
      sql`address ->> 'street' = ${street}`,
      sql`address ->> 'street' = ${street1}`
    ])
  })
  .executeTakeFirst()

const result = await db
  .selectFrom('users')
  .where((eb) => {
    return eb.or([
      sql`address ->> 'street' = ${street}`,
      sql`address ->> 'street' = ${street1}`
    ])
  })
  .executeTakeFirst()

I can pass sql literal to the where clause directly, but in this case I want to write or where

Solution:

And you're right, eb.or expects a list of Expression<SqlBool> where SqlBool is boolean | 0 | 1

Jump to solution

4 Replies

virkOP•8mo ago

Okay, I think I got it after reading the expressions doc in and out https://kysely.dev/docs/recipes/expressions

Expressions | Kysely

An Expression is the basic type-safe query building block in Kysely. Pretty much all methods accept expressions as inputs. Most internal classes like SelectQueryBuilder and RawBuilder (the return value of the sql tag) are expressions themselves.

virkOP•8mo ago

But still want to verify if I am correct

  .where((eb) => {
    return eb.or([
      sql<boolean>`address ->> 'street' = ${eb.val(street)}`,
      sql<boolean>`address ->> 'street' = ${eb.val(street1)}`
    ])
  })

  .where((eb) => {
    return eb.or([
      sql<boolean>`address ->> 'street' = ${eb.val(street)}`,
      sql<boolean>`address ->> 'street' = ${eb.val(street1)}`
    ])
  })

The trick is to set the output type of RawBuilder to a boolean. RawBuilder extends Expression, so it is acceptable by eb.or

Igal•8mo ago

Hey 👋 ->> should be supported using eb.ref('address', '->>').key('street') I owe the community a recipe on JSON references

Solution

Igal•8mo ago

And you're right, eb.or expects a list of Expression<SqlBool> where SqlBool is boolean | 0 | 1

Gaming

Programming

Using raw SQL with `or` where