mysql IN(), I want to retrieve data by receiving multiple type condition as parameter in product tab-CodePudding

I want to retrieve data by receiving multiple type conditions as parameters in the products table. I tried using IN() as below. If I directly input IN(a,b) to query it, it works fine. But if you try it as a parameter, it won't work.

The query sent by Postman is as it follows:

?name=a&type=b,c&page=1

What am I doing wrong? Maybe the way I send the query is wrong? I need help.

const reviewSort = async (name, type, page) => {
  const sort = await myDataSource.query(
    `SELECT
      c.name AS category,
      t.name AS type,
      pr.name,
      pr.description,
      (SELECT COUNT(w.product_id)
        FROM products p
        LEFT OUTER JOIN product_wishlist w
        ON p.id = w.product_id
        WHERE p.id = pr.id) AS likeCount,
      COUNT(r.product_id) AS reviewCount,
      pr.price_origin
    FROM products pr
    JOIN category c ON c.id = pr.category_id
    JOIN product_types t ON t.id = pr.type_id
    LEFT OUTER JOIN review r ON pr.id = r.product_id
    WHERE c.name = ? AND t.name IN (?)
    GROUP BY pr.id
    ORDER BY COUNT(r.product_id) DESC
    LIMIT ?, 9`,
    [name, type, (page - 1) * 9]
  );
  return sort;
};

CodePudding user response：

You should split the string into an array. Otherwise, it's trying to match the literal string a,b, not treat them as separate values in the IN list.

const reviewSort = async (name, type, page) => {
  const sort = await myDataSource.query(
    `SELECT
      c.name AS category,
      t.name AS type,
      pr.name,
      pr.description,
      (SELECT COUNT(w.product_id)
        FROM products p
        LEFT OUTER JOIN product_wishlist w
        ON p.id = w.product_id
        WHERE p.id = pr.id) AS likeCount,
      COUNT(r.product_id) AS reviewCount,
      pr.price_origin
    FROM products pr
    JOIN category c ON c.id = pr.category_id
    JOIN product_types t ON t.id = pr.type_id
    LEFT OUTER JOIN review r ON pr.id = r.product_id
    WHERE c.name = ? AND t.name IN (?)
    GROUP BY pr.id
    ORDER BY COUNT(r.product_id) DESC
    LIMIT ?, 9`,
    [name, type.split(','), (page - 1) * 9]
  );
  return sort;
};