Postgres multi-update with multiple `where` cases

Question

Excuse what seems like it could be a duplicate. I'm familiar with multiple updates in Postgres... but I can't seem to figure out a way around this one...

I have a photos table with the following columns: id (primary key), url, sort_order, and owner_user_id.

We would like to allow our interface to allow the user to reorder their existing photos in a collection view. In which case when a drag-reorder interaction is complete, I am able to send a POST body to our API with the following:

req.body.photos = [{id: 345, order: 1, id: 911, order: 2, ...<etc>}]

In which case I can turn around and run the following query in a loop per each item in the array.

photos.forEach(function (item) {
  db.runQuery('update photos set sort_order=$1 where id=$2 and owner_user_id=$3', [item.order, item.id, currentUserId])
})

In general, it's generally frowned upon to run database queries inside loops, so if there's anyway this can be done with 1 query that would be fantastic.

Much thanks in advance.

note flagging as a dupe because this is mysql but ... stackoverflow.com/questions/40623132/… — e4c5
– e4c5, Commented Dec 21, 2016 at 3:03

Hambone · Accepted Answer · 2016-12-21 04:28:22Z

1

Running a select query inside of a loop is definitely questionable, but I don't think multiple updates is necessarily frowned upon if the data you are updating doesn't natively reside on the database. To do these as separate transactions, however, might be.

My recommendation would be to wrap all known updates in a single transaction. This is not only kinder to the database (compile once, execute many, commit once), but this is an ACID approach to what I believe you are trying to do. If, for some reason, one of your updates fails, they will all fail. This prevents you from having two photos with an order of "1."

I didn't recognize your language, but here is an example of what this might look like in C#:

NpgSqlConnection conn = new NpgSqlConnection(connectionString);
conn.Open();

NpgSqlTransaction trans = conn.BeginTransaction();
NpgSqlCommand cmd = new NpqSqlCommand("update photos set sort_order=:SORT where id=:ID",
    conn, trans);
cmd.Parameters.Add(new NpgSqlParameter("SORT", DbType.Integer));
cmd.Parameters.Add(new NpgSqlParameter("ID", DbType.Integer));

foreach (var photo in photos)
{
   cmd.Parameters[0].Value = photo.SortOrder;
   cmd.Parameters[1].Value = photo.Id;
   cmd.ExecuteNonQuery();
}

trans.Commit();

I think in Perl, for example, it would be even simpler -- turn off DBI AutoCommit and commit after the inserts.

CAVEAT: Of course, add error trapping -- I was just illustrating what it might look like.

Also, I changed you update SQL. If "Id" is the primary key, I don't think you need the additional owner_user_id=$3 clause to make it work.

answered Dec 21, 2016 at 4:28

Hambone

16.5k8 gold badges54 silver badges79 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

ded Over a year ago

very cool. thanks! i'm familiar with "transactions" and didn't occur to me now to use them for this scenario. as a matter regarding the owner_user_id - it's there as a preventative security measure... ensuring only the authed user is the one allowed to sort the given id's.

Hambone Over a year ago

Great clarification -- so it's not to specify which row to update, rather to prevent a row from updating under certain conditions. Makes sense now. What language was that (one I don't know, obviously)?

ded Over a year ago

i'm working with node's pg module for Postgres. See github.com/brianc/node-postgres

Collectives™ on Stack Overflow

Postgres multi-update with multiple `where` cases

1 Answer 1

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related