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

Comparing version 0.1.3 to 0.1.4

package.json

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

queryBuilder.js

@@ -1,2 +0,5 @@
-module.exports.buildQuery = function (reqBody) {
+const mongoose = require('mongoose');
+// const ObjectID = require('mongodb').ObjectID;
+
+export const buildQuery = function (reqBody) {
     let {
@@ -17,8 +20,10 @@ groupKeys = [], // holds the ID of the expanded node and the ID's of all its parent/grandparent node
     const isFiltering = Object.keys(filterModel).length > 0;
-    const isGrouping = groupKeys.length > 0 || 1;
+    const isGrouping = rowGroupCols.length > 0 || 0;
     const isSorting = sortModel.length > 0;
     const limit = endRow - startRow;
+    const isAggregation = valueCols.length > 0 || 0;
 
     const filterQuery = isFiltering ? buildFilterQuery(filterModel) : [];
-    const groupQuery = isGrouping ? buildGroupingQuery(groupKeys, rowGroupCols) : null;
+    const groupQuery = isGrouping ? buildGroupingQuery(groupKeys, rowGroupCols, valueCols, pivotCols,pivotMode) : [];
+    const aggregationQuery = isAggregation ? buildAggregationQuery(valueCols) : [];
     const sortQuery = isSorting ? buildSortQuery(sortModel) : null;
@@ -32,3 +37,4 @@ const skipQuery = {'$skip': startRow};
         ...filterQuery, 
-        groupQuery, 
+        ...groupQuery, 
+        ...aggregationQuery,
         sortQuery, 
@@ -158,3 +164,4 @@ skipQuery,
      */
-    console.log('inside set filter for : ', filterModel , '   key:', key);
+    console.log('inside set filter');
+    console.log('set filter for : ', filterModel , '   key:', key);
 
@@ -178,8 +185,121 @@ if(filterModel.values.length >0)
 
-function buildGroupingQuery(groupKeys , rowGroupCols){
-    const query = groupKeys
-        .map((key, index) =>({ [rowGroupCols[index].id ] : key}));
-    
-    return {"$match": {"$and" : query}};
+function buildGroupingQuery(groupKeys , rowGroupCols, valueCols, pivotCols,pivotMode){
 
+    console.log('group keys:', groupKeys);
+    console.log('rowGroupCols :', rowGroupCols);
+    console.log('valuCols:', valueCols);
+    console.log('Pivot mode:', pivotMode);
+    console.log('Pivot cols:',pivotCols);
+
+    // 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} } ) : [{}];
+    const _finalMatchQuery= _matchQuery.length > 1 ? [{"$match": {"$and" : _matchQuery}}] : [{"$match": _matchQuery[0]}];
+
+    console.log('match query:',JSON.stringify(_matchQuery));
+    const excessIndex = rowGroupCols.length - groupKeys.length > 0 ? groupKeys.length : 0;
+
+    console.log('excess indeX:',excessIndex);
+    const constantGroupQuery = {
+        '$group': {
+            "_id": "$"+rowGroupCols[excessIndex].id+'',
+            "doc":{"$first":"$$ROOT"},
+        }
+    }
+
+
+    console.log('group query1:',JSON.stringify(constantGroupQuery));
+    const aggregationArray = buildAggregationQuery(valueCols);
+
+    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);
+
+    console.log('group query2:',JSON.stringify({"$group":_groupQuery}));
+    console.log('aggr query:',JSON.stringify(aggregateQuery));
+    console.log('final match query in grouping:',JSON.stringify(_finalMatchQuery));
+    const grouingPipelineQuery = [
+        ..._finalMatchQuery,
+        {"$group":_groupQuery},
+        {"$replaceRoot":{"newRoot":"$doc"}},
+    ];
+
+    console.log('grouping pipeline:',JSON.stringify(grouingPipelineQuery));
+    return grouingPipelineQuery;
 }
@@ -195,2 +315,41 @@
     return {'$sort': sortQuery}
-}
+}
+
+function buildAggregationQuery(valueCols){
+    if(!valueCols || valueCols== null || Object.keys(valueCols).length === 0) // if valuecols null or undefined.. just return empty array
+        return [];
+    
+    const aggrQuery=valueCols.map((aggFunction)=>{
+        if(Object.keys(aggFunction).includes('aggFunc') && aggFunction['aggFunc']){
+            return { [aggFunction.id+"_"+aggFunction.aggFunc]: aggregateQuery(aggFunction)};
+        }
+    });
+    console.log('aggrQuery array :', aggrQuery);
+    return aggrQuery;
+}
+
+function aggregateQuery(aggFunction){
+
+    console.log('inside building aggregateQuery: ',aggFunction);
+    switch (aggFunction.aggFunc) {
+        case 'sum': return {$sum : "$"+aggFunction.id+""};
+
+        case 'avg': return {$avg : "$"+aggFunction.id+""};
+
+        case 'min' : return {$min : "$"+aggFunction.id+""};
+
+        case 'max' : return {$max : "$"+aggFunction.id+""};
+
+        case 'first' : return {$first : "$"+aggFunction.id+""};
+
+        case 'last' : return {$last : "$"+aggFunction.id+""};
+
+        case 'count' : return { 'count' : {$count : { } } }; // counts each doc
+    }
+}
+
+
+
+
+
+

No alert changes

Improved metrics

Total package byte prevSize

14081

increased by75.27%

Lines of code

291

increased by77.44%

No dependency changes