K
Kyselybun

how to sort `db.updateTable` returning values?

await db
    .updateTable("users")
    .where("id", "in", ({ selectFrom }) =>
      selectFrom("users")
        .where("users.status", "=", "PREPARED")
        .where("profileId", "=", profileId)
        .select("id")
        .limit(100),
    )
    .set({ status: "SENT" })
    .returningAll()
    .orderBy("id", "asc") // why does this not exist?
    .execute()
await db
    .updateTable("users")
    .where("id", "in", ({ selectFrom }) =>
      selectFrom("users")
        .where("users.status", "=", "PREPARED")
        .where("profileId", "=", profileId)
        .select("id")
        .limit(100),
    )
    .set({ status: "SENT" })
    .returningAll()
    .orderBy("id", "asc") // why does this not exist?
    .execute()
why cant i .orderBy("id", "asc") ?
koskimas
koskimas•262d ago
You can't add an order by clause to an update query in Postgres: https://www.postgresql.org/docs/15/sql-update.html What you can do is this: 
const result = await db
  .with('updatedUsers', db => db
    .updateTable("users")
    .where("id", "in", ({ selectFrom }) =>
      selectFrom("users")
        .where("users.status", "=", "PREPARED")
        .where("profileId", "=", profileId)
        .select("id")
        .limit(100),
    )
    .set({ status: "SENT" })
    .returningAll()
  )
  .selectFrom('updatedUsers')
  .selectAll()
  .orderBy('id', 'asc')
  .execute()
const result = await db
  .with('updatedUsers', db => db
    .updateTable("users")
    .where("id", "in", ({ selectFrom }) =>
      selectFrom("users")
        .where("users.status", "=", "PREPARED")
        .where("profileId", "=", profileId)
        .select("id")
        .limit(100),
    )
    .set({ status: "SENT" })
    .returningAll()
  )
  .selectFrom('updatedUsers')
  .selectAll()
  .orderBy('id', 'asc')
  .execute()
Unknown User
Unknown User•262d ago
Message Not Public
Sign In & Join Server To View
koskimas
koskimas•262d ago
No. It's doing a with statement. Kysely is a WYSIWYG query builder. There's never anything done "under the hood" in that sense. Kysely also always executes one single query when you call execute https://kyse.link/?p=s&i=cD5mUe2pO8zUuZnu3Qgb
Want results from more Discord servers?
Add your server
More Posts
How can I write a query that does multiple insertions at once?I have an array of Ids that I want to assign to a single id in a "bridging" table for a many to manyHow can I write a count query?Do I have to use raw SQL?How to "generate" or "store" a query result type?Hello, I was wondering how can I extract or store the type resulting from a query so that I ca use sMigrations in transaction and self managementHi, I have a questions regarding migrations: - Is it possible to run all migrations in one transactiRunning a function during migrationI am looking to use Kysely with TimescaleDB, which is an extension of Postgres to support timeseriesUpdating JSON object value in columnHello, Im trying to wrap my head around how to update a JSON column were I want to update the value npm downloadsWe just broke 100k weekly downloads on npm!!! #adoption 📈I think the fast growth lately has beenI think the fast growth lately has been due to all the work @Igal has been doing in the social mediaNoice7000 ⭐ in github btw 😻Ad-Hoc select from table not in Kysely typesHey, we are dynamically generating our KyselyDB types using the `kysely-codgen` cli, and want to knoConvert JSON array result into JS arrayHello, I am running an SQL query that returns data in this format since it is a PG JSON array: ``` {Destroying connection necessary?Hello! If we are using pooling via pgbouncer and using kysely from our edge functions, is it necessahow to insert an array of stringshey guys, I wanted to know how to store an array of strings with kysely, I created my table using mTypescript Error when creating MySQL db poolHi Everyone, I'm getting the following error when creating a database: ``` kysely Type '() => Promi