1

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

SomePerson
  • 1,171
  • 4
  • 16
  • 45

1 Answers1

0

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');
forpas
  • 160,666
  • 10
  • 38
  • 76
  • This only returns the first result I think? I'm using `better-sqlite3` and I didn't get anything like an Array, I just got a single row. – SomePerson Aug 15 '20 at 21:21
  • This query returns all the rows where user is 6 or 9 or 4 if you pass the string '6,9,4'. For the better-sqlite3 code I'm not sure, I used your code of getAll() which is the only appropriate for this case because it passes just 1 string. – forpas Aug 15 '20 at 21:27
  • 2
    @SomePerson from the documentation of `all()`: *Similar to .get(), but instead of only retrieving one row all matching rows will be retrieved. The return value is an array of row objects.* So use `all()` instead of `get()`. – forpas Aug 15 '20 at 21:35