DEV Community

Discussion on: How To Build Rest API With NodeJS, Express, and MySQL

View post
Collapse
 
ramakanthrapaka profile image
RamakanthRapaka •

Hi Julia,
Can you please help me,How can we write query for WHERE IN,
when passing array to query it is giving no records found 404 response
when am passing comma separated ids it is giving syntax error because comma separated ids converting as string '(6,5) '

SELECT farmer.*, farmer_address.country, farmer_address.region, farmer_address.landmark FROM farmer INNER JOIN farmer_address ON farmer.id = farmer_address.farmer_id WHERE farmer.id IN '(6,5)'

for where in query can please share small code snippet

Collapse
 
juliest88 profile image
Julia Strichash •

Hi,
You need to use the prepared statement.
Just put placeholders (?) in the IN => IN(?, ?)
and pass the [5,6] array as a parameter to the query async method:

const result = await query(sql, [5, 6]);
Enter fullscreen mode Exit fullscreen mode

In my repository(on GitHub), I added an additional function in the common.utils.js file. this function sets the placeholders(?) instead of the array items, and returns back a string like '?, ?'.

exports.getPlaceholderStringForArray = (arr) => {
    if (!Array.isArray(arr)) {
        throw new Error('Invalid input');
    }

    // if is array, we'll clone the arr 
    // and fill the new array with placeholders
    const placeholders = [...arr];
    return placeholders.fill('?').join(', ').trim();
}
Enter fullscreen mode Exit fullscreen mode

So you can use this function to fill the placeholders as the number of your array something like => IN(getPlaceholderStringForArray([5, 6]))
Just don't forget to import the function.

Collapse
 
ramakanthrapaka profile image
RamakanthRapaka • • Edited

Hi Julie,

var farmercropsLists = await UserCropsModel.findCropsByChampsArray({ 'farmer.id': farmar_ids });

if (!Object.keys(params).length) {
return await query(sql);
}
for (const [key, value] of Object.entries(params)) {
var values = value;
sql += WHERE ${key} IN ( + getPlaceholderStringForArray(value) + );
}
console.log(sql);

    return await query(sql, values);
Enter fullscreen mode Exit fullscreen mode

can you please suggest JULIE how can we use where and where IN in single function

Thank you