Node.JS is returning only a part of records for the MySQL “WHERE IN” clause

I am trying to execute the following GET function.

http://127.0.0.1:3000/sportfolioimages/getbyportfoliolist?portfolioid_list=69,70,71

My code is in Node.js, check below.

const mysql = require('mysql2');
const errorCodes = require('source/error-codes');
const PropertiesReader = require('properties-reader');

const prop = PropertiesReader('properties.properties');

const con = mysql.createConnection({
  host: prop.get('server.host'),
  user: prop.get("server.username"),
  password: prop.get("server.password"),
  port: prop.get("server.port"),
  database: prop.get("server.dbname")
});


exports.getSellerPortfolioItemImagesByPortfolioList = (event, context, callback) => {

  const params = event.queryStringParameters;


  if (!params || portfolioid_list == null) {
    context.callbackWaitsForEmptyEventLoop = false;
    var response = errorCodes.missing_parameters;
    callback(null, response)
  }
  else {

    const portfolioid_list = event.queryStringParameters.portfolioid_list;
    context.callbackWaitsForEmptyEventLoop = false;

    const sql = "SELECT * FROM peresia.seller_portfolio_item_images WHERE idseller_portfolio_item IN (?)";
      con.execute(sql, [portfolioid_list], function (err, result) {
      console.log(sql);
        if (err) {
          console.log(err);
          var response = errorCodes.internal_server_error;
          callback(null, response);
        }
        else {
          var response = {
            "statusCode": 200,
            "headers": {
              "Content-Type": "application/json"
            },
            "body": JSON.stringify(result),
            "isBase64Encoded": false
          };
          callback(null, response)
        }
      });


  }
};

My code always returns whatever the values that is at the first of the list of values in my call. Since my list of values are 69,70,71 it always returns only the records that matched 69 and and no records are returned for 70 and 71 even though there are records in the database.

So for an example, below is the result I get when the above GET function is executed,.

[
    {
        "idseller_portfolio_item_images": 22,
        "idseller_portfolio_item": 69,
        "image_url": "https://database.com/portfolio/IMG_20211020_114254-1634730049335.jpg"
    },
    {
        "idseller_portfolio_item_images": 23,
        "idseller_portfolio_item": 69,
        "image_url": "https://database.com/portfolio/IMG_20211020_114254-1634730049335.jpg"
    },
    {
        "idseller_portfolio_item_images": 31,
        "idseller_portfolio_item": 69,
        "image_url": "https://peresia3.s3.us-east-2.amazonaws.com/portfolio/IMG_20211020_114254-1634730049335.jpg"
    },
    {
        "idseller_portfolio_item_images": 32,
        "idseller_portfolio_item": 69,
        "image_url": "https://database/portfolio/IMG_20211020_114254-1634730049335.jpg"
    }
]

If I just run the MySQL code directly in the database, I will be able to get the full set of records without an issue.

Why is this and how can I fix it?

Answers:

Thank you for visiting the Q&A section on Magenaut. Please note that all the answers may not help you solve the issue immediately. So please treat them as advisements. If you found the post helpful (or not), leave a comment & I’ll get back to you as soon as possible.

Method 1

According to this you will need something like this

const portfolioid_list = [69,70,71];

const sql = "SELECT * FROM peresia.seller_portfolio_item_images WHERE idseller_portfolio_item IN (?,?,?)";
      con.execute(sql, portfolioid_list, function (err, result) { ...

This way you can dynamically build your query. reference

const portfolioid_list = event.queryStringParameters.portfolioid_list.split(",");  //converts to a list ["69", "70", "71"]
    
portfolioidValsPlaceHolders=Array(portfolioid_list.length).fill("?").join();
const sql = "SELECT * FROM peresia.seller_portfolio_item_images WHERE idseller_portfolio_item IN ("+portfolioidValsPlaceHolders+")"; 
      con.execute(sql, portfolioid_list, function (err, result) { ...    

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0