select statement with dynamic columns for where condition
I need to do exactly what this answer says except my query is:
export async function find_items_based_on_params(category,type,obj) {
const q = 'SELECT * FROM $1 INNER JOIN category ON category.cat_id = $2 WHERE (category.cat_type = $3)'
const res = await sql.query(q, [type,type + '.category_id',category]);
return res;
}
Depending on whether the obj has obj.name obj.color I need to add ' AND table = obj.name '
So if obj parameter was
obj = {
name: 'Brick',
color: 'brown'
}
//Expected query
//SELECT * FROM $1 INNER JOIN category ON category.cat_id = $2 //WHERE (category.cat_type = $3) AND $4:name = $5 AND $6 = $7;
I've tried use the function above and creating my own function (which isn't suggested) but Im struggling to understand how to construct this correctly.
I constructed a custom function to add the values to the query and escaping array. I don't know how safe this is but it works and am awaiting a reply from someone with experience.
export async function find_items_based_on_params(category,type,obj) {
let res;
let escaping_array = [];
let q = 'SELECT * FROM $1:name INNER JOIN category ON category.cat_id = $2:value WHERE category.cat_type = $3';
escaping_array = [type,type + '.category_id',category];
res = await sql.query(add_conditional_and_logic(obj,escaping_array,type,q), escaping_array);
return res;
}
function add_conditional_and_logic(obj,escaping_array,type,query) {
Object.keys(obj).map(k => {
const val = obj[k];
escaping_array.push(type + '.' + type + '_' + k);
escaping_array.push(val);
let len = escaping_array.length;
query += ` AND $${len -1}:alias = $${len}`;
});
return query;
}
//escaping array turns into
//[type,type + '.category_id',category,OBJ.VAL1,OBJ.VAL2]
//q string turns into
//'SELECT * FROM $1:name INNER JOIN category ON category.cat_id = $2:value WHERE category.cat_type = $3 AND $4:alias = $5'