Update setting value by calculation with other columns

[10787]

Is it possible to set a value in an update operation, by referencing other columns, similar to the following sql example:
update orderLine
set lineAmount = price * qty

What would be the syntax in jsstore?
Not this for sure...:
var noOfRowsUpdated = await jsstoreCon.update({
in: 'orderLine',
set: {
lineAmount: 'quantityLine' * 'price'
}
});

Thanks

[ujjwalguptaofficial]

Right now, its not supported but i can work on this. We already have option to update with operators which accepts hardcoded value - https://jsstore.net/tutorial/update/with-operators/

So it should be easy to implement.

[10787]

That would be great, much appreciated. I have seen and played with the operator-option you mentioned, but clearly that would not help in this case, where I need to reference values from other columns in the set-part.

I am new to this, so just curious how such improvements would work from a timeline/release persepctive?

[ujjwalguptaofficial]

@10787 have added a mapSet option in updateQuery which can be used to map the set value. Here is doc - https://jsstore.net/tutorial/update/map-set/

The feature is added in version - 4.3.0. Let me know if you need any help.

[pylnata]

Hello @ujjwalguptaofficial ,
am I right that if I use connection with web worker I am obligated to use mapSet also with web worker?

Because I have connection with web-worker. But formula for update goes from user. And updating method without web-worker would be more suitable. As I can change this function dynamically.

When I tru to use mapSet without web Worker I get error:

jsstore.commonjs2.js:592 Uncaught DOMException: Failed to execute 'postMessage' on 'Worker': function updateMapper(setValue, storedValue) {
setValue.RESCA = storedValue.RESCA * 10;
} could not be cloned.
at Connection.push../node_modules/jsstore/dist/jsstore.commonjs2.js.module.exports.2.ConnectionHelper.sendRequestToWorker

Thanks

[ujjwalguptaofficial]

@pylnata you need to change code for jsstore initiation. Here is doc for jsstore without web worker - https://jsstore.net/tutorial/connection/#webpack-without-web-worker

[pylnata]

@ujjwalguptaofficial thanks, changed code and not it works without web worker. I have a question about perfomance for update query. I have 11000 records in table. Select queries works quite fast for me. With speed of insert I can live.

But I just tried update as:

function updateMapper(setValue, storedValue) {
setValue.RESCA = storedValue.RESCA + 10;
}
await this.connection.update({
in: 'rows',
set: {
RESCA: 0,
},
mapSet: updateMapper,
});

and it takes about 10 seconds... I couldn't imagine that it be so slow :(

Is there any way to update faster? I tried also:

const existingData = await this.connection.select({
from: 'rows',
});
const newData = existingData.map((value: any) => {
value.RESCA = value.RESCA + 10;
return value;
});

    await this.connection.insert({
        into: 'rows',
        upsert: true,
        values: newData,
    });

It takes about 8 sec..

Do you have any suggestions?

Thanks

[ujjwalguptaofficial]

please ask in another thread @pylnata