Filter a Postgres Table based on multiple columns

Question

I'm working on an shopping website. User selects multiple filters on and sends the request to backend which is in node.js and using postgres as DB. So I want to search the required data in a single query. I have a json object containing all the filters that user selected. I want to use them in postgres query and return to user the obtained results.

I have a postgres Table that contains a few products.

name         Category      Price
------------------------------
LOTR          Books        50
Harry Potter  Books        30
Iphone13      Mobile       1000
SJ8           Cameras      200

I want to filter the table using n number of filters in a single query.

I have to make it work for multiple filters such as the ones mentioned below. So I don't have to write multiple queries for different filters.

{ category: 'Books', price: '50' }
{ category: 'Books' }
{category : ['Books', 'Mobiles']}

I can query the table using

SELECT * FROM products WHERE category='Books' AND 'price'='100'
SELECT * FROM products WHERE category='Books'
SELECT * FROM products WHERE category='Books' OR category='Mobiles'

respectively. But I want to write my query in such a way that it populates the Keys and Values dynamically. So I may not have to write separate query for every filter.

I have obtained the key and value pairs from the request.query and saved them

const params = req.query;
const keys: string = Object.keys(params).join(",")
const values: string[] = Object.values(params)

const indices = Object.keys(params).map((obj, i) => {
  return "$" + (i + 1)
})

But I'm unable to pass them in the query in a correct manner.

Does anybody have a suggestion for me? I'd highly appreciate any help.

Thank you in advance.

Answer 1

This is not the way you filter data from a SQL database table.

You need to use the NodeJS pg driver to connect to the database, then write a SQL query. I recommend prepared statements.

A query would look like:

SELECT * FROM my_table WHERE price < ...

At least based on your question, to me, it is unclear why would want to do these manipulations in JavaScript, nor what you want to be accomplished really.