代码之家 › 专栏 › 技术社区 › Richlewis

将数组传递到postgresql查询-nodejs

postgresql node.js

Richlewis · 技术社区 · 6 年前

IN 条件,当我手动将每个搜索词添加到查询中时,我可以成功地返回结果

async function getFixtures() {
  let response;
  try {
    response = await pool.query("SELECT * FROM fixtures WHERE league_name IN ('Chinese Super League', 'Iran Pro League', 'English Premier League')");
  } catch (e) {
    console.error('Error Occurred', e);
  }
    return response.rows;
}

当我尝试传递一个数组时,却没有返回结果

async function getFixtures(leaguesArray) {
  let response;
  try {
    response = await pool.query("SELECT * FROM fixtures WHERE league_name IN ($1)", [leaguesArray]);
  } catch (e) {
    console.error('Error Occurred', e);
  }
    return response.rows;
}

当我注销时 leaguesArray

['Chinese Super League', 'Iran Pro League', 'English Premier League']

[['Chinese Super League', 'Iran Pro League', 'English Premier League']]

我需要一个初始数组来转换吗?

我显然在这里遗漏了一些东西,但不确定是什么

谢谢

1 回复 | 直到 6 年前

mtshaikh 6 年前

作为 docs 提到。我相信你有两种方法

async function getFixtures(leaguesArray) {
let response;
  try {
    response = await pool.query("SELECT * FROM fixtures WHERE league_name = ANY ($1)", [leaguesArray]));
  } catch (e) {
    console.error('Error Occurred', e);
  }
return response.rows;
}

方法2

async function getFixtures(leaguesArray) {
let response;
const offset = 1;
const placeholders = leagueArray.map(function(name,i) { 
    return '$'+(i+offset); 
}).join(',');
  try {
    response = await pool.query("SELECT * FROM fixtures WHERE league_name IN (" + placeholders+")", leaguesArray));
  } catch (e) {
    console.error('Error Occurred', e);
  }
return response.rows;
}

推荐文章