ag-grid-mongo-query-builder - npm Package Compare versions

Comparing version 0.2.1 to 0.2.2

index.js

@@ -9,2 +9,6 @@ const {buildQuery, buildCountQuery} = require('./queryBuilder.js');
     return buildCountQuery(params);
+}
+
+module.exports.getPivotCols = function(params, data) {
+    return getPivotColumns(params, data);
 }

package.json

 {
   "name": "ag-grid-mongo-query-builder",
-  "version": "0.2.1",
+  "version": "0.2.2",
   "description": "Utility to generate Mongo DB aggregation pipeline queries starting from AgGrid server side params",
@@ -5,0 +5,0 @@ "main": "index.js",

queryBuilder.js

@@ -32,2 +32,3 @@ //const mongoose = require('mongoose');
     const isAggregation = valueCols.length > 0 || 0;
+    var pivotActive = pivotMode && pivotCols.length > 0;
 
@@ -52,3 +53,4 @@ const filterQuery = isFiltering ? buildFilterQuery(filterModel) : [];
 
-    const groupQuery = isGrouping ? buildGroupingQuery(groupKeys, rowGroupCols, valueCols, pivotCols,pivotMode) : [];
+    const groupQuery = !pivotActive && isGrouping ? buildGroupingQuery(groupKeys, rowGroupCols, valueCols, pivotCols,pivotMode) : [];
+    const pivotQuery = pivotActive ? buildPivotQuery(groupKeys, rowGroupCols, valueCols, pivotCols) : {query: [], pivotColumns: []};    
     //const aggregationQuery = isAggregation ? buildAggregationQuery(valueCols) : [];
@@ -62,6 +64,9 @@ const sortQuery = isSorting ? buildSortQuery(sortModel) : null;
     console.log('Group query:',JSON.stringify(groupQuery));
+    console.log('pivotQuery query:',JSON.stringify(pivotQuery));
     console.log('final filter query', JSON.stringify(finalFilterQuery));
+    const pivotColumnsPipeline = pivotQuery && pivotQuery.pivotColumnsPipeline ? pivotQuery.pivotColumnsPipeline: []
     const aggregationPipeline = [
         ...finalFilterQuery, 
         ...groupQuery, 
+        ...pivotQuery.query,
         sortQuery, 
@@ -72,5 +77,6 @@ skipQuery,
 
-    console.log('Final pipeline: ', JSON.stringify(aggregationPipeline))
+    console.log('Final pipeline: ', JSON.stringify(aggregationPipeline));
+    console.log('Final pipeline pivotColumnsPipeline: ', JSON.stringify(pivotColumnsPipeline));
 
-    return aggregationPipeline;
+    return {aggregationPipeline, pivotColumnsPipeline};
 
@@ -99,2 +105,3 @@ }
     const isGrouping = rowGroupCols.length > 0 || 0;
+    var pivotActive = pivotMode && pivotCols.length > 0;
     const isAggregation = valueCols.length > 0 || 0;
@@ -120,3 +127,4 @@
     }
-    const groupQuery = isGrouping ? buildGroupingQuery(groupKeys, rowGroupCols, valueCols, pivotCols,pivotMode) : [];
+    const groupQuery = !pivotActive && isGrouping ? buildGroupingQuery(groupKeys, rowGroupCols, valueCols, pivotCols,pivotMode) : [];
+    const pivotQuery = pivotActive ? buildPivotQuery(groupKeys, rowGroupCols, valueCols, pivotCols) : {query: [], pivotColumns: []};    
     //const aggregationQuery = isAggregation ? buildAggregationQuery(valueCols) : [];
@@ -127,5 +135,8 @@ const countQuery = {$count: "totalRows"};
     console.log('Group query:',JSON.stringify(groupQuery));
+    console.log('Pivot query:',JSON.stringify(pivotQuery.query));
+    const pivotColumns = pivotQuery && pivotQuery.pivotColumns ? pivotQuery.pivotColumns: []
     const aggregationPipeline = [
         ...finalFilterQuery, 
         ...groupQuery, 
+        ...pivotQuery.query,
         countQuery
@@ -326,22 +337,2 @@ ].filter(stage => !!stage)
 
-    // there is a match step if groupKeys.length > 0
-   
-
-    // there is always a grouping step 
-    //    the grouping step can be with or without aggregation depending of valueCols.lengh > 0
-
-    // final query looks something like this:
-    // [
-    //  {$match: {}},   //consition: empty if groupKeys.length = 0 otherwise it matches with keys
-    //  {$group: {
-    //      "_id": "$"+rowGroupCols[excessIndex].id+'',
-    //      "doc":{"$first":"$$ROOT"},
-    //      <field1>: { <accumulator1> : <expression1> },
-    //      <field1>: { <accumulator1> : <expression1> } // as many times as many items in valueCols otherwise nothing
-    //      
-    //  }},
-    // {"$replaceRoot":{"newRoot":"$doc"}}  
-    //]
-
-
     const _matchQuery = groupKeys.length ? groupKeys.map((key, index) => { return {[rowGroupCols[index].id ] : key} } ) : [{}];
@@ -366,62 +357,3 @@ const _finalMatchQuery= _matchQuery.length > 1 ? [{"$match": {"$and" : _matchQuery}}] : [{"$match": _matchQuery[0]}];
     console.log('group query2:',JSON.stringify(aggregationArray));
-    if(pivotMode)
-    {
-        //build pivot Query 
-        // pivotCOls, pivot mode.. query for pivoting is :
-
-        // {
-        //     $group: {
-        //         _id: "$field2",      //rowGroupCols[0]
-        //         field1_counts: {
-        //             $addToSet: {
-        //                 k: "$field1",  // PivotCols[0]
-        //                 v: { $sum: 1 } // get this calling aggr function : sum  avg max min etc.
-        //             }
-        //         }
-        //     }
-        // },
-        // {
-        //     $project: {
-        //         _id: 0,
-        //         field2: "$_id",
-        //         field1_counts: {
-        //             $arrayToObject: "$field1_counts"
-        //         }
-        //     }
-        // }
-
-            let tempGroup=[
-                            {
-                                "$group": {
-                                    "_id": "$"+rowGroupCols[excessIndex].id+'',
-                                    "field1_aggr": {
-                                        "$addToSet": {
-                                           // k: { $concat: ["$"+ pivotCols[0].id+""] },
-                                            k: "$"+ pivotCols[0].id+"",  // PivotCols[0]
-                                            v : aggregationArray
-                                            // v: { $sum: 1 } // get this calling aggr function : sum  avg max min etc.
-                                        }
-                                    },
-                                    // "doc":{"$first":"$$ROOT"}
-                                }
-                            },                   
-                            {
-                                "$project": {
-                                    // _id: 0,
-                                    // [rowGroupCols[0].id+'']: "$_id",
-                                    "field1_aggr": {
-                                        "$arrayToObject": "$field1_aggr"
-                                    }
-                                }
-                            }
-                        ];
-                    console.log('temp group query with pivot:', JSON.stringify(tempGroup));   
-                   
-                    return [
-                        ..._finalMatchQuery,
-                        ...tempGroup,
-                        {"$replaceRoot":{"newRoot":"$field1_aggr"}},
-                    ];
-    }
-
+    
     const _groupQuery = Object.assign(constantGroupQuery['$group'], constantGroupQuery['$group'], ...aggregationArray);
@@ -450,6 +382,2 @@
 
-    // console.log('===============excessIndex');
-    // console.log(excessIndex);
-    // console.log(rowGroupCols.length);
-    // console.log(groupKeys.length);
 
@@ -461,7 +389,254 @@ if(rowGroupCols.length - groupKeys.length <= 0)  {
     }
+}
 
-    // console.log('grouping pipeline:',JSON.stringify(grouingPipelineQuery));
-    // return grouingPipelineQuery;
+
+function buildPivotQuery(groupKeys , rowGroupCols, valueCols, pivotCols) {
+
+    const excessIndex = rowGroupCols.length - groupKeys.length > 0 ? groupKeys.length : 0;
+    const _matchQuery = groupKeys.length ? groupKeys.map((key, index) => { return {[rowGroupCols[index].id ] : key} } ) : [{}];
+    const _finalMatchQuery= _matchQuery.length > 1 ? [{"$match": {"$and" : _matchQuery}}] : [{"$match": _matchQuery[0]}];
+    const aggregationArray = buildAggregationQuery(valueCols);
+    
+    let tempGroup = null;
+    let colsPipeline = null;
+    if(rowGroupCols.length > 0) {
+        tempGroup = getPivotPipeline(pivotCols, rowGroupCols,aggregationArray, excessIndex, true);
+        colsPipeline = getPivotColumnsPipeline(pivotCols);
+    } else {
+        tempGroup = getPivotPipeline(pivotCols, rowGroupCols,aggregationArray, excessIndex, false);
+        colsPipeline = getPivotColumnsPipeline(pivotCols);
+
+    }
+    
+    const pivotColumnsPipeline = [
+        ...colsPipeline.pipeline,
+        {"$replaceRoot": colsPipeline.replaceRootObj}
+    ];
+
+    const query = [
+        ..._finalMatchQuery,
+        ...tempGroup.pipeline,
+        {"$replaceRoot": tempGroup.replaceRootObj},
+        {"$project" : tempGroup.exclusionProject}
+    ];
+    return {  query, pivotColumnsPipeline };
+    
 }
 
+function getPivotPipeline(pivotCols, rowGroupCols, aggregationArray, excessIndex, isGrouping) {
+
+    let replaceRootObj = {"newRoot" : "$$ROOT"};
+    let exclusionProject = {"_id": 0};
+    let addFieldObj = {};
+    let groupingFields = { };
+    let groupingFields2 = { "_id": null};
+    let aggObjName = "field_aggr";
+    let condObj = {};
+    let condObj2 = {};
+    let projectObj = {};
+    let counter = 1;
+    let mergedRoot = ["$$ROOT"];
+
+    if(isGrouping) {
+        groupingFields[rowGroupCols[excessIndex].id] = "$"+rowGroupCols[excessIndex].id;
+        groupingFields2[rowGroupCols[excessIndex].id] = "$_id."+rowGroupCols[excessIndex].id;
+    } else {
+        projectObj["childCount"] = 1;
+    }
+    let pivotFullPath = [];
+    pivotCols.map(eachPivotField => {
+        groupingFields[eachPivotField.id] = "$"+eachPivotField.id;
+        pivotFullPath.push(`$_id.${eachPivotField.id}`);
+        pivotFullPath.push(`|`);
+    });
+
+    if(!isGrouping) {
+        condObj['childCount'] = {"$sum" : 1};
+        condObj2['childCount'] = { "$addToSet": { "k": "childCount", "v": "$childCount" }};
+        projectObj["childCount"] = 1;
+        projectObj['childCount'] = {"$arrayToObject": `$childCount` };
+        mergedRoot.push('$childCount');
+    } 
+    
+
+    if(isGrouping) {
+        condObj2['groupingField'] = { "$addToSet": { "k": `${rowGroupCols[excessIndex].id}`, "v": `$_id.${rowGroupCols[excessIndex].id}` }};
+        projectObj['groupingField'] = {"$arrayToObject": `$groupingField` };
+        exclusionProject['groupingField'] = 0;
+        mergedRoot.push('$groupingField');
+    }
+
+    const childSumArrays = [];
+    if(aggregationArray.length > 0) {
+        aggregationArray.map(eachObj => {
+            const key= Object.keys(eachObj)[0];
+            const value= Object.values(eachObj);
+            let elementPath = [];
+            elementPath = [...pivotFullPath];
+            elementPath.push(key);
+            condObj[`${aggObjName}${counter}`] = value[0];
+            condObj2[`${aggObjName}${counter}`] =  { "$push": { k: {"$concat" : elementPath},v : `$${aggObjName}${counter}`}};
+            projectObj[`${aggObjName}${counter}`] = {"$arrayToObject": `$${aggObjName}${counter}` };
+            if(isGrouping) childSumArrays.push(`$${aggObjName}${counter}.v`);
+            exclusionProject[`${aggObjName}${counter}`] = 0;
+            mergedRoot.push(`$${aggObjName}${counter}`);
+            counter = counter + 1
+        });
+    } 
+
+    if(childSumArrays.length > 0 && isGrouping) projectObj = {"childCount": {"$sum":  {"$concatArrays" : [...childSumArrays] }} , ...projectObj};
+    addField = {"$addFields" : addFieldObj};
+    replaceRootObj.newRoot = mergedRoot.length >0? { "$mergeObjects" : mergedRoot}: "$$ROOT" ;
+
+    
+    return {"pipeline": [
+        {
+            "$group": {
+                "_id": groupingFields,
+                ...condObj
+            }
+        },  
+        {
+            "$group": {
+                "_id": groupingFields2,
+                ...condObj2
+            }
+        },                 
+        {
+            "$project": {
+                ...projectObj
+            }
+        }
+    ], "replaceRootObj": replaceRootObj, "exclusionProject": exclusionProject};
+
+
+}
+
+function getPivotColumnsPipeline(pivotCols) {
+
+    
+    let replaceRootObj = {"newRoot" : "$$ROOT"};
+    let addFieldObj = {};
+    let addField = null;
+    let groupingFields = { };
+    let pivotFullPath = [];
+    let sortObj = {};
+    pivotCols.map(eachPivotField => {
+        groupingFields[eachPivotField.id] = "$"+eachPivotField.id;
+        pivotFullPath.push(`$_id.${eachPivotField.id}`);
+        pivotFullPath.push(`|`);
+        addFieldObj[`${eachPivotField.id}`] = "$_id."+eachPivotField.id;
+        sortObj[`${eachPivotField.id}`] = 1;
+    });
+
+    addField = {"$addFields" : addFieldObj};
+    return {"pipeline": [
+        {
+            "$group": {
+                "_id": groupingFields,
+            }
+        },
+        addField,
+        {"$sort" : sortObj}               
+    ], "replaceRootObj": replaceRootObj};
+}
+
+function getPivotColumns(reqBody, data) {
+
+    let {
+        rowGroupCols = [],
+        valueCols = [] , // required for aggregation
+        pivotCols= [],
+    } = reqBody;
+
+    var pivotData = [];
+    var aggColsList = [];
+
+    var colKeyExistsMap = {};
+    var pivotResultColDefs = [];
+    var pivotResultColDefsMap = {};
+
+    data.forEach(function (item) {
+      var pivotValues = [];
+      pivotCols.forEach(function (pivotCol) {
+        var pivotField = pivotCol.id;
+        var pivotValue = item[pivotField];
+        if (
+          pivotValue !== null &&
+          pivotValue !== undefined &&
+          pivotValue.toString
+        ) {
+          pivotValues.push(pivotValue.toString());
+        } else {
+          pivotValues.push('-');
+        }
+      });
+
+      var pivotItem = {};
+
+      valueCols.forEach(function (valueCol) {
+        var valField = valueCol.id;
+        var colKey = createColKey(pivotValues, valField);
+
+        var value = item[valField];
+        pivotItem[colKey] = value;
+
+        if (!colKeyExistsMap[colKey]) {
+          addNewPivotResultColDef(colKey, pivotValues, valueCol);
+          colKeyExistsMap[colKey] = true;
+        }
+      });
+
+      rowGroupCols.forEach(function (rowGroupCol) {
+        var rowGroupField = rowGroupCol.id;
+        pivotItem[rowGroupField] = item[rowGroupField];
+      });
+
+      pivotData.push(pivotItem);
+    });
+
+    function addNewPivotResultColDef(colKey, pivotValues, valueCol) {
+      var parentGroup = null;
+
+      var keyParts = [];
+
+      pivotValues.forEach(function (pivotValue) {
+        keyParts.push(pivotValue);
+        var colKey = createColKey(keyParts);
+        var groupColDef = pivotResultColDefsMap[colKey];
+        if (!groupColDef) {
+          groupColDef = {
+            groupId: colKey,
+            headerName: pivotValue,
+            children: [],
+          };
+          pivotResultColDefsMap[colKey] = groupColDef;
+          if (parentGroup) {
+            parentGroup.children.push(groupColDef);
+          } else {
+            pivotResultColDefs.push(groupColDef);
+          }
+        }
+        parentGroup = groupColDef;
+      });
+
+    parentGroup.children.push({
+        colId: colKey,
+        headerName: valueCol.aggFunc + '(' + valueCol.displayName + ')',
+        field: colKey,
+      });
+    }
+
+    function createColKey(pivotValues, valueField) {
+      var result = pivotValues.join('|');
+      if (valueField !== undefined) {
+        result += '|' + valueField;
+      }
+      return result;
+    }
+
+    return { pivotResultColDefs: pivotResultColDefs};
+}
+
+
 function buildSortQuery(sortModel){
@@ -534,3 +709,3 @@ const sortQuery = sortModel.reduce((sortQuery, val) => {
 
-        case 'count' : return  {$count : { } } ; // counts each doc
+        case 'count' : return  {$sum : 1 } ; // counts each doc
     }
@@ -542,4 +717,5 @@ }
 module.exports.buildCountQuery=buildCountQuery;
+module.exports.getPivotColumns=getPivotColumns;
 
 
 

No alert changes

Improved metrics

Total package byte prevSize

26970

increased by25.98%

Lines of code

601

increased by32.09%

No dependency changes