1

I'm having some trouble with pg-promise.

I would like to specify an undefined number of parameters in the WHERE clause.

All the parameters will be concatenated with OR.

db.any(
  'SELECT genre FROM genres'+
  'WHERE ( (genres.genre LIKE \'Animation\' OR genres.genre LIKE \'Action\') ')
  .then(function(data) {
    // success;
  })
  .catch(function(error) {
    // error;
  });
});

Instead of specifying parameters one by one, I have an array of genres passed by the user.

I would like to do something like this...

var genres = ["Action", "Adventure", "Romantic"];   
db.any(
      'SELECT genre FROM genres'+
      'WHERE ( (genres.genre LIKE $1) '), [genres])
      .then(function(data) {
        // success;
      })
      .catch(function(error) {
        // error;
      });
});

I can't find a way to make it work.

Thank you for your help!

vitaly-t
  • 24,279
  • 15
  • 116
  • 138
Atomos
  • 17
  • 6
  • `pg-promise` is all about generating whatever SQL you want. Therefore, you should include example of a **valid** final SQL that you want generated. I don't think that `LIKE $1` can result in a valid SQL in your case. – vitaly-t Oct 08 '17 at 15:17

1 Answers1

2

Your question is more about PostgreSQL syntax for multiple LIKE usage, rather than pg-promise. See Combining multiple like queries.

The easiest in your case is to use LIKE ANY(array[...]) syntax:

const genres = ['Action', 'Adventure', 'Romantic'];

db.any('SELECT genre FROM genres WHERE genre LIKE ANY($1)'), [genres])
      .then(data => {
          // success, data = matching genres
      })
      .catch(error => {
          // error
      });
});

This will format and execute:

SELECT genre FROM genres WHERE genre LIKE ANY(array['Action', 'Adventure', 'Romantic'])
vitaly-t
  • 24,279
  • 15
  • 116
  • 138