Execute update query with increment operation on field

[jdevelop]

Hi!

Is it possible to avoid using of native SQL for query like:

> update tableA set someField=someField+1 where id=:refid ?

Also, in order to make such change seen in scope of current thread, I
have to use

> ormConf.transactionManager.get.cache.invalidate()

which is ugly. May be there is another solution?

Thanks!

[Boris Okunskiy]

Greetings,

Hi!

Is it possible to avoid using of native SQL for query like:

update tableA set someField=someField+1 where id=:refid ?

Unfortunately, you cannot use arbitrary expression in the SET close of `ru.circumflex.orm.Update`. 

Technically, you can implement your own query class by mixing in `DMLQuery` with `SearchQuery`

(see Update class for the reference https://github.com/inca/circumflex/blob/master/circumflex-orm/src/main/scala/query.scala#L377) to achieve the desired functionality. However, this approach will be bound to native SQL, too, since there is no way you can take the advantage of existing dialect methods. You can stick to this approach only if you have lots of such queries throughout your application, so that, in case of switching the database vendor, you would have to revisit only this place, instead of every specific query.

Also, in order to make such change seen in scope of current thread, I
have to use

ormConf.transactionManager.get.cache.invalidate()

Well, this is a known problem. As long as you do not use the active-record-style methods to work with your records (e.g. `r.save()` or `r.INSERT()` or `r.UPDATE()` or `r.DELETE_!()`, you would have to maintain the cache manually.  Can't figure a way to "know" at the ORM level about specific records (or maybe even tables) which are affected by custom queries.

I can only suggest you using a shortcut method `tx` instead of `ormConf.transactionManager.get`. It can be found in the package object `orm`.

Best regards,

Boris Okunskiy