Dynamic query with SELECT IN Node JS

Question

I will explain my problem shortly. I have to select price inside my database about more services.

I am passing an array of ID of services and I am using a SELECT query to get all information about it.

let serviceIDSplitted = ServicesID.split(','); // To split the service id

var tokens = new Array(serviceIDSplitted.length).fill('?').join(','); // To generate the ?

let appointmentGetPriceQuery = `SELECT ServicePrice, ServiceDuration FROM SalonService WHERE PlaceID = ? AND ServiceID IN (${tokens});`; // The query

The query is good because if I try to make a console.log() its printing well but when I try to execute it I got this problem:

"SELECT ServicePrice, ServiceDuration FROM SalonService WHERE PlaceID = 'x' AND ServiceID IN ('1', 'e12204d6abf65c509d310057681b6a1a',?);"

I tried to debug and the main problem is this: I got 2 services + 1 PlaceID and it will put inside query 4 ? instead of 3 I got 3 services + 1 PlaceID and it will put inside query 5 ? instead of 4

Debugging the "tokens" variable the ? are ok, even inside the print of final query but when I try to execute it not.

How can I fix that?

Answer 1

You can do something like this:

let serviceIDSplitted = ServicesID.split(','); // To split the service id
let len = serviceIDSplitted.length === 1 || serviceIDSplitted.length === 0 ? 1 : serviceIDSplitted.length -1;
var tokens = new Array(len).fill('?').join(','); // To generate the ?

let appointmentGetPriceQuery = `SELECT ServicePrice, ServiceDuration FROM SalonService WHERE PlaceID = ? AND ServiceID IN (${tokens});`; // The query

Now, you can be sure if there is no serviceId or 1 you can populate your query well.