0

I have a web application built on Node.js and I use Knex as my db tool. It has all worked perfectly for months, but now, one of the columns in my db is not updating correctly. I have the columns 'base','bonus','production_bonus','deductions' and 'commission_earned'. The 'commission_earned' column is calculated on the backend of the application and then the values are sent to the db with knex. It is working perfectly for the initial creation of the record, but when I edit the record, it is not updating the 'commission_earned' column correctly. It updates the other values correctly, and when I console.log() the value to be stored for commission_earned it shows the correct value, it just is not updating it.

I have included screenshots of my code below as well as an example for you to see. I tried copying the column name from postgres to ensure it wasn't misspelled and it still doesn't work. So far, I have tried the following:

  • Ensured that all of the column names are being used correctly
  • Tried using a separate update call to update the value after updating the other values
  • Created a postgres function with a trigger that should update the column value any time a row is added
  • Tried using knex.raw to run the query that way
  • Tried placing the update method after the where clause in knex

I am stuck at this point and have no idea what to do.

P.S I know this db is not properly normalized, I built it when I was first learning how to build with Node

This is my index.js

When I console.log myData.commission_earned, it returns the correct value, but it does not update it in the db

app.post("/editScore", async (req,res) => {
    var score_id = req.body.scoreID;
    const myData = await calculateCommission.getData(req.body);
    knex('commissions').update(myData).where('id',score_id)
    .then( () => {
        res.redirect("/commissionLog")   
        })
});

Below is the code for the initial log, it is working correctly, and they both calculate the commission_earned with the same function

app.post("/submitCommission", async (req,res) => {
    //take the data sent in the request and send it to the commissionCalc file for processing
    const myData = await calculateCommission.getData(req.body);

        //Insert values into DB
    knex("commissions").insert(myData)
    .then( () => {
        res.redirect("/commissionLog")   
    })
})

I know it is not an issue with the calculation itself since I am able to console.log() and see that the correct value is being returned. And like I mentioned, it is updating the other values correctly, just not the commission_earned column.

get_post_malone
  • 36
  • 2
  • hmm hard to tell without seeing your table, and is not clear, looks like you try to update all the data and not only myData.commission_earned, you said the commission_earned column did not update, what about the other ? and where is the 'id' column ? or did you have on your table ? – ShueiYang Jan 24 '23 at 21:54
  • Yes, I am trying to update all of the data. All of the columns except for 'commission_earned' are updating correctly. The id column is on the table. It is locating the row correctly and updating the other values, just not commission_earned. – get_post_malone Jan 24 '23 at 23:03
  • Damn i am also stuck lol, i am quite new at knex, are you sure your myData.commission_earned from console log is different than your table? and you don't get any error right ? just in case can you try in this order with knex : `knex('commissions').where('id',score_id).update(myData).then ...` – ShueiYang Jan 24 '23 at 23:25
  • I can confirm they are indeed different. I am not sure why it does not want to update the commission_earned column, but it may just be time for a complete db redesign anyways. I have attempted using that order as well with no luck. I have been working with Knex for 2 years now and have never had this issue before. – get_post_malone Jan 25 '23 at 20:50
  • Can you also give a try since you are using async/await, to also use on the 2nd line, and replace "then": `await knex('commissions').where('id',score_id).update(myData); res.redirect("/commissionLog") ` – ShueiYang Jan 25 '23 at 21:11
  • So that didn't work either, but I was able to make it work! I will post what I did – get_post_malone Jan 25 '23 at 22:57
  • "*When I console.log `myData.commission_earned`, it returns the correct value*" - this sounds a bit like [being confused by `console.log`](https://stackoverflow.com/q/23392111/1048572) when the logged object changes asynchronously, which would explain the problem and why your solution (sometimes) works. Can you please [edit] your question to show how and where exactly you were logging `myData`, as well as the implementation of the `calculateCommission.getData` function? – Bergi Jan 26 '23 at 01:37

2 Answers2

0

I was finally able to get this to work by doing the following

app.post("/editScore", async (req,res) => {
var score_id = req.body.scoreID;
calculateCommission.getData(req.body).then( myData => {
    knex('commissions').update(myData).where('id',score_id)
    .then( () => {
        knex('commissions').update('commission_earned',myData.commission_earned).where('id',score_id).then( () => {
            res.redirect("/commissionLog")  
        })   
    })
})

});

get_post_malone
  • 36
  • 2
-1

Gratz for you to solved it! however i still don't think it's normal for you to do 2 operations in your database with the same data, it feel like you have to hard code to make it work and i am sure there is better and more elegant solution, i check again in the Knex documentation, how about you try again but "destructure" out your data? like this:

  app.post("/editScore", async (req,res) => {
     const score_id = req.body.scoreID;
     const myData = await calculateCommission.getData(req.body);
     await knex('commissions')
        .where('id',score_id)
        .update({
            foo1: myData.foo1,
            foo2: myData.foo2,
            foo3: myData.foo3, //...since i don't know how is your data stucture
            commission_earned: myData.commission_earned
        })
     res.redirect("/commissionLog")

Btw you can remove your Async keyword in your answer since you use only promise, as for me i am using here async/await, but this is just personal preference.

ShueiYang
  • 648
  • 1
  • 3
  • 7