sqlquerybuilder - npm Package Compare versions

Comparing version 0.0.86 to 0.0.87

lib/index.js

@@ -21,2 +21,3 @@ var uuid = require('node-uuid'), pluralizer = require('pluralizer'), _ = require('lodash');
         enumerationSuffixes: opt.enumerationSuffixes || [],
+        groupingOverrides: opt.groupingOverrides || {},
         tables: opt.tables || [],
@@ -83,3 +84,3 @@ useUtc: opt.useUtc || true,
      */
-    self._checkTableName = function checkTableName(tname) {
+    self._checkTableName = function checkTableName(tname, otherTable) {
         var fname;
@@ -101,2 +102,11 @@ if(options.sqlFunctions) {
 
+        if(otherTable){
+            if(options.groupingOverrides && options.groupingOverrides[otherTable]){
+                if(options.groupingOverrides[otherTable][tname]){
+                    tname = options.groupingOverrides[otherTable][tname];
+                }
+            }
+
+        }
+
         var tableNamematch = _.filter(options.tables, function (x) {
@@ -185,3 +195,3 @@ return x.single.toUpperCase() === tname.toUpperCase();
                     if (self._isDateTimeField(props[i]) && (foundPattern = self.slashedDateRegex.exec(values[props[i]])))
-                            values[props[i]] = values[props[i]].replace(foundPattern[0], RegExp.$3 + '-' + RegExp.$1.substring(0, 2) + '-' + RegExp.$2.substring(0, 2));
+                        values[props[i]] = values[props[i]].replace(foundPattern[0], RegExp.$3 + '-' + RegExp.$1.substring(0, 2) + '-' + RegExp.$2.substring(0, 2));
                     self._sqlObject.set += props[i] + " = '" + values[props[i]].replace(/'/g, "''") + "'";  //makes it a string
@@ -188,0 +198,0 @@ }

lib/joins.js

@@ -1,3 +0,1 @@
-var _ = require('lodash');
-
 var JOINTYPE = {
@@ -63,3 +61,5 @@ JOIN: ' JOIN ',
 
-    self._buildJoin = function (joinString, joinType, alias, joinOn, joinTo, joinWhere, retainTableName) {
+    self._buildJoin = function (joinString, joinType, alias, joinOn, joinTo, joinWhere) {
+
+
         if (!alias) {
@@ -79,2 +79,3 @@ if (joinString.toUpperCase().indexOf(" ON ") == -1) {   //if there is no 'on' then join[table] will just be the trimmed string
             joinString = self._options.entityToTableMap[joinString];
+
         }
@@ -86,20 +87,11 @@
         if (self._sqlObject.joins.join.indexOf(joinString) === -1 || (alias && self._sqlObject.joins.joinAlias.indexOf(alias) === -1)) {
+
             var foreignkeyname;
-            // Check the Entity To Table Map if there is no joinOn
+            // Check the Entity To Tabel Map if there is no joinOn
             if (!joinOn) {
-                if (retainTableName) {
-                    var tableNamematch = _.filter(self._options.tables, function (x) {
-                        return x.plural.toUpperCase() === joinString.toUpperCase();
-                    });
-                    if (tableNamematch.length > 0)
-                        foreignkeyname = tableNamematch[0].single;
-                    else
-                        foreignkeyname = joinString;
-                    foreignkeyname += '_id';
-                }
-                else {
-                    for (var key in self._options.entityToTableMap) {
-                        if (self._options.entityToTableMap[key] === joinString) {
-                            foreignkeyname = key + '_id';
-                        }
+
+                for (var key in self._options.entityToTableMap) {
+                    if (self._options.entityToTableMap[key] === joinString) {
+                        foreignkeyname = key + '_id';
+
                     }
@@ -116,2 +108,3 @@ }
             self._sqlObject.joins.joinWhere.push(joinWhere);
+
         }
@@ -150,5 +143,5 @@
      */
-    self.leftJoin = function leftJoin(join, joinOn, joinTo, retainTableName) {
+    self.leftJoin = function leftJoin(join, joinOn, joinTo) {
 
-        return self._buildJoin(join, JOINTYPE.LEFTJOIN, undefined, joinOn, joinTo, undefined, retainTableName);
+        return self._buildJoin(join, JOINTYPE.LEFTJOIN, undefined, joinOn, joinTo);
 
@@ -263,14 +256,14 @@ };
                 var table = self._sqlObject.joins.join[i].substring(index + 5,
-                                                                    Math.min(parenthesis,
-                                                                             (parenthesis > 0 && space > 0) ?
-                                                                                 Math.min(parenthesis, space)
-                                                                               : Math.max(parenthesis, space)
-                                                                    )
+                    Math.min(parenthesis,
+                        (parenthesis > 0 && space > 0) ?
+                            Math.min(parenthesis, space)
+                            : Math.max(parenthesis, space)
+                    )
                 );
                 joinString += self._sqlObject.fromAlias + "." + self._depluralize(table) +                              //assumes the join will be on Id's
-                    "_id = " + joinTable + ".Id";
+                "_id = " + joinTable + ".Id";
             }
             else {                                                                                                      //only 1 parameter. join on Id's is assumed
                 joinString += self._sqlObject.fromAlias + "." + self._depluralize(self._sqlObject.joins.join[i]) +
-                    "_id = " + joinTable + ".Id";
+                "_id = " + joinTable + ".Id";
             }
@@ -277,0 +270,0 @@

lib/selects.js

@@ -95,3 +95,5 @@ module.exports = function selects(self) {
 
-                    } else if (self._options.enumerationSuffixes.indexOf(nameParts[1]) !== -1) {
+                    } else if(self._options.groupingOverrides && self._options.groupingOverrides[self._sqlObject.from] && self._options.groupingOverrides[self._sqlObject.from][nameParts[0]]){
+                        tableName = self._options.groupingOverrides[self._sqlObject.from][nameParts[0]] + '.' + nameParts[1];
+                    }else if (self._options.enumerationSuffixes.indexOf(nameParts[1]) !== -1) {
                         tableName = nameParts[0] + nameParts[1];
@@ -135,3 +137,3 @@
                 if (props[i].indexOf('.') !== -1 && shouldMakeJoin(props[i])) {
-                    var table = self._checkTableName(props[i].split('.')[0]);
+                    var table = self._checkTableName(props[i].split('.')[0], self._sqlObject.from);
 
@@ -142,3 +144,13 @@ if (!self._sqlObject.from) {
                         if (table != self._sqlObject.from) {
-                            self.leftJoin(table, null, null, retainTableName);
+                            var joinClause;
+                            if(self._options.groupingOverrides && self._options.groupingOverrides[self._sqlObject.from]){
+                                for(var key in self._options.groupingOverrides[self._sqlObject.from]){
+                                    if(self._options.groupingOverrides[self._sqlObject.from].hasOwnProperty(key)){
+                                        if(self._options.groupingOverrides[self._sqlObject.from][key] == table){
+                                            joinClause = key + "_id";
+                                        }
+                                    }
+                                }
+                            }
+                            self.leftJoin(table, joinClause, null, retainTableName);
                         }
@@ -156,2 +168,2 @@ }
     return self;
-};
+};

package.json

 {
   "name": "sqlquerybuilder",
-  "version": "0.0.86",
+  "version": "0.0.87",
   "description": "Highly opinionated Sql Server Query Writer, mostly for internal use.",
@@ -5,0 +5,0 @@ "main": "./lib/index",

test/unit/grouping/groupByTest.js

@@ -12,2 +12,9 @@ /**
                 "CustomerType": "AccountingProcessTypes"
+            }, groupingOverrides: {
+                'PurchaseOrders':{
+                    'Status':'StatusDisplayName',
+                    'ApprovalStatus':'ApprovalStatusDisplayName',
+                    "Location":"AccountLocations",
+                    'Buyer':'Users'
+                }
             },
@@ -113,3 +120,22 @@ enumerationSuffixes: ['DisplayName', 'Value'],
     });
+
+    it('Should create the propery query with table specific overrides', function(done){
+var reqquery = { groupby: 'Location.Name',
+    operations: '',
+    sidx: 'Location.Name',
+    sord: 'asc',
+    RenderFormat: 'paged',
+    page: '',
+    pagePercent: '0',
+    total: '',
+    rows: '100',
+    totalPages: '' };
+
+        var qObj = { 'AccountLocations.Name': 'Location.Name',
+            'count(AccountLocations.Name)': 'Count' };
+        var query = sqlBuilder().from('PurchaseOrders').selectJoin(qObj).groupBy('AccountLocations.Name').processListRequest(reqquery).build();
+        query.should.equal("WITH SelectedItems AS (SELECT AccountLocations.Name AS 'Location.Name', COUNT(AccountLocations.Name) AS 'Count', ROW_NUMBER() OVER (ORDER BY AccountLocations.Name ASC) AS Position FROM PurchaseOrders LEFT JOIN AccountLocations ON PurchaseOrders.Location_id = AccountLocations.Id GROUP BY AccountLocations.Name) SELECT *, (Select MAX(Position) From SelectedItems) as 'TotalRows' FROM SelectedItems WHERE Position > 0 AND Position <= 100 ");
+        done();
+    });
 });
 

No alert changes

Improved metrics

Total package byte prevSize

255610

increased by0.74%

Lines of code

5137

increased by0.61%

No dependency changes