javascript - 从 pg-promise 过滤 CSV 仅采用数组中的第一个值

Question

我的 pg-promise 查询(传入数组中的 2 个 id):

db.any(`SELECT * FROM users WHERE user_id IN ($1:csv)`, team.member_ids)

传递给 pg-promise 的查询和参数的控制台输出:

SELECT * FROM users WHERE user_id IN ($1:csv) [ 1, 2 ]
//Fails one row returned, two expected

但是我只得到一个结果。如果我在 psql 中运行以下查询，我会得到两个结果:

select * from users where user_id IN (1,2);
//Works successfully with two rows returned

感谢任何帮助!

Answer 1

这在 Query Formatting 中有解释，一个值与数组如何改变值的解释:

This however works only for types number, bigint, string, boolean, Date and null, because types like Array and Object change the way parameters are interpreted.

db.any(`SELECT * FROM users WHERE user_id IN ($1:csv)`, team.member_ids)
//=> SELECT * FROM users WHERE user_id IN (1)

因为在上面您直接传入值数组本身，所以 $1 指的是该数组中的第一个元素。要使 $1 引用整个值数组，该值本身需要在一个数组中:

db.any(`SELECT * FROM users WHERE user_id IN ($1:csv)`, [team.member_ids])
//=> SELECT * FROM users WHERE user_id IN (1,2)

或者，推荐使用Named Parameters使代码更简洁:

db.any(`SELECT * FROM users WHERE user_id IN ($<member_ids:csv>)`, team)
//=> SELECT * FROM users WHERE user_id IN (1,2)

一般来说，使用pg-monitor查看正在执行的查询。