Monday, 3 April 2017

Using KnexJS to query X number of tables?

I have a unique situation here which I am having trouble solving in an elegant fashion.

A user passes up an array of signals which they want to export data for. This array can be 1 -> Any_Number so first I go fetch the table names (each signal stores data in a separate table) based on the signals passed and store those in an object.

The next step is to iterate over that object (which contains the table names I need to query), execute the query per table and store the results in an object which will be passed to next chain in the Promise. I haven't seen any examples online of good ways to handle this but I know it's a fairly unique scenario.

My code prior to attempting to add support for arrays of signals was simply the following:

exports.getRawDataForExport = function(data) {
  return new Promise(function(resolve, reject) {

    var getTableName = function() {
      return knex('monitored_parameter')
        .where('device_id', data.device_id)
        .andWhere('internal_name', data.param)
        .first()
        .then(function(row) {
          if(row) {
            var resp = {"table" : 'monitored_parameter_data_' + row.id, "param" : row.display_name};
            return resp;
          }
        });
    }

    var getData = function(runningResult) {
      return knexHistory(runningResult.table)
        .select('data_value as value', 'unit', 'created')
        .then(function(rows) {
          runningResult.data = rows;
          return runningResult;
        });
    }

    var createFile = function(runningResult) {

      var fields = ['value', 'unit', 'created'],
          csvFileName = filePathExport + runningResult.param + '_export.csv',
          zipFileName = filePathExport + runningResult.param + '_export.gz';

      var csv = json2csv({data : runningResult.data, fields : fields, doubleQuotes : ''});

      fs.writeFileSync(csvFileName, csv);

      // create streams for gZipping
      var input = fs.createReadStream(csvFileName);
      var output = fs.createWriteStream(zipFileName);

      // gZip
      input.pipe(gzip).pipe(output);

      return zipFileName;

    }

    getTableName()
      .then(getData)
      .then(createFile)
      .then(function(zipFile) {
        resolve(zipFile);
      });
  });
}

Obviously that works fine for a single table and I have gotten the getTableName() and createFile() methods updated to handle arrays of data so this question only pertains to the getData() method.

Cheers!



via Nicholas Kreidberg

No comments:

Post a Comment