So, looking around, certainly one nice feature about QB overall is there are some situations that can be resolved several ways…of course the trick is finding the most optimal end result.
LOCAL.queryresult = LOCAL.query.from(“users”)
.whereUSERID(1)
.update(values={“loginfailurecount”=LOCAL.query.raw(“ISNULL(loginfailurecount,0)+1”)},tosql=true);
… generates …
UPDATE [users] SET [loginfailurecount] = ISNULL(loginfailurecount,0)+1 WHERE [USERID] = ?
with (sql server) is exactly what I’d expect. So two non-blocking questions pop into my head…
Is the way I did it the best way to arrive at this result or is there a better method that could be use say for ease of readability / building out?
Since not all SQL syntax across database platforms may not be the same for this kind of functionality…
(a) could a closure be a better solution as a value for a field being updated? and/or…
(b) is this a good candidate for a special function that would allow for say “fieldincrement(fieldname, increment value)” so that the particular database could handle the null check and increment value behind the scenes?
(b.1) has this already been discussed and argued against and if so what was the argument against for curious minds
Slightly related…because .update() is a final command to ask the builder to execute the query…has anyone considering having .updateField() has a method that would allow several fields to be tacked onto the query as you are dynamically building it and just let .update() with options do the generation and execution? Inserts could be the same. Again…if an argument has been made against this ideas, I would love to know why. Building out a struct ahead of time is not always practical, I assume, because sometimes the values that will be assigned (like my example here) are cross referenced within values in the query being built.
Fun tool…sorry for the harassment.
Mike