Function to get rows from DB by multiple columns conditionally

Question

So lets say I have a GET route for /movies (it's not actually movies but it would be easier to explain this way)

On client side I have a table that I want to filter movies depending on dropdown menus:

Genre, Year, Country, Type (By default they all are on ALL)

Those are also columns on the db for the movies table. I am using node-postgres

So my query would be like this:

  const query = {
    text: 'SELECT * FROM movies WHERE genre = $1 AND year = $2 AND country = $3 AND type = $4',
    values: [genre, year, country, type],
  };

But that doesn't work if the user don't choose anything from the dropdown (it would be ALL)

What would be the best way to do this so if the client sends ALL it just don't add that column to the query?

Webvoid · Accepted Answer · 2018-06-05 10:09:26Z

1

I think you have no choice, you should only put your conditions if they are applicables...

For example:

const params = {
    genre: null,
    year: 2015,
    country: null,
    type: 'western',
};

const query = {
    text: 'SELECT * FROM movies',
    values: Object.values(params).filter(x => x),
};

if (Object.values(params).filter(x => x).length) {
    query.text += ' WHERE '
    let i = 1;
    for (const param of Object.keys(params).filter(x => params[x])) {
        query.text += `${param} = $${i} AND `
        i++;
    }
    query.text = query.text.substr(0, query.text.length - 5);
}

console.log(query); // {"text":"SELECT * FROM movies WHERE year = $1 AND type = $2","values":[2015,"western"]}

Hope it helps.

edited Jun 5, 2018 at 10:09

answered Jun 5, 2018 at 10:00

Webvoid

5112 silver badges7 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Function to get rows from DB by multiple columns conditionally

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related