I use dynamic parameters for my SQLITE statments in node. For example SELECT * FROM table WHERE table.id = ?. This all works quite well. But with a WHERE IN query I always get no results. Which indicates that it is misinterpreting the dynamic parameter. Here is my code:
getModelsByBrandId: (ids_array) => {
const ids = ids_array.toString();
const sql = "SELECT * FROM models WHERE brand_id IN (?)";
const params = [ids];
return new Promise((resolve, reject) => {
db.all(sql, params, async (err, rows) => {
if (err) {
reject(err);
} else {
resolve(rows)
}
});
});
},
I had already tried passing the array but also a string (array.toString()). Unfortunately, neither of these returned any results.
Question: What am I doing wrong and what do I have to do to make the WHERE IN query work?
Thanks in advance! Max
getModelsByBrandId: (ids_array) => {
const placeholders = ids_array.map(() => '?').join(',');
const params = ids_array;
const sql = "SELECT * "
+ "FROM models "
+ "WHERE brand_id IN (" + placeholders + ")";
return new Promise((resolve, reject) => {
db.all(sql, params, async (err, rows) => {
if (err) {
reject(err);
} else {
resolve(rows)
}
});
});
},
Should send a query like
getModelsByBrandId([1, 2, 3]);
// sql = "SELECT * FROM models WHERE brand_id IN (?,?,?)"
// params = [1, 2, 3]
I needed somewhat the same feature, where I was dealing with raw queries. So, instead of writing everything, and as a personal exercise, I wrote a template function that handled SQL parameters like these.
import { sql } from "./sql.template";
const brand_ids = [1, 2, 3];
const [ query, params ] = sql`
SELECT *
FROM models
WHERE brand_id IN (${brand_ids})
`;
console.log({ query, params });
// { query:"SELECT * FROM models WHERE brand_id IN (?,?,?)", params:[1, 2, 3] }
Source available here.
Is to write the statement without "dynamic parameter" like:
const sql = "SELECT * FROM models WHERE brand_id IN (" + ids_array.toString() + ")";
const params = [];
Why? See the comments below...