Sqlite_orm: `update_all` returning std::vector<UpdatedRecord> ?

[sivabudh]

It would be really nice if update_all (especially when used with where clause) returns the list of records that were updated. Otherwise, we need to query twice: get_all matchings then update_all.

What do you think?

[fnc12]

this is not necessary. In pure sqlite (or even SQL) to update some rows and get them you need to perform two queries. sqlite_orm aims to keep query logic like 'single function call per single query' (in most cases, not all). So to make it in sqlite_orm please use update_all then get_all. If you complain about same arguments in both function calls you can keep where argument in a variable/constant like this:

storage.update_all(set(c(&User::lastName) = "Hardey",
                       c(&User::typeId) = 2),
                   where(c(&User::id) < 10 and length(&User::firstName) > 5));
auto updatesUsers = storage.get_all<User>(where(c(&User::id) < 10 and length(&User::firstName) > 5));

replace with

auto w = where(c(&User::id) < 10 and length(&User::firstName) > 5);
storage.update_all(set(c(&User::lastName) = "Hardey",
                       c(&User::typeId) = 2),
                   w);
auto updatesUsers = storage.get_all<User>(w);

Update

Also you can call this functions within a single transaction for better performance:

storage.begin_transaction();
auto w = where(c(&User::id) < 10 and length(&User::firstName) > 5)
storage.update_all(set(c(&User::lastName) = "Hardey",
                       c(&User::typeId) = 2),
                   w);
auto updatesUsers = storage.get_all<User>(w);
storage.commit();

[sivabudh]

Thanks for the response! I cannot wait to try this tomorrow!

[sivabudh]

By the way, don't you need to get_all before you do an update? Otherwise, you won't get the list of User that matches your query w.

[fnc12]

Sometimes. It depends on your set fields and where fields. If they are the same or some fields are the same then updated set will change. Otherwise it won't. Why do you need updated rows? Maybe I can help you better if you tell me some more details about your sqlite_orm usage

[sivabudh]

My sqlite_orm usage is as follows:
I use the library to store transactions made in a gas station. I need to:

• bulk update all transactions where status == pending, and setting them to completed
• but I also need to notify all connected clients of the transactions that were _updated_

What do you think about this use case?

Thanks 😄

[fnc12]

you need to perform three queries:
1) get ids by status
2) set status by ids
3) get records by ids

[sivabudh]

OK, thanks for the suggestions.