mysql IN()，我想通过在产品表中接收多个类型条件作为参数来检索数据

Question

我想通过接收products表中的多个类型条件作为参数来检索数据。我尝试使用IN((，如下所示。如果我直接输入IN(a，b(来查询它，它工作得很好。但如果你把它作为一个参数来尝试，它不会起作用。

邮差发送的查询如下：

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

我做错了什么？也许我发送查询的方式不对？我需要帮助。

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;
};

Answer 1

您应该将字符串拆分为一个数组。否则，它将尝试匹配文字字符串a,b，而不是将它们视为IN列表中的单独值。

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;
};