K
Kyselyvirk

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
virk
virk•37d 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.
virk
virk•37d 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
Igal•37d ago
Hey 👋 ->> should be supported using eb.ref('address', '->>').key('street') I owe the community a recipe on JSON references
Solution
Igal
Igal•37d ago
And you're right, eb.or expects a list of Expression<SqlBool> where SqlBool is boolean | 0 | 1
Want results from more Discord servers?
Add your server
More Posts
Custom Plugin to transform Alias.* to "Alias.Column1", "Alias.Column2", etc.Hi everyone, in the previous question, I asked how to transform the following syntax into a new one.can kysely be extended with custom types within the databasehi. i would like to extend my database with custom types (a date type for sqlite) so that it is easinewbie need help with json_build_objectHi! Just starting with kysely and encountered problem I cant solve. I want to build raw query with kSolved: Exporting query builder classesI saw this PR https://github.com/kysely-org/kysely/pull/763 where you guys have decided not to exporbuilding kysely makes the package size very largehi. i'd be more than happy to figure this out because i like kysely a lot more than drizzle. the thiKysely setup in monolith APIHello, we have a basic http server with a basic router and a PostgreSQL database. We're wondering whIs it possible to get the total count while fetching rows in a single query?I have a query which looks like this: ```typescript const { items, count } = await db .selectFrom(Separating results of join into objects of each typeHey guys, I'm curious if anyone knows a way to unmerge the results of a join into objects of both tySnippet compilationHi, is it somehow possible to get only part of the compiled code to use it as snippet in ORM? Or vicCreating an 'enum' type columnHi all, Trying to re-create this MySQL in Kysely ``` ... CREATE TABLE ...( ... members ENUM('femalError when destructuring QueryCreator in withRecursiveI'm new to Kysely and attempting to migrate a Next app from prisma to kysely. I've generated types, Is `sql.join` the best way to concatenate sql templates?I have some business logic that incrementally builds up a sql WHERE query as a string. It looks likeMultiple calls to values in insert statement?I just had a production bug where I was calling `.values` multiple times on a query object. I've fix0.27.3 is out. Didn't have time to write0.27.3 is out. Didn't have time to write release notes yet since it was a hotfix to support typescriusing pgvector with KyselyFollowing the example here: https://github.com/pgvector/pgvector-node?darkschemeovr=1#kysely But I Static/reusable custom window functionsHello, I'm trying to buld a properly typed `array_agg` window function and would like to reuse the e