Node.JS is returning only a part of records for the MySQL "WHERE IN" clause-CodePudding

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?

CodePudding user response：

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