Update a DB record with calculated values

General
1

Turns out, I don’t know how to write a basic update statement with updateData(). :frowning:
I want something along the lines of

UPDATE myObject
SET column = column + 1,
    column2 = column3 * column4
WHERE id = :myId

Its nothing exotic or especially unusual, so it surely must be possible. But how do I do this with updateData(), please?

Thank you!

2

Right now, we do this in plain sql with sqlrunner. We’ve no provision for updating values with dynamic sql.

To be super brown nose developer, you’d do something like:

property name="sqlRunner" inject="sqlRunner";
//...
var myObjDao = $getPresideObject( "my_obj" );
var dbadapter = myObjDao.getDbAdapter();
var tableName = dbadapter.escapeEntity( myObjDao.getTableName() );
var sql       = "update #tableName# set blah = blah where id = :id"; // also could do the escapeEntity on column names here)

sqlRunner.runSql(
     dsn        = myObjDao.getDsn()
   , sql        = sql
   , params     = [ { name="id", value=blahId, type="cf_sql_varchar" } ] // or whatever type you have
   , returnType = "info"
);

There is some logic that does this sort of thing in the Preside core for email stats collection. In there, we cache the produced SQL with all the entity escaping so that only runs once. Nice and efficient.

Of course, you don’t get any of the core interceptors doing it this way but that’s probably a good thing for these type of queries. You may also need/want to ensure related caches are cleared (presideObjectService.clearRelatedCaches( ... ))

3

Note also that this will bypass any of Preside’s record versioning, which depends on knowing what the values are going to be in advance of updating a record…

4

@dominic.watson @seb Thank you for the replies. Unfortunately it was more or less what I had expected. :wink: So basically, if I want versioning, I’d have to load the record, change the values and then update the record. Which, of course isn’t thread safe (and very inefficient if you want to update multiple records).

5

Yes and no, you could manually do the versioning yourself using a similar query.

It would be the same issue if/when we were to have an official api to do this (with versioning I mean).

And do you defo want a new version record for a change to a field that gets incremented like this? Or just get the latest version record up to date with the primary record. So:

update my_obj set counter = counter + 1 where some_field = 'test';

update _version_my_obj v 
inner join my_obj m on m.id = v.id
set v.counter = m.counter
where v._version_is_latest = true
and some_field = 'test'
6

Thanks Dom, good points. I’ll have to discuss this with the client, but, I think in his special case he will want versioning even with an update like this.

7

Again you can do similar, but with an insert (and maybe able to use the version service directly if there are appropriate methods).

If its a statistic type field then another option is separate non versioned table. Versioning stats makes little sense generally I think. Curious if it is a different use case and would love to know what that is.

8

Sorry for answering late… I definitely agree, versioning stats is not something I would use.

The problem with the client was that he confused versioning with logging. In the end we could convince him that versioning doesn’t make sense here. :wink:

1 Like