select statement with dynamic columns for where condition

Question

I am make a nodejs project with postgresql as the backend and i use pg_promise as the driver for queries

curretly i have to do a select statement with columns that may vary but are all having the same equality condition check, like below

    pg.any('select * from table where col1 = ${col1} and col2 = ${col2}',{
col1:value1,
col2:value2
});

// which generates the query shown below
// select * from table where col2 = value1 and col2 = value2;

what i want is to hopefully find a easier way to generate the select query with variable no of columns having equality where conditions, something similiar to what pg_promise allows us to user helpers.update to genearte update queries.

// something like shown below
pg.helpers.select('select * from table ',{col1:value1, col2:value2})
// it shoud generate the same as the query with static columns
//select * from table where col2 = value1 and col2 = value2;

You should include in your question the expected output for your `$(dynamic columns list)`. Otherwise, it is not clear what you want. Also, some effort into formatting your question properly would be good, and perhaps, a better title. It looks like a mess. — vitaly-t, Nov 15 '20 at 23:11

vitaly-t · Accepted Answer · 2020-11-19T11:52:20.213

pg-promise doesn't include anything like this, because what you are looking for is a custom, and not particularly generic solution, because it may require AND/OR logic, type casting, use of nested properties, etc.

The library however, does give you all the tools necessary to create such a custom solution for yourself. For example, if all you need is AND condition for all properties in an object, you can use something like this:

const andProps = obj => ({
    rawType: true,
    toPostgres: () => Object.keys(obj).map(k => {
        const val = obj[k];
        if (val === null || val === undefined) {
            return pgp.as.format('$1:name IS NULL', [k]);
        }
        return pgp.as.format('$1:name = $2', [k, val]);
    }).join(' AND ')
});

The above code uses Custom Type Formatting, plus internal format function. And it adds special provision for null/undefined, to produce IS NULL. I used :name filter here, but you can also use :alias for shorter names (see SQL Names).

Usage Example

const obj = {
    id: null,
    name: 'John',
    age: 30
};

const data = await db.any('SELECT * FROM users WHERE $1', [andProps(obj)]);
//=> SELECT * FROM users WHERE "id" IS NULL AND "name" = 'John' AND "age" = 30

select statement with dynamic columns for where condition

1 Answers1

Linked