small hack to provide sql grouping for javascript

grouping.js

//dependency: underscore.js
_ = Underscore.load();

/*function that allows generic grouping and filtering 
args:
obj: json object like this: {"data":[{"dim1":"foo","dim2":"bar",...,"measure":n},...]} (required)
dims: array of dimensions (required)
measure: one measure (required)
order: ordered array of arrays like this: 
  [dimension or measure to order,true for ascending, false for descending]
  (optional: replace by false)
filter: array of arrays of dimensions, comparator and values to be filtered for, e.g.
  [["dim1","==","foo"],["measure",">",100]] (optional: replace by false)
limit: limit output to n rows
Note: every filter needs to be included in the dims OR measures as well!
returns: array of arrays
*/


function rollUp(obj,dims,measure,order,filterVals,limit) {
  var sum = 0;
  var newObj = {"data": []};
  var tempObj = {};
  var allDims = dims.slice();
  allDims.push(measure);
  // remove duplicates: kind of a hack, TODO: find another way to do this
  for (var i = 0; i < obj.data.length; i++) {
    var id = '';
    var agg = obj["data"][i][measure];
    for (j = 0; j < dims.length; j++) {
      var raw_id = obj["data"][i][dims[j]];
      var clean_id = raw_id.replace("\t", '_');
      id = id + clean_id + "\t";
    }
    // group by
    if (tempObj[id]) {
      tempObj[id] += parseFloat(agg);
    }
    else {
      tempObj[id] = parseFloat(agg);
    }
  }
  //split temp obj into multiple objects
  Object.keys(tempObj).forEach(function(key) {
    var temp = {};
    temp[key] = tempObj[key];
    newObj["data"].push(temp);
  })
  // split distinct objects and insert into output Obj and add measure
  var finalObj = {"data": []};
  for (var i=0; i < newObj.data.length; i++) {
    var tempObj = {};
    for (var j = 0; j < dims.length; j++) {
      var dimValue = Object.keys(newObj["data"][i])[0].split("\t")[j];
      tempObj[dims[j]] = dimValue;
    }
    tempObj[measure] = _.values(newObj["data"][i])[0];
    finalObj["data"].push(tempObj);
  }
  if (filterVals) {
    finalObj = rollUpFilter(finalObj,filterVals);
  }
  if (order) {
    finalObj = rollUpSort(finalObj,order);
  }
  // convert to array of arrays and sort keys in the right order
  var superFinalObj = convertToArray(finalObj);
    if (limit) {
    superFinalObj = superFinalObj.slice(0,limit+1);
  }
  return superFinalObj;
}

function rollUpSort(obj,order) {
  for (var i = order.length - 1; i >= 0 ; i--) {
    var dim = order[i][0];
    var asc = order[i][1];
    //Logger.log(obj["data"])
    if (isNumber(obj["data"][0][dim])) {
      obj["data"].sort(function(a,b) {
        var numberA = parseFloat(a[dim]), numberB = parseFloat(b[dim]);
        if (asc == true) {
          return numberA - numberB;
        }
        else {
          return numberB - numberA;
        }
      })
    }
    else {
      obj["data"].sort(function(a,b) {
        var nameA=a[dim].toLowerCase();
        var nameB=b[dim].toLowerCase();
        if (asc == true) {
          if (nameA < nameB) //sort string ascending
            return -1;
          if (nameA > nameB)
            return 1;
          return 0; //default return value (no sorting)
        }
        else {
          if (nameA > nameB) //sort string ascending
            return -1;
          if (nameA < nameB)
            return 1;
          return 0; //default return value (no sorting)
        }
      });
    }
  }
  return obj;
}

function rollUpFilter(obj,filterVals) {
  for (var i = 0; i < filterVals.length; i++) {
    if (isNumber(filterVals[i][2])) {
      obj["data"] = obj["data"].filter(function(el) { 
        var objEl = el[filterVals[i][0]];
        var filterEl = parseFloat(filterVals[i][2]);
        if (filterVals[i][1] == "==" || filterVals[i][1] == "===") {
          return objEl == filterEl;
        }
        else if (filterVals[i][1] == ">") {
          return objEl > filterEl;
        }
        else if (filterVals[i][1] == "<") {
          return objEl < filterEl;
        }
        else if (filterVals[i][1] == "<=") {
          return objEl <= filterEl;
        } 
        else if (filterVals[i][1] == ">=") {
          return objEl >= filterEl;
        }  
      });
    }
    else {
      obj["data"] = obj["data"].filter(function(el) {
        if (filterVals[i][1] == "!==" || filterVals[i][1] == "!=") {
          return el[filterVals[i][0]] != filterVals[i][2];
        }
        else if (filterVals[i][1] == "==" || filterVals[i][1] == "===") {
          return el[filterVals[i][0]] == filterVals[i][2];
        }
      });
    }
  }
  return obj;
}

function convertToArray(convObj) {
  var finalArray = [];
  //header row
  var header = Object.keys(convObj["data"][0]);
  finalArray.push(header);
  for (var i = 0; i < convObj["data"].length; i++) {
    var tempArray = [];
    for (var j = 0; j < header.length; j++) {
      tempArray.push(convObj["data"][i][header[j]]);
    }
    finalArray.push(tempArray);
  }
  return finalArray;
}