sqlquerybuilder - npm Package Compare versions

Comparing version 0.0.14 to 0.0.15

lib/sqlQueryBuilder.js

@@ -21,2 +21,10 @@ var uuid = require('node-uuid'), pluralizer = require('pluralizer'), _ = require('lodash');
 
+var JOINTYPE = {
+    JOIN: ' JOIN ',
+    LEFTJOIN: ' LEFT JOIN ',
+    RIGHTJOIN: ' RIGHT JOIN ',
+    FULLOUTERJOIN: ' FULL OUTER JOIN ',
+    LEFTOUTERJOIN: ' LEFT OUTER JOIN '
+};
+
 function QueryBuilder() {
@@ -101,2 +109,3 @@ var self = this;
 
+
         var fname;
@@ -112,2 +121,6 @@ options.sqlFunctions.forEach(function (f) {
 
+        if (options.entityToTableMap[tname]) {
+            tname = options.entityToTableMap[tname];
+        }
+
         var tableNamematch = _.filter(options.tables, function (x) {
@@ -137,4 +150,9 @@ return x.single.toUpperCase() === tname.toUpperCase();
                     var nameParts = tableName.split('.');
-                    if (options.enumerationSuffixes.indexOf(nameParts[1]) !== -1) {
+
+                    if (options.entityToTableMap[nameParts[0]]) {
+                        tableName = options.entityToTableMap[nameParts[0]] + '.' + nameParts[1];
+
+                    } else if (options.enumerationSuffixes.indexOf(nameParts[1]) !== -1) {
                         tableName = nameParts[0] + nameParts[1];
+
                     } else {
@@ -145,2 +163,5 @@ var tname = checkTableName(nameParts[0]);
                     }
+                    _selectProps.push(tableName);
+                } else {
+                    _selectProps.push(props[i]);
                 }
@@ -150,3 +171,3 @@
 
-                _selectProps.push(props[i]);
+
                 var aliases = selObj[props[i]];
@@ -161,2 +182,4 @@ if (aliases !== null && aliases !== "") {
                     }
+                } else {
+                    _selectAliases.push(props[i]);
                 }
@@ -172,3 +195,2 @@ if (i != props.length - 1) _sqlObject.select += ", ";
                         if (table != _sqlObject.from) {
-
                             self.join(table);
@@ -200,21 +222,65 @@ }
 
-    self.join = function join(joinString) {
-        var alias = null;
-        if (joinString.toUpperCase().indexOf(" ON ") == -1) {   //if there is no 'on' then join[table] will just be the trimmed string
-            joinString = joinString.trim();
+    /**
+     * Sets up a join
+     * @param joinString
+     * @param {string} [joinOn=undefined] - JoinOn clause
+     * @param {string} [joinTo=undefined] - JoinTo clause
+     * @returns {*}
+     */
+    self.join = function join(joinString, joinOn, joinTo) {
+       return self._buildJoin(joinString, JOINTYPE.JOIN, undefined, joinOn, joinTo);
+    };
 
-            if (joinString.indexOf(" ") != -1) {       //if it has a space then split up and set join table and join alias
-                alias = joinString.substring(joinString.indexOf(" ") + 1, joinString.length);
-                joinString = joinString.substring(0, joinString.indexOf(" "));
+    /**
+     *  Pushes join objects into the join arrays for building query later
+     * @param join
+     * @param joinType use JOINTYPE
+     * @param joinAlias
+     * @param joinOn
+     * @param joinTo
+     * @returns {QueryBuilder}
+     * @private
+     */
+    self._setJoinProperties = function (join, joinType, joinAlias, joinOn, joinTo) {
+        _sqlObject.joins.join.push(join);
+        _sqlObject.joins.joinType.push(joinType);
+        _sqlObject.joins.joinAlias.push(joinAlias);
+        _sqlObject.joins.joinOn.push(joinOn);
+        _sqlObject.joins.joinTo.push(joinTo);
+
+        return self;
+    };
+
+    self._buildJoin = function (joinString, joinType, alias, joinOn, joinTo) {
+
+        if(!alias) {
+            if (joinString.toUpperCase().indexOf(" ON ") == -1) {   //if there is no 'on' then join[table] will just be the trimmed string
+                joinString = joinString.trim();
+
+                if (joinString.indexOf(" ") != -1) {       //if it has a space then split up and set join table and join alias
+                    alias = joinString.substring(joinString.indexOf(" ") + 1, joinString.length);
+                    joinString = joinString.substring(0, joinString.indexOf(" "));
+                }
             }
         }
-
         // Just check if the join is in the joins already ...or if it's in the join then it has a different alias
         if (_sqlObject.joins.join.indexOf(joinString) === -1 || (alias && _sqlObject.joins.joinAlias.indexOf(alias) === -1)) {
+
+            var foreignkeyname;
+            // Check the Entity To Tabel Map if there is no joinOn
+            if(!joinOn) {
+                for (var key in options.entityToTableMap) {
+                    if (options.entityToTableMap[key] === joinString) {
+                        foreignkeyname = key + '_id';
+
+                    }
+                }
+            }
+
             _sqlObject.joins.join.push(joinString);
-            _sqlObject.joins.joinType.push(" JOIN ");
+            _sqlObject.joins.joinType.push(joinType);
             _sqlObject.joins.joinAlias.push(alias);
 
-            _sqlObject.joins.joinOn.push(arguments['1']);
-            _sqlObject.joins.joinTo.push(arguments['2']);
+            _sqlObject.joins.joinOn.push(foreignkeyname || joinOn);
+            _sqlObject.joins.joinTo.push(joinTo);
         }
@@ -225,145 +291,97 @@
 
-    self.joinSubQuery = function joinSubQuery(query, alias, criteria) {
-        _sqlObject.joins.join.push("(" + query + ")" + (criteria ? " " + alias + " ON " + criteria : ""));
-        _sqlObject.joins.joinType.push(" JOIN ");
+    self._buildSubQueryJoin = function (query, alias, criteria, jointype) {
         if (!alias) console.error("joinSubQuery requires 2 parameters, but only 1 was given. Please add the subQuery alias as the 2nd parameter");
-        _sqlObject.joins.joinAlias.push(alias);
-        _sqlObject.joins.joinOn.push(undefined);
-        _sqlObject.joins.joinTo.push(undefined);
+        return self._setJoinProperties("(" + query + ")" + (criteria ? " " + alias + " ON " + criteria : ""), jointype, alias, undefined, undefined);
+    };
 
-        return self;
+    self.joinSubQuery = function joinSubQuery(query, alias, criteria) {
+        return self._buildSubQueryJoin(query, alias, criteria, JOINTYPE.JOIN);
     };
 
-    self.joinAs = function joinAs(join, alias) {
-        _sqlObject.joins.join.push(join);
-        _sqlObject.joins.joinAlias.push(alias);
-        _sqlObject.joins.joinType.push(" JOIN ");
-
-        _sqlObject.joins.joinOn.push(arguments['2']);
-        _sqlObject.joins.joinTo.push(arguments['3']);
-
-        return self;
+    /**
+     * Sets up a join
+     * @param joinString
+     * @param alias - alias of join table
+     * @param {string} [joinOn=undefined] - JoinOn clause
+     * @param {string} [joinTo=undefined] - JoinTo clause
+     * @returns {*}
+     */
+    self.joinAs = function joinAs(join, alias, joinOn, joinTo) {
+        return self._buildJoin(join, JOINTYPE.JOIN, alias, joinOn, joinTo);
     };
 
-    self.leftJoin = function leftJoin(join) {
-        var alias = null;
-        if (join.toUpperCase().indexOf(" ON ") == -1) {
-            join = join.trim();
+    /**
+     * Sets up a join
+     * @param joinString
+     * @param {string} [joinOn=undefined] - JoinOn clause
+     * @param {string} [joinTo=undefined] - JoinTo clause
+     * @returns {*}
+     */
+    self.leftJoin = function leftJoin(join, joinOn, joinTo) {
 
-            if (join.indexOf(" ") != -1) {
-                alias = join.substring(join.indexOf(" ") + 1, join.length);
-                join = join.substring(0, join.indexOf(" "));
-            }
-        }
-        _sqlObject.joins.join.push(join);
-        _sqlObject.joins.joinType.push(" LEFT JOIN ");
-        _sqlObject.joins.joinAlias.push(alias);
+        return self._buildJoin(join, JOINTYPE.LEFTJOIN, undefined, arguments['1'], arguments['2']);
 
-        _sqlObject.joins.joinOn.push(arguments['1']);
-        _sqlObject.joins.joinTo.push(arguments['2']);
-
-        return self;
     };
 
     self.leftJoinAs = function leftJoinAs(join, alias) {
-        _sqlObject.joins.join.push(join);
-        _sqlObject.joins.joinAlias.push(alias);
-        _sqlObject.joins.joinType.push(" LEFT JOIN ");
-
-        _sqlObject.joins.joinOn.push(arguments['2']);
-        _sqlObject.joins.joinTo.push(arguments['3']);
-
-        return self;
+        return self._buildJoin(join, JOINTYPE.LEFTJOIN, alias, arguments['2'], arguments['3']);
     };
 
     self.leftJoinSubQuery = function leftJoinSubQuery(query, alias, criteria) {
-        _sqlObject.joins.join.push("(" + query + ")" + (criteria ? " " + alias + " ON " + criteria : ""));
-        _sqlObject.joins.joinType.push(" LEFT JOIN ");
-        if (!alias) console.error("joinSubQuery requires 2 parameters, but only 1 was given. Please add the subQuery alias as the 2nd parameter");
-        _sqlObject.joins.joinAlias.push(alias);
-        _sqlObject.joins.joinOn.push(undefined);
-        _sqlObject.joins.joinTo.push(undefined);
-        return self;
+        return self._buildSubQueryJoin(query, alias, criteria, JOINTYPE.LEFTJOIN);
     };
 
-    self.rightJoin = function rightJoin(join) {
-        var alias = null;
-        if (join.toUpperCase().indexOf(" ON ") == -1) {
-            join = join.trim();
+    /**
+     * Sets up a join
+     * @param joinString
+     * @param {string} [joinOn=undefined] - JoinOn clause
+     * @param {string} [joinTo=undefined] - JoinTo clause
+     * @returns {*}
+     */
+    self.rightJoin = function rightJoin(join, joinOn, joinTo) {
 
-            if (join.indexOf(" ") != -1) {
-                alias = join.substring(join.indexOf(" ") + 1, join.length);
-                join = join.substring(0, join.indexOf(" "));
-            }
-        }
-        _sqlObject.joins.join.push(join);
-        _sqlObject.joins.joinAlias.push(alias);
-        _sqlObject.joins.joinType.push(" RIGHT JOIN ");
-
-        _sqlObject.joins.joinOn.push(arguments['1']);
-        _sqlObject.joins.joinTo.push(arguments['2']);
-
-        return self;
+        return self._buildJoin(join, JOINTYPE.RIGHTJOIN, undefined, joinOn, joinTo);
     };
 
-    self.rightJoinAs = function rightJoinAs(join, alias) {
-        _sqlObject.joins.join.push(join);
-        _sqlObject.joins.joinAlias.push(alias);
-        _sqlObject.joins.joinType.push(" RIGHT JOIN ");
-
-        _sqlObject.joins.joinOn.push(arguments['2']);
-        _sqlObject.joins.joinTo.push(arguments['3']);
-
-        return self;
+    /**
+     * Sets up a join
+     * @param joinString
+     * @param alias - alias of join table
+     * @param {string} [joinOn=undefined] - JoinOn clause
+     * @param {string} [joinTo=undefined] - JoinTo clause
+     * @returns {*}
+     */
+    self.rightJoinAs = function rightJoinAs(join, alias, joinOn, joinTo) {
+        return self._buildJoin(join, JOINTYPE.RIGHTJOIN, alias, joinOn, joinTo);
     };
 
     self.rightJoinSubQuery = function rightJoinSubQuery(query, alias, criteria) {
-        _sqlObject.joins.join.push("(" + query + ")" + (criteria ? " " + alias + " ON " + criteria : ""));
-        _sqlObject.joins.joinType.push(" RIGHT JOIN ");
-        if (!alias) console.error("joinSubQuery requires 2 parameters, but only 1 was given. Please add the subQuery alias as the 2nd parameter");
-        _sqlObject.joins.joinAlias.push(alias);
-        _sqlObject.joins.joinOn.push(undefined);
-        _sqlObject.joins.joinTo.push(undefined);
-        return self;
+        return self._buildSubQueryJoin(query, alias, criteria, JOINTYPE.RIGHTJOIN);
     };
 
-    self.fullOuterJoin = function fullOuterJoin(join) {
-        var alias = null;
-        if (join.toUpperCase().indexOf(" ON ") == -1) {
-            join = join.trim();
-
-            if (join.indexOf(" ") != -1) {
-                alias = join.substring(join.indexOf(" ") + 1, join.length);
-                join = join.substring(0, join.indexOf(" "));
-            }
-        }
-        _sqlObject.joins.join.push(join);
-        _sqlObject.joins.joinAlias.push(alias);
-        _sqlObject.joins.joinType.push(" FULL OUTER JOIN ");
-
-        _sqlObject.joins.joinOn.push(arguments['1']);
-        _sqlObject.joins.joinTo.push(arguments['2']);
-
-        return self;
+    /**
+     * Sets up a join
+     * @param joinString
+     * @param {string} [joinOn=undefined] - JoinOn clause
+     * @param {string} [joinTo=undefined] - JoinTo clause
+     * @returns {*}
+     */
+    self.fullOuterJoin = function fullOuterJoin(join, joinOn, joinTo) {
+        return self._buildJoin(join, JOINTYPE.FULLOUTERJOIN, undefined, joinOn, joinTo);
     };
 
-    self.fullOuterJoinAs = function fullOuterJoinAs(join, alias) {
-        _sqlObject.joins.join.push(join);
-        _sqlObject.joins.joinAlias.push(alias);
-        _sqlObject.joins.joinType.push(" FULL OUTER JOIN ");
-
-        _sqlObject.joins.joinOn.push(arguments['2']);
-        _sqlObject.joins.joinTo.push(arguments['3']);
-
-        return self;
+    /**
+     * Sets up a join
+     * @param joinString
+     * @param alias - alias of join table
+     * @param {string} [joinOn=undefined] - JoinOn clause
+     * @param {string} [joinTo=undefined] - JoinTo clause
+     * @returns {*}
+     */
+    self.fullOuterJoinAs = function fullOuterJoinAs(join, alias, joinOn, joinTo) {
+        return self._buildJoin(join, JOINTYPE.FULLOUTERJOIN, alias, joinOn, joinTo);
     };
 
     self.fullOuterJoinSubQuery = function fullOuterJoinSubQuery(query, alias, criteria) {
-        _sqlObject.joins.join.push("(" + query + ")" + (criteria ? " " + alias + " ON " + criteria : ""));
-        _sqlObject.joins.joinType.push(" FULL OUTER JOIN ");
-        if (!alias) console.error("joinSubQuery requires 2 parameters, but only 1 was given. Please add the subQuery alias as the 2nd parameter");
-        _sqlObject.joins.joinAlias.push(alias);
-        _sqlObject.joins.joinOn.push(undefined);
-        _sqlObject.joins.joinTo.push(undefined);
-        return self;
+        return self._buildSubQueryJoin(query, alias, criteria, JOINTYPE.FULLOUTERJOIN);
     };
@@ -505,10 +523,10 @@
                 var containsAlias = _selectProps[_selectAliases.indexOf(prop)];
-                if(containsAlias){
+                if (containsAlias) {
                     whereString += containsAlias;
-                }else if(prop.indexOf(".") !== -1){
+                } else if (prop.indexOf(".") !== -1) {
                     whereString += prop;
-                }else{
+                } else {
                     whereString += _sqlObject.fromAlias + "." + prop;
                 }
-                 whereString += " LIKE '%" +
+                whereString += " LIKE '%" +
                     filter.substr(contains + 11, filter.lastIndexOf("\"") - (contains + 11)) +
@@ -598,3 +616,3 @@ "%'";
         var idx = _selectAliases.indexOf(orderByInput);
-        if(idx !== -1){
+        if (idx !== -1) {
             orderByInput = _selectProps[idx];
@@ -833,3 +851,3 @@ _sqlObject.orderBy = orderByInput;
                 }
-                else if(_sqlObject.groupBy) {
+                else if (_sqlObject.groupBy) {
                     query += _sqlObject.groupBy;
@@ -836,0 +854,0 @@ }

package.json

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

test/unit/nestedSelectTest.js

@@ -12,3 +12,4 @@ /**
             entityToTableMap: {
-                "CustomerType": "AccountingProcessTypes"
+                "CustomerType": "AccountingProcessTypes",
+                "OperationalCategory": "EquipmentTypes"
             },
@@ -120,2 +121,4 @@ enumerationSuffixes: ['DisplayName', 'Value'],
     it('should not pluralize a table that is singluar in the map', function (done) {
+
+
         var qObj = {
@@ -131,5 +134,26 @@ 'Id': 'Id',
         query.should.equal("SELECT Id AS 'Id', Number AS 'Number', LongName AS 'LongName', VendorType.IsTaxAuthority AS 'VendorType.IsTaxAuthority' FROM RemitTos JOIN VendorType ON RemitTos.VendorType_id = VendorType.Id WHERE RemitTos.IsActive = 1 AND VendorType.IsTaxAuthority = 1 ");
-done();
-    })
+        done();
+    });
 
+    it('It should create proper query when using entityToTablemap', function (done) {
+
+        var reqquery ={
+            sidx: 'OperationalCategory.Name',
+            sord: 'asc',
+            RenderFormat: 'paged',
+            page: '1',
+            pagePercent: '0.3333333333333333',
+            total: '238',
+            rows: '100',
+            totalPages: '3' };
+
+        var qObj = {
+            'OperationalCategory.Name': 'OperationalCategory.Name',
+            'count(OperationalCategory.Name)': 'Count'
+        };
+        var query = sqlBuilder().from('FixedAssets').selectJoin(qObj).groupBy('OperationalCategory.Name').processListRequest(reqquery).build();
+        query.should.equal("WITH SelectedItems AS (SELECT EquipmentTypes.Name AS 'OperationalCategory.Name', COUNT(EquipmentTypes.Name) AS 'Count', ROW_NUMBER() OVER (ORDER BY EquipmentTypes.Name ASC) AS Position FROM FixedAssets JOIN EquipmentTypes ON FixedAssets.OperationalCategory_id = EquipmentTypes.Id GROUP BY EquipmentTypes.Name) SELECT *, (Select MAX(Position) From SelectedItems) as 'TotalRows' FROM SelectedItems WHERE Position > 0 AND Position <= 100 ");
+        done();
+    });
+
 });

test/unit/selectTest.js

@@ -129,7 +129,7 @@ /**
             .select({"p.Name": "ProductName"})
-            .from("Products", "p")
+            .from("Items", "p")
             .joinAs("(" + sqlBuilder().select("Id").from("Tickets").build() + ")", "t", "p.Ticket_Id", "t.Id")
             .where("p.Id = (SELECT o.Product_id FROM OrderItems o WHERE o.Product_id = p.Id )")
             .build())
-            .should.equal("SELECT p.Name AS 'ProductName' FROM Products p JOIN (SELECT Id FROM Tickets ) t ON p.Ticket_Id = t.Id WHERE p.Id = (SELECT o.Product_id FROM OrderItems o WHERE o.Product_id = p.Id ) ");
+            .should.equal("SELECT p.Name AS 'ProductName' FROM Items p JOIN (SELECT Id FROM Tickets ) t ON p.Ticket_Id = t.Id WHERE p.Id = (SELECT o.Product_id FROM OrderItems o WHERE o.Product_id = p.Id ) ");
         done();
@@ -136,0 +136,0 @@ });

No alert changes

Improved metrics

Total package byte prevSize

164173

increased by0.76%

Lines of code

3540

increased by1.14%

No dependency changes