How to write a structured update using max()

[jubishop]

I'm trying to use all your amazing libraries rather than write raw sql, but I'm stumped on this one. here's what works in raw sql:

// Add currently unqueued item to the bottom of the queue
try await appDB.db.write { db in
  try db.execute(
    sql: """
      update episode 
        set queueOrder = (SELECT MAX(queueOrder) FROM episode) + 1 
        where id = :id  
      """,
    arguments: ["id": episode.id]
  )
}

How would you write this without dropping to raw sql (if possible)?

thanks again for this great library!

[jubishop]

side note: counting all the records with queueOrder not set to nil (and then +1) would also work here.

[jubishop]

ok I've come up with this as my best attempt thus far.

    try await appDB.db.write { db in
      let max = try Episode.select(max(Column("queueOrder")), as: Int.self).fetchOne(db)
      episode.queueOrder = (max ?? 0) + 1
      try episode.update(db, columns: ["queueOrder"])
    }

[jubishop]

or!

      let max = try Episode.select(max(Column("queueOrder")), as: Int.self)
        .fetchOne(db)
      try Episode
        .filter(id: episode.id)
        .updateAll(db, Column("queueOrder").set(to: (max ?? 0) + 1))

[jubishop]

I think I’ve probably answered my own question here :)

I also need to figure out the reverse: when I want to insert an episode at the top of the queue (giving it the queueOrder value of 1) how can I update all other records that have a queueOrder set at all to being +1 of what they are now…

[groue]

Hello @jubishop,

Performing two requests looks fine to me 👍

I also need to figure out the reverse: when I want to insert an episode at the top of the queue (giving it the queueOrder value of 1) how can I update all other records that have a queueOrder set at all to being +1 of what they are now…

try Episode.updateAll(db, Column("queueOrder").set(to: Column("queueOrder") + 1))

// Equivalent
try Episode.updateAll(db, Column("queueOrder") += 1)