Kysely•16mo ago

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") ?

3 Replies

koskimas•16mo 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•16mo ago

Message Not Public

koskimas•16mo 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

Gaming

Programming

how to sort `db.updateTable` returning values?