Handle SQL IN(...) statement

[benomite]

Just submitting an enhancement idea. It would be awesome if this could work:

query.append(SQL` WHERE i.type IN (${types})`);

This results in an error error: invalid input syntax for integer: "4,2,3"

For now, the workaround is something like:

query.append(`
  WHERE i.type IN (
`);
types.forEach((id, index, type) => {
    where.append(SQL`${id}`);
    if(index < types.length - 1) {
      where.append(',');
    }
});
where.append(`)`);

Thanks for this awesome module !

[felixfbecker]

See https://github.com/felixfbecker/node-sql-template-strings/issues/34#issuecomment-292319942

[felixfbecker]

If you use a dialect that doesn't support ANY, you can also write it like this:

types.reduce((prev, curr) => prev.append(',').append(SQL`${id}`), where);

[felixfbecker]

It is probably worth to add some documentation for this

[benomite]

Thank you very much! Exactely what I needed. I'll try to find time for a PR and add these info in the readme.

[benomite]

https://github.com/felixfbecker/node-sql-template-strings/pull/43

[ro-savage]

@felixfbecker - Unless I am missing something your suggest doesn't work as it append a , to the start of the where clause. e.g. query SELECT * FROM transactions WHERE id IN (,$1,$2)

Could a helper be made, something like .appendWhereIn(columnName, array) that takes an array and then creates the correct where clause for you?

function appendWhereIn(column, array) {
  this.query.append(`
      WHERE txHash IN (
    `);
    transactions.forEach((transaction, i) => {
      this.query.append(SQL`${transaction}`)
      if (i < transactions.length - 1) { query.append(',') }
    })
    this.query.append(`)`)
}

[ro-savage]

The reason for creating a new function, is to give users insight into how to use this module.

Its no obvious for someone new that they can not do this

SQL`SELECT * FROM transactions WHERE id IN (${transactions.join(', ')})`

Then they have to run down a huge rabbit hole of trying to figure out the correct way to do this.

While having a specific function and docs saves people from going through the same process over and over again.

We ended adding this

SQL.SQLStatement.prototype.appendWhereIn = function(column, array) {
  this.append(` WHERE ${column} IN (`);
  array.forEach((item, i) => {
    this.append(SQL`${item}`);
    if (i < array.length - 1) {
      this.append(",");
    }
  });
  this.append(`)`);

  return this;
};

which is used like

const query = SQL`SELECT * FROM transactions`;
query.appendWhereIn("id", transactionsIds);