How to construct conditional AND query to existing query with pg-promise

Question

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.

Answer 1

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'