[SOLVED] Is there a way to get more than 1 value using?

Is there a way to get more than 1 value using?

I have a better-sqlite3 statement that orders and ranks my database, and I have a IN statement so I can select more than 1 row. That is where I run into an issue, I need to fetch multiple rows based on a dynamic array of IDs.

My SQLITE Statement looks like this:

Table.prepare('SELECT *, RANK () OVER (ORDER BY amount DESC) rank FROM table WHERE user IN(?)');

And I try to get from this statement with things like this:

getAll.get(['1','2','3']);
getAll.get('6,9,4');
getAll.get('7','5','8');

I get an error:

RangeError: Too many parameter values were provided

How exactly can I select multiple values without knowing the length of my array (so ?,? won't cut it), and allow as much values as possible? I used ?* and I get a Syntax error.

I am using better-sqlite3 for Node.JS

Solution

Instead of IN use the operator LIKE:

SELECT *, RANK () OVER (ORDER BY amount DESC) rank 
FROM table 
WHERE ',' || ? || ',' LIKE '%,' || user || ',%'

and pass the list as 1 string of comma separated values (without spaces):

all('6,9,4');