How to ignore a column update if not exist using knex?

Question

I'm trying to update a table using this syntax

knex(tableName) .where("id", "=", userId) .update({ [payment]: userPayment, exampleColumn: userInput, }) .then((_result) => {console.log(_result);}) .catch(err => console.error(err))

now exampleColumn may exist in some tables or not so i'm in a situation that i want to make this dynamic as possible

is there a syntax equal to this

.update({ [payment]: userPayment, exampleColumn?: userInput, // ? to update if exist if not ignore this line })

as work around i added a condition to my code

if (!isExampleColumn) { /* I get this from a another function */ userInput = undefined; } knex(tableName) .where("id", "=", userId) .update({ [payment]: userPayment, exampleColumn: userInput, // if userInput is undefined then it will be ignored }) .then((_result) => {console.log(_result);}) .catch(err => console.error(err))

but i think there is a better solution than this.

felixmosh · Accepted Answer · 2020-10-10 18:28:12Z

You can use conditional spread, it looks like:

knex(tableName) .where("id", "=", userId) .update({ [payment]: userPayment, ... isExampleColumn? {exampleColumn: userInput} : {}, }) .then((_result) => {console.log(_result);}) .catch(err => console.error(err))

Since Knex is a QueryBuilder, you can also split the "building" parts.

const query = knex(tableName) .where("id", "=", userId) .update({ [payment]: userPayment, }); if(isExampleColumn) { query.update({exampleColumn: userInput}) } const results = query .then((_result) => {console.log(_result);}) .catch(err => console.error(err))

does the second solution do query 3 times in db first one to create the query, second one is the result and lastly if isExampleColumn then it will update again ? is this correct ? and the first solution is exactly doing an if condition but with ternary operators ?
The first one makes the if as ternary, the second is the same query. Knex is a query builder, which executes the query against the DB only when then is called. you can "break" the query as you need (as I did in the second example)

Collectives™ on Stack Overflow

How to ignore a column update if not exist using knex?

1 Answer 1

2 Comments

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Related