upper
Question: Number of affected rows after calling Update
How to get number of affected rows after calling Update(...)?
Consider this code:
cond := db.Cond{"id": p.ID, "updated_at": oldUpdateAt}
found := products.Find(cond)
if found != nil {
err := found.Update(p)
if err != nil {
return err
}
// (A) how many rows got updated? any at all?
}
The generated SQL update command has a where clause that may or may not get met. How to find out if any rows got actually updated at point (A)?
Hmm, good question. There's currently nothing in place that gives you this when using a result set, you could count matches before updating (within a transaction) but that's not always the number of affected rows:
import "upper.io/db.v2/lib/sqlbuilder"
err := sess.Tx(func(sess sqlbuilder.Tx) error {
col := sess.Collection("products")
res := col.Find(...)
if c, err := res.Count(); err != nil {
return err
}
if c >0 {
return res.Update(newItem)
} else {
// No matches, do something else.
}
return nil
})
In this specific app (my current project), another round of going to database (with same constraints) solves the problem - though not desirable - and it is needed AFTER the update - not before.
IMHO It seems as an essential feature for any app substantially complicated and I would like to see it added, but it might be just me.
I disagree. It's an expensive operation. You want a count of output, use len()
Data sets are huge, use a cursor.
On Sep 3, 2016, at 7:30 PM, Kaveh Shahbazian [email protected] wrote:
In this specific app (my current project), another round of going to database (with same constraints) solves the problem - though not desirable - and it is needed AFTER the update - not before.
IMHO It seems as an essential feature for any app substantially complicated and I would like to see it added, but it might be just me.
— You are receiving this because you are subscribed to this thread. Reply to this email directly, view it on GitHub, or mute the thread.
When we execute a command like this in a database:
UPDATE "product"
SET "created_at" = $1,
"depth" = $2,
"description" = $3,
"height" = $4,
"id" = $5,
"meta" = $6,
"price" = $7,
"product_class" = $8,
"product_name" = $9,
"tags" = $10,
"updated_at" = $11,
"weight" = $12,
"width" = $13
WHERE ("id" = $14
AND "updated_at" = $15)
The database returns a number as the result. That's the number I want to retrieve. It's just standard SQL.
It has nothing to do with len or cursor. Am I missing something?
I think this is possible with database/sql's RowsAffected() without any additional operation, the problem here is that, as far as I know, this depends on the database, MongoDB doesn't have a feature like this and the SQL databases that have it exhibit inconsistent behaviour, for that reason we won't be adding it to Find().Update() (we're making an effort to make the Find() syntax result-set oriented and as agnostic as we can, this includes Find().Update() as it works on the result set provided by Find()).
However, upper-db also has a place for database particularities, if you need something from the underlying SQL driver and Find() is not enough, then you should use the SQL builder:
res, err := sess.Update("product").Set(itemStruct).Where(conds).Exec()
...
res.RowsAffected()
Can you try and see if this answers your question?
By the way, in your example:
cond := db.Cond{"id": p.ID, "updated_at": oldUpdateAt}
found := products.Find(cond)
if found != nil {
err := found.Update(p)
if err != nil {
return err
}
// (A) how many rows got updated? any at all?
}
You can remove the found != nil check, as Find() always returns a result set.
Thanks!
Most databases we use in our day to day tasks are RDBMSes and the only exception for upper.io/db is MongoDB - not the other four.
Yes; I think RowsAffected does what explained above. Yet IMHO exposing that (or the underlying res) somehow would be handy. As I understand the session (sess) object should not be used concurrently. So we could have something like sess.LastResult() (just thinking).
Thanks again and for now I'll use the session object directly.
Kinda similar to #175 (for SELECT statements).
Having number of affected rows could be very helpful, especially when you run something like
UPDATE users SET age=20 WHERE age <= 0. There's no other way to get the result, except for the RETURNING clause in Postgres.
@xiam after some time using the sql builder, I could say, I can live with that; though it's not as nice as the collection based DSL. I think you should point out the RowsAffected in Update section of the documents on the site.
BTW Are sessions concurrent safe?
Hello @dc0d, sorry for the delay! I think you're right, we should explain about RowsAffected, I'll keep this issue open until providing docs for that. Sessions are concurrent safe, you usually need only one long-lived session per database.
