Node -Postgres SELECT WHERE In Dynamic Query Optimization
We are working on a Node / Express web application with Postgres database using node-postgres package . We followed the instructions in this question and our query works like this:
exports.getByFileNameAndColName = function query(data, cb) {
const values = data.columns.map(function map(item, index) {
return '$' + (index + 2);
});
const params = [];
params.push(data.fileName);
data.columns.forEach(function iterate(element) {
params.push(element);
});
db.query('SELECT * FROM columns ' +
'INNER JOIN files ON columns.files_id = files.fid ' +
'WHERE files.file_name = $1 AND columns.col_name IN (' + values.join(', ') + ')',
params, cb
);
};
data
is an object containing a string fileName
and an array of column names columns
. We want this query to pull information from our "columns" and "files" tables from a dynamic number of columns.
db.query
takes as parameters (query, args, cb)
, where query
is an SQL query, args
is an array of parameters to pass to the query, and cb
is a callback function to execute on the database results.
So the code written this way returns the correct data, but (we think) it is ugly. We tried different ways of passing parameters to the request, but this is the only format that successfully returned data.
Is there a cleaner / easier way to pass our parameters? (for example, any way to pass parameters in such a way that node-postgres will accept without creating an additional array from array + non-array elements.)
Ask this because:
- maybe there is a better way to use the node-postgres package / we are using it incorrectly and
- If this is the correct way to solve this type of problem, then this code complements the answer in the above question.
source to share
My question was asking if there is a way to use the node-postgres library in a way that would clean up our create code params
before requesting. However, from a few deleted answers as well as those that remain, it seems that we are ornery and those few extra lines are not that important and that this is the best way to write this code. So, I mark this question "answered", although now it seems that it was not the biggest question, and perhaps we should not ask it in the first place.
source to share
Hello, I tried to translate "but (we think) it's ugly". I believe my answer answers your question. In the same question you linked you will find this answer
In which user takes pg-prom with special case variable formatting
In your case it might look something like this using a shared connection , but in your example, I would recommend using a simple db.query I just use a shared connection to show you how I expanded on "ugly":
exports.getByFileNameAndColName = function query(data,cb) {
var sco;
const params = [];
params.push(data.fileName);
data.columns.forEach(function iterate(element) {
params.push(element);
});
db.connect()
.then(function(obj){
sco=obj;
return sco.query('SELECT * FROM columns ' +
'INNER JOIN files ON columns.files_id = files.fid ' +
'WHERE files.file_name = $1 AND columns.col_name IN ($2^)',
pgp.as.csv(params)));
},function(reason){
console.log(reason);
})
.done(function(){
if(sco){
sco.done();
cb();
}
});
};
Now again I'm not sure what you meant by ugly, but in my case, the return format was something like this:
{
column:[
{
id: data,
data: data,
col_name: data,
files_id: data,
fid: data,
files_name: data
},...
]
}
And in my case, I really wanted this:
{
column:[
{
id: data,
data: data,
col_name: data,
files_id: data,
},...
],
file:[
{
fid: data,
files_name: data
},...
]
}
So, to do this, I took the same generic connection and added an additional variable to manage the results. Now this may not answer your question, or I just might say something, but I suggest taking a look at pg-promises , it might be useful for pre-queries and formatting.
source to share