javascript - 重构 KnexJS 多重 Promise

Question

我正在使用 Knexjs 和 Promise 运行多个选择查询。我需要所有查询在发送结果之前返回一个值，我已经能够实现这一点。但是我不认为代码非常优化。

knex(table).select('CRMContactId').where('FCIRecognition', '<', -49.00)
            .then(function(results) {

                data.largeclawbacks = results.length;

                knex(table).select('CRMContactId').where('PlanStatus', 'Out of Force').andWhere(function(){this.whereNot('IncomeType', 'Fund Based Commission').andWhereNot('IncomeType', 'Renewal Commission')})
                    .then(function(results) {

                        data.outofforce = results.length;

                        knex(table).select('CRMContactId').where('GroupOneCaption', 'Tier 2').andWhereNot('Payaways Made/Received', 'Payaway Made')
                            .andWhere((builder) => builder.whereIn('Plantype', ['Flexible Benefits','General Insurance','Group Critical Illness','Group Death In Service','Group Dental Insurance','Group Healthcare Cash Plan','Group Income Protection','Group Life','Group Life;Group Income Protection','Group PMI','Group Travel Insurance']))
                            .andWhereNot('Payable', 0)
                            .then(function(results) {

                                data.tier2 = results.length;

                                knex(table).select('CRMContactId').where((builder) => builder.where('GroupOneCaption', 'Tier 3').orWhere('GroupOneCaption', 'Tier 4')).
                                andWhereNot('Payaways Made/Received', 'Payaway Made')
                                    .andWhere((builder) => builder.whereIn('Plantype', ['Accident Sickness & Unemployment Insurance','AVC','Discretionary Managed Service','Endowment','Enhanced Pension Annuity','Executive Pension Plan','FSAVC','General Investment Account','Income Drawdown','Income Protection','Individual Retirement Account', 'Insurance / Investment Bond','Investment Trust','ISA','Long Term Care','Maximum Investment Plan','Money Purchase Contracted','OEIC / Unit Trust','Offshore Bond','Pension Annuity','Pension Term Assurance','Personal Equity Plan','Personal Pension Plan','Regular Savings Plan','Relevant Life Policy','s226 RAC','s32 Buyout Bond','Savings Account','SIPP','SSAS','Stakeholder Individual','Term Protection','Venture Capital Trust','Whole Of Life','Wrap']))
                                    .andWhereNot('Payable', 0)
                                    .then(function(results) {

                                        data.tier3 = results.length;

                                        knex(table).select('CRMContactId').where('FCIRecognition', '>', 500.00).andWhere('IncomeType', 'Renewal Commission')
                                            .then(function(results) {

                                                data.largerenewal = results.length;

                                                knex.raw(`SELECT ContactName AS Adviser, FORMAT(SUM(Payable),2) AS 'Renewal Income' FROM fci_test WHERE IncomeType IN ("Renewal Commission","Fund Based Commission","Ongoing Fee") AND \`Payaways Made/Received\` != 'Payaway Made' GROUP BY ContactName`)
                                                    .then(function(results){

                                                        data.renewalincome = results[0];
                                                        res.send(data)
                                                    })

                                            })
                                    })
                            })
                    })
            })

我确信有更好的方法来编码并获得相同的结果。

Answer 1

我首先关心的是可读性，然后才是性能。首先使代码更具可读性，更容易看出可以应用哪种优化。

经过一些重构，我们可以得到类似于以下的代码:

knex(table).select('CRMContactId')
  .where('FCIRecognition', '<', -49.00)
  .then(function(results) {

    data.largeclawbacks = results.length;

    knex(table).select('CRMContactId')
      .where('PlanStatus', 'Out of Force')
      .andWhere((builder) => {
        builder.whereNot('IncomeType', 'Fund Based Commission')
          .andWhereNot('IncomeType', 'Renewal Commission');
      })
      .then(function(results) {

        data.outofforce = results.length;

        knex(table).select('CRMContactId')
          .where('GroupOneCaption', 'Tier 2')
          .andWhereNot('Payaways Made/Received', 'Payaway Made')
          .whereIn('Plantype', tier2PlanTypes)
          .andWhereNot('Payable', 0)
          .then(function(results) {

            data.tier2 = results.length;

            knex(table).select('CRMContactId')
              .whereIn('GroupOneCaption', ['Tier 3', 'Tier 4'])
              .andWhereNot('Payaways Made/Received', 'Payaway Made')
              .whereIn('Plantype', tier3PlanTypes)
              .andWhereNot('Payable', 0)
              .then(function(results) {

                data.tier3 = results.length;

                knex(table).select('CRMContactId')
                  .where('FCIRecognition', '>', 500.00)
                  .andWhere('IncomeType', 'Renewal Commission')
                  .then(function(results) {

                    data.largerenewal = results.length;

                    knex.raw(`SELECT ContactName AS Adviser, FORMAT(SUM(Payable),2) AS 'Renewal Income' FROM fci_test 
                              WHERE IncomeType IN ("Renewal Commission","Fund Based Commission","Ongoing Fee") 
                                AND \`Payaways Made/Received\` != 'Payaway Made' GROUP BY ContactName`)
                      .then(function(results){
                          data.renewalincome = results[0];
                          res.send(data)
                      });
                  })
              })
          })
      })
  });

看起来不太像，但是我可以更清楚地看到所有查询都是相互独立的(我将用它来优化)

之后，进一步重构，我将每个查询保存在一个常量中，然后使用 Promise.all 一次发出所有查询以及它们完成的方式以便发送响应。

const largeclawbacksQuery = knex(table).select('CRMContactId')
  .where('FCIRecognition', '<', -49.00);

const outofforceQuery = knex(table).select('CRMContactId')
  .where('PlanStatus', 'Out of Force')
  .andWhere((builder) => {
    builder.whereNot('IncomeType', 'Fund Based Commission')
      .andWhereNot('IncomeType', 'Renewal Commission')
  });

const tier2Query = knex(table).select('CRMContactId')
    .where('GroupOneCaption', 'Tier 2')
    .andWhereNot('Payaways Made/Received', 'Payaway Made')
    .whereIn('Plantype', tier2PlanTypes)
    .andWhereNot('Payable', 0);

const tier3Query = knex(table).select('CRMContactId')
  .whereIn('GroupOneCaption', ['Tier 3', 'Tier 4'])
  .andWhereNot('Payaways Made/Received', 'Payaway Made')
  .whereIn('Plantype', tier3PlanTypes)
  .andWhereNot('Payable', 0);

const largerenewalQuery = knex(table).select('CRMContactId')
  .where('FCIRecognition', '>', 500.00)
  .andWhere('IncomeType', 'Renewal Commission');

const renewalincomeQuery = knex.raw(
  `SELECT ContactName AS Adviser, FORMAT(SUM(Payable),2) AS 'Renewal Income' FROM fci_test 
    WHERE IncomeType IN ("Renewal Commission","Fund Based Commission","Ongoing Fee") 
      AND \`Payaways Made/Received\` != 'Payaway Made' GROUP BY ContactName`
);

Promise.all([largeclawbacksQuery, outofforceQuery, tier2Query, tier3Query, largerenewalQuery, renewalincomeQuery])
  .then((result) => {
    res.send({
      largeclawbacks: result[0].length,
      outofforce: result[1].length,
      tier2: results[2].length,
      tier3: results[3].length,
      largerenewal: results[4].length,
      renewalincome: results[4][0],
    });
  });

要点:

• whereIn 可以链接起来，它们将转换为 sql WHERE afield IN avalues AND bfield IN bvalues
• 行长可以提高可读性，从而使代码更易于阅读
• 如果我们将其视为 promise ，我们可以等待查询构建器完成其查询

进一步改进:

• 每个方法(whereIn、where、orWhere 等)都会返回一个查询构建器可以通过克隆查询生成器实例来重用部分查询，如所述 here 。这可以帮助您定义 tier2Query 和 tier3Query 的基本查询。
• 我们可以等待使用更好的 API 来解决 promise ，例如 promise-all-properties
• 您可以直接请求 COUNT，而不是查询所有记录来获取长度值，这样可以提高性能。