If case within string in JavaScript with variable either inside quotation marks or NULL?-CodePudding

This question is difficult to title but easy to show. I would like to add multiple sets of values to an SQL insert such as

var sqlInsertString = `INSERT INTO events (url) VALUES`
var sqlInsertValuesString = `(`('${event.url ? event.url : null}'), null, 1, 2, 3)`

pg_client.query(sqlInsertString   sqlInsertValuesString)

this happens in a loop and thus the separation of insert string and values string. Now, what I want to happen is that if event.url contains a url it should insert the url as 'https://www.example.com' (with quotation marks) but if event.url is empty, it should insert null.

My code above will insert 'null' (with quotation marks) instead of an actual null in the database. Removing the quotation marks will cause errors because of the ':' in the url.

How do I, in sqlInsertValuesString either provide a valid url string OR a 'real' null (without quotation marks) with this method?

CodePudding user response：

You can try

`(${event.url ? `'${event.url}'` : `null`})`

So there are only single quotes around the url if it exists, and not for nulls.

CodePudding user response：

Unless you particularly want to use the backticks, you can do this

var event = {};
event.url = 'http://someurl.com';
var sqlInsertString = "INSERT INTO events (url, a, b, c, d) VALUES ";
var sqlInsertValuesString = "('"   (event.url ? event.url   "'" : null)   ", null, 1, 2, 3)";

console.log(sqlInsertString   sqlInsertValuesString);

var event = {};
var sqlInsertString = "INSERT INTO events (url, a, b, c, d) VALUES ";
var sqlInsertValuesString = "("   (event.url ? "'"   event.url   "'" : null)   ", null, 1, 2, 3)";

console.log(sqlInsertString   sqlInsertValuesString);

CodePudding user response：

It probably makes sense to split the assignment for clarity:

let eventUrl = event.url ?? null;
const sqlInsertString = `INSERT INTO events (url) VALUES (${eventUrl}, null, 1, 2, 3)`

I have used the coalescing operator for the eventUrl