providing list for WHERE <column> IN (...list) statement

[Leone25]

Hello,

today I found my self needing to fetch data from a table based on an id, where I had a list of ids and in a single query I wanted the results.
I tried looking in the docs but I couldn't see any built-in method to do this "cleanly".
This is what I came up with:

let myList = ['a','b','c','d'];
let data = db.prepare(`SELECT * FROM table WHERE id IN (${Array(myList.length).fill("?").join(",")})`).all(myList);

Is there a better way to do this?

any input is welcome

thx

Rico

[mceachen]

That's the only way I'm aware of. If there's a cleaner way, I'd love to know.

If you have a large (> 500) number of ids, it may be faster to bulk-load them into a temp table and do a inner join, or to chunk through your ids array in batches of N (where N is large but doesn't bonk against any SQLite limits: https://www.sqlite.org/limits.html )

[Leone25]

Alright, thanks!

[Prinzhorn]

There are many duplicates of this question, I personally use json_each because it does not have the limitations of providing a fixed list (SQLite has a maximum number) and you can re-use the same prepared statement. See #283 (comment)

[Leone25]

I didn't think of doing it that way, sounds like a good solution!

If it's such a common question why isn't it somewhere in the docs? Could be a good idea to add it.

Thx for the help

[Prinzhorn]

If it's such a common question why isn't it somewhere in the docs?

Because it's a SQLite question and not specific to better-sqlite3.

We could add an FAQ maybe, but people generally don't read docs or faqs or use the search functionality, otherwise I wouldn't close duplicates daily in multiple repos 😉

[Leone25]

i actually tried searching for similar issues, but didn't find stuff related to my question, i always forget about discussions...

but yea, i did read the docs, couldn't find any mention so i opened an issue.

and faq or examples section would probably be very useful