Update a query in installment form

Question

Update a query in installment form

Navigation

#1 by (1 votes)

0

Good afternoon, today a small question has arisen. Somewhere in my code I have a query to do an update. The function is as follows:

function updateUser(user_id, params) {
    const query = 'UPDATE users 
                   SET user_name = $1,
                       user_brithday = $2,
                       user_active = $3
                   WHERE user_id = $4';
    const queryParams = [
        params.user_name,
        params.user_brithday,
        params.user_ative,
        user_id
    ];

    return pg.query(query, queryParams);
}

What I wanted was for example to send the object params without some keys, not to update to undefined but to preserve what is already there. I have already seen that it is possible to dynamically generate the query but what I wanted was to know if there is any way in the query to do that.

sql node.js postgresql

asked by anonymous 12.04.2018 / 18:30

1 answer

Inserting HTML elements inside another HTML Generated with Jquery in the same function Send URL parameter of a table to a modal

score 1 · Answer 1

If what you want to do is to mount a query dynamically with only the columns reported in the object, do the following:

Define a variable to store the number of parameters, and the object that will store the query values:

let total = 1;
let queryParams = [];

Set the variable query to the initial value:

let query = 'UPDATE users SET ';

Create a for loop to loop through the params object inside it, make sure the value of total is greater than 1, if it is larger, each loop concatenates a comma and the key in query .

if (total > 1) { query += ', '; }
query += '${key} = $${total}';

Then add the value of the key in the object queryParams ;

queryParams.push(params[key]);

Make the increment in the variable total :

total++;

The complete loop:

for (key in params){
  if (total > 1) { query += ', '; }
  query += '${key} = $${total}';
  queryParams.push(params[key]);
  total++;
}

Finally, concatenate the WHERE clause in query and add the parameter user_id on the object queryParams :

query += ' WHERE user_id = $${total}';
queryParams.push(user_id);

Example running

function updateUser(user_id, params) {
  let total = 1;
  let queryParams = [];
  let query = 'UPDATE users SET ';
  for (key in params){
    if (total > 1) { query += ', '; }
    query += '${key} = $${total}';
    queryParams.push(params[key]);
    total++;
  }
  query += ' WHERE user_id = $${total}';
  queryParams.push(user_id);
  console.log(query);
  console.log(queryParams);
  // return pg.query(query, queryParams);
}

updateUser(1, { user_name: 'Usuário 1', user_brithday: '1960-05-30' });
updateUser(2, { user_brithday: '1990-11-12' });