Compare data within multiple queries - Postgres Node.js

Question

So I am creating an API endpoint where I send in two different types of data from the UI. One is a string and the other is an array containing upto 5 items. I need to compare the results across the queries and get data as the following.

This is a sample schema data for the above example: link

This is my implementation:

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

Is there a way I can simplify the way I compare data and mark them as present or not?

Answer 1

I might be oversimplifying it here but it is not straight forward to get a sample data from your question so pardon me if it is not 100% exactly what you are after:

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]))

The code above your compare the data array (which contains your elements from your sample data) against all the 3 different tables and produce a similar result "table" as you have in your post. You can further add the actual id and name etc to it.