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

比较多个查询中的数据-Postgres节点.js

node-postgres lodash object node.js javascript

-1

arunmmanoharan · 技术社区 · 7 年前

所以我创建了一个API端点,在这里我从UI发送两种不同类型的数据。一个是字符串,另一个是最多包含5个项目的数组。我需要比较查询的结果,并获得如下数据。

以下是上述示例的模式数据示例: link

这是我的实现:

router.post('/compareRoles/', async (request, response, next) => {

  const srcRoleName = request.body.srcRole // 'ENGINEER';
  const tgtRoleNames = request.body.tgtRoles // ['ARCHITECT', 'TESTER'] array can have atleast one element and a maximum length of 5 chosen via a multi-select dropdown;

  const querySrcRoleName = `select * from table roles where role_name = '${srcRoleName}'`;

  const tgtRoleQueries = [];

  _.forEach(tgtRoleNames, (item, index) => {
    this['tgtQuery' + index] = `select * from table roles where role_name = '${item}'`;

    tgtRoleQueries.push(this['tgtQuery' + index])
  });


  let result = {};

  try {
    const results = await Promise.all([compareRoles()]);
    result = Object.assign({}, result, ...results);
  } catch (err) {
    result.error = err.message;
  }
  response.json(result);

  async function compareRoles() {
    const srcRoleValues = await pool.query(queryJobRoleName);
    let tgtRoleValues0, tgtRoleValues1, tgtRoleValues2, tgtRoleValues3, tgtRoleValues4;
    if (tgtRoleQueries[0]) {
      tgtRoleValues0 = await pool.query(tgtRoleQueries[0]);
    }
    if (tgtRoleQueries[1]) {
      tgtRoleValues1 = await pool.query(tgtRoleQueries[1]);

    }
    if (tgtRoleQueries[2]) {
      tgtRoleValues2 = await pool.query(tgtRoleQueries[2]);
    }
    if (tgtRoleQueries[3]) {
      tgtRoleValues3 = await pool.query(tgtRoleQueries[3]);

    }
    if (tgtRoleQueries[4]) {
      tgtRoleValues4 = await pool.query(tgtRoleQueries[4]);
    }

    let compareRoles = _.uniqWith(_.concat(srcRoleValues.rows,
      tgtRoleQueries[0] ? tgtRoleValues0.rows : [],
      tgtRoleQueries[1] ? tgtRoleValues1.rows : [],
      tgtRoleQueries[2] ? tgtRoleValues2.rows : [],
      tgtRoleQueries[3] ? tgtRoleValues3.rows : [],
      tgtRoleQueries[4] ? tgtRoleValues4.rows : []),
      _.isEqual);

    compareRoles = _.map(compareRoles, function (obj, index) {
      const comparedObj = {
        role_name: obj.role_name,
        role_id: obj.role_id,
        presentInSrc: _.find(
          srcRoleValues.rows, (o) => o.role_name === obj.role_name && o.role_id === obj.role_id
        ) ? "true" : "false",
      };

      if (tgtRoleQueries[0]) {
        comparedObj['presentInTgt0'] = _.find(
          tgtRoleQueries[0].rows, (o) => o.role_name === obj.role_name && o.role_id === obj.role_id
        ) ? "true" : "false"
      }
      if (tgtRoleQueries[1]) {
        comparedObj['presentInTgt1'] = _.find(
          tgtRoleQueries[1].rows, (o) => o.role_name === obj.role_name && o.role_id === obj.role_id
        ) ? "true" : "false"
      }
      if (tgtRoleQueries[2]) {
        comparedObj['presentInTgt2'] = _.find(
          tgtRoleQueries[2].rows, (o) => o.role_name === obj.role_name && o.role_id === obj.role_id
        ) ? "true" : "false"
      }
      if (tgtRoleQueries[3]) {
        comparedObj['presentInTgt3'] = _.find(
          tgtRoleQueries[3].rows, (o) => o.role_name === obj.role_name && o.role_id === obj.role_id
        ) ? "true" : "false"
      }
      if (tgtRoleQueries[4]) {
        comparedObj['presentInTgt4'] = _.find(
          tgtRoleQueries[4].rows, (o) => o.bus_nm === obj.bus_nm && o.role_id === obj.role_id && o.rbf_rating === obj.rbf_rating && o.prvlg_grp === obj.prvlg_grp
        ) ? "true" : "false"
      }
      return comparedObj;
    });
    return {rows: compareRoles};
  }
});

有没有一种方法可以简化我比较数据并将它们标记为存在的方式?

1 回复 | 直到 7 年前

1

Akrion 7 年前

我在这里可能过于简单化了,但是从你的问题中得到一个样本数据并不是直接的,所以请原谅我,如果不是100%正是你想要的:

const src = [{ id: 1, name: 'a' }, { id: 2, name: 'b' }, { id: 3, name: 'c' }, { id: 4, name: 'd' }]

const tgt1 = { name: 'tgt1', data: [{ id: 3, name: 'c' }, { id: 4, name: 'd' }]}
const tgt2 = { name: 'tgt2', data: [{ id: 1, name: 'a' }, { id: 4, name: 'd' }]}
const tgt3 = { name: 'tgt3', data: [{ id: 2, name: 'b' }, { id: 3, name: 'c' }]}
const tgt4 = { name: 'tgt4', data: [{ id: 1, name: 'a' }, { id: 3, name: 'c' }]}
const tgt5 = { name: 'tgt5', data: [{ id: 1, name: 'a' }, { id: 4, name: 'd' }]}

const summary = (arr, arrays) => {
   arr.forEach(x => {
     arrays.map(({name, data}) => {
       x[name] = data.some(y => y.name == x.name)
       return x
     })
  })
  return arr
}

console.log(summary(src, [tgt1,tgt2,tgt3,tgt4,tgt5]))

data 数据 )对所有3个不同的表,并产生一个类似的结果“表”你在你的帖子。您可以进一步添加 id 和 name