NodeJS Sqlite WHERE IN Query Stament with dynamic Parameter (?)-CodePudding

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

CodePudding user response：

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]

EDIT

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.

CodePudding user response：

What you should not do!

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...

** EDIT **

What you should not do!

EDIT