AlexErrant Posts - Answer Overflow

AlexErrant

Explore posts from servers

CDCloudflare Developers SSolidJS KKysely

•Created by AlexErrant on 3/11/2024 in #help

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 like I can't do the dumb thing and just string concat the pieces together like so:

let q = sql.raw` noteFtsFv.rowid ` + sql.raw` in `

let q = sql.raw` noteFtsFv.rowid ` + sql.raw` in `

So instead, I build a list of sql.raw and use sql.join with an empty string like so:

 const things = [
  sql.raw(" noteFtsFv.rowid "),
  sql.raw(" not "),
  sql.raw("IN (SELECT rowid FROM noteFtsFv WHERE noteFtsFv.fieldValues MATCH "),
  "some user input",
  sql.raw(" ) "),
]

let q = sql.join(things, sql``) as RawBuilder<SqlBool>

const person = await db
  .selectFrom('person')
  .select(['id', 'first_name'])
  .where(q)
  .executeTakeFirst()

 const things = [
  sql.raw(" noteFtsFv.rowid "),
  sql.raw(" not "),
  sql.raw("IN (SELECT rowid FROM noteFtsFv WHERE noteFtsFv.fieldValues MATCH "),
  "some user input",
  sql.raw(" ) "),
]

let q = sql.join(things, sql``) as RawBuilder<SqlBool>

const person = await db
  .selectFrom('person')
  .select(['id', 'first_name'])
  .where(q)
  .executeTakeFirst()

Is this the best way or am I missing a better solution 😅

8 replies

KKysely

•Created by AlexErrant on 2/16/2023 in #help

How to make a top level SELECT that isn't from a table?

I'm trying to combine two queries, SELECT COUNT(*) FROM A and SELECT COUNT(*) FROM B. SELECT (SELECT COUNT(*) FROM A), (SELECT COUNT(*) FROM B) does what I want. How can I convert this to Kysely? My real code also returns two scalars, so I could theoretically use a UNION ALL - however I'm not sure if the return order is guaranteed in MySQL. I'm open to other solutions that don't have a top level SELECT.

12 replies

Gaming

Programming