sqlquerybuilder - npm Package Compare versions

Comparing version 0.0.40 to 0.0.41

lib/ordering.js

@@ -6,17 +6,14 @@ var _ = require('lodash');
     self.orderBy = function orderBy(orderByInput, direction, skipAlias) {
-        var alias = _.find(self._selectAliases, function(alias) {
-            return alias.alias == orderByInput;
-        });
-        if (alias) {
-            orderByInput = alias.selectProp;
-            self._sqlObject.orderBy = orderByInput;
-            self._sqlObject.direction = direction ? direction.toUpperCase() : "ASC";
+        if(!orderByInput) return self;
 
-            return self;
+        if (orderByInput.indexOf('.DisplayName') !== -1) {
+            orderByInput = orderByInput.replace(/\b.DisplayName/ig, "DisplayName");
         }
+        if (orderByInput.indexOf('.Value') !== -1) {
+            orderByInput = orderByInput.replace(/\b.Value/ig, "Value");
+        }
 
-        if (orderByInput && orderByInput.indexOf('.') !== -1 && orderByInput.indexOf('(') !== 0) {
+        var alias;
+        if (orderByInput.indexOf('.') !== -1 && orderByInput.indexOf('(') !== 0) {
             var parts = orderByInput.split('.');
-
-
             var table = parts[0];
@@ -27,10 +24,23 @@ if (self._options.enumerationSuffixes.indexOf(parts[1]) !== -1) {
                 if (self._options.entityToTableMap[table]) {
-                    table = self._options.entityToTableMap[table];
+                    orderByInput = self._options.entityToTableMap[table] + '.' + parts[1];
                 } else {
-                    table = self._checkTableName(table);
+                    alias = _.find(self._selectAliases, function(alias) {
+                        return alias.alias == orderByInput;
+                    });
+
+                    if (alias)
+                        orderByInput = alias.selectProp;
+                    else
+                        orderByInput = self._checkTableName(table) + '.' + parts[1];
                 }
-                orderByInput = table + '.' + parts[1];
             }
         } else if(!skipAlias && orderByInput.indexOf('(') !== 0) {
-            orderByInput = self._sqlObject.fromAlias + "." + orderByInput;
+            alias = _.find(self._selectAliases, function(alias) {
+                return alias.alias == orderByInput;
+            });
+
+            if (alias)
+                orderByInput = alias.selectProp;
+            else
+                orderByInput = self._sqlObject.fromAlias + "." + orderByInput;
         }
@@ -37,0 +47,0 @@

lib/selects.js

@@ -106,7 +106,7 @@ module.exports = function selects(self) {
                         self._sqlObject.select += " AS '" + alias + "'";
-                        self._selectAliases.push(alias.replace(/'/g, ""));
+                        self._selectAliases.push({alias:alias.replace(/'/g, ""),selectProp:props[i]});
                         if (j != aliases.length - 1) self._sqlObject.select += ", " + props[i];
                     }
                 } else {
-                    self._selectAliases.push(props[i]);
+                    self._selectAliases.push({alias:props[i],selectProp:props[i]});
                 }
@@ -113,0 +113,0 @@ if (i != props.length - 1) self._sqlObject.select += ", ";

lib/where.js

@@ -93,16 +93,11 @@ var _ = require('lodash');
 
+            var opIndex,
+                reference;
+
             // String filters
-            var contains = filter.indexOf(".Contains");
-            if (contains !== -1) {
-                var prop = filter.substr(0, contains);
-                var containsAlias = _.find(self._selectAliases, {alias: prop});
-                if (containsAlias) {
-                    whereString += containsAlias.selectProp;
-                } else if (prop.indexOf(".") !== -1) {
-                    whereString += prop;
-                } else {
-                    whereString += self._sqlObject.fromAlias + "." + prop;
-                }
-                whereString += " LIKE '%" +
-                    filter.substr(contains + 11, filter.lastIndexOf("\"") - (contains + 11)) +
+            opIndex = filter.indexOf(".Contains");
+            if (opIndex !== -1) {
+                reference = filter.substr(0, opIndex);
+                whereString += self._whereJoin(reference) + " LIKE '%" +
+                    filter.substr(opIndex + 11, filter.lastIndexOf("\"") - (opIndex + 11)) +
                     "%'";
@@ -112,30 +107,36 @@ continue;
 
-            var equals = filter.indexOf(".Equals");
+            opIndex = filter.indexOf(".StartsWith");
+            if (opIndex != -1) {
+                reference = filter.substr(0, opIndex);
+                whereString += self._whereJoin(reference) +
+                    " LIKE '" +
+                    filter.substr(opIndex + 13, filter.lastIndexOf("\"") - (opIndex + 13)) +
+                    "%'";
+                continue;
+            }
+
+            opIndex = filter.indexOf(".EndsWith");
+            if (opIndex != -1) {
+                reference = filter.substr(0, opIndex);
+                whereString += self._whereJoin(reference) +
+                    " LIKE '%" +
+                    filter.substr(opIndex + 11, filter.lastIndexOf("\"") - (opIndex + 11)) +
+                    "'";
+                continue;
+            }
+
+            opIndex = filter.indexOf(".Equals");
             var shouldAppendParenth = false;
-            if (equals !== -1) {
-                var equalsAlias = _.find(self._selectAliases, {alias: filter.substr(0, equals)});
+            if (opIndex !== -1) {
                 if (filter.charAt(0) === '(') {
                     whereString += '(';
                     filter = filter.substr(1);
-                    equals = filter.indexOf(".Equals");
+                    opIndex = filter.indexOf(".Equals");
                     shouldAppendParenth = true;
                 }
+                reference = filter.substr(0, opIndex);
 
-                if (equalsAlias)
-                    equalsAlias = equalsAlias.selectProp;
-                else {
-                    // Check the join aliases
-                    var filterParts = filter.split('.');
-                    if (filterParts.length > 2) {
-                        if (self._sqlObject.joins.joinAlias.indexOf(filterParts[0]) !== -1) {
-                            equalsAlias = filterParts[0] + '.' + filterParts[1];
-                        }
-                    }
-                }
-
-                var equalsWhere = (equalsAlias ? equalsAlias : (self._sqlObject.fromAlias + "." + filter.substr(0, equals)));
-                whereString += equalsWhere +
-
+                whereString += self._whereJoin(reference) +
                     " = '" +
-                    filter.substr(equals + 9, filter.lastIndexOf("\"") - (equals + 9)) +
+                    filter.substr(opIndex + 9, filter.lastIndexOf("\"") - (opIndex + 9)) +
                     "'";
@@ -148,29 +149,9 @@ if (shouldAppendParenth)
 
-            var startsWith = filter.indexOf(".StartsWith");
-            if (startsWith != -1) {
-                var startsAlias = _.find(self._selectAliases,{alias:filter.substr(0, startsWith)});
-                whereString += (startsAlias ? startsAlias.selectProp : (self._sqlObject.fromAlias + "." + filter.substr(0, startsWith))) +
-                    " LIKE '" +
-                    filter.substr(startsWith + 13, filter.lastIndexOf("\"") - (startsWith + 13)) +
-                    "%'";
-                continue;
-            }
-
-            var endsWith = filter.indexOf(".EndsWith");
-            if (endsWith != -1) {
-                var endsAlias = _.find(self._selectAliases, {alias: filter.substr(0, endsWith)});
-                whereString += (endsAlias ? endsAlias.selectProp : (self._sqlObject.fromAlias + "." + filter.substr(0, endsWith))) +
-                    " LIKE '%" +
-                    filter.substr(endsWith + 11, filter.lastIndexOf("\"") - (endsWith + 11)) +
-                    "'";
-                continue;
-            }
-
             // Date filters, strict inequalities need to come after their non strict versions
-            var dateComparer = filter.indexOf(">=");
-            if (dateComparer != -1) {
-                var greatEqualAlias = _.find(self._selectAliases, {alias: filter.substr(0, dateComparer)});
-                whereString += (greatEqualAlias ? greatEqualAlias.selectProp : (filter.substr(0, dateComparer).charAt(0) === "(" ? filter.substr(0, dateComparer) : self._sqlObject.fromAlias + "." + filter.substr(0, dateComparer))) +
+            opIndex = filter.indexOf(">=");
+            if (opIndex != -1) {
+                reference = filter.substr(0, opIndex);
+                whereString += self._whereJoin(reference) +
                     " >= '" +
-                    filter.substr(dateComparer + 2, filter.length - dateComparer) +
+                    filter.substr(opIndex + 2, filter.length - opIndex) +
                     "'";
@@ -180,8 +161,8 @@ continue;
 
-            dateComparer = filter.indexOf(">");
-            if (dateComparer != -1) {
-                var greatAlias = _.find(self._selectAliases, {alias: filter.substr(0, dateComparer)});
-                whereString += (greatAlias ? greatAlias.selectProp : (filter.substr(0, dateComparer).charAt(0) === "(" ? filter.substr(0, dateComparer) : self._sqlObject.fromAlias + "." + filter.substr(0, dateComparer))) +
+            opIndex = filter.indexOf(">");
+            if (opIndex != -1) {
+                reference = filter.substr(0, opIndex);
+                whereString += self._whereJoin(reference) +
                     " > '" +
-                    filter.substr(dateComparer + 1, filter.length - dateComparer)+ //.replace(/-/g, "/") +
+                    filter.substr(opIndex + 1, filter.length - opIndex)+ //.replace(/-/g, "/") +
                     "'";
@@ -191,8 +172,8 @@ continue;
 
-            dateComparer = filter.indexOf("<=");
-            if (dateComparer != -1) {
-                var lessEqualAlias = _.find(self._selectAliases, {alias: filter.substr(0, dateComparer)});
-                whereString += (lessEqualAlias ? lessEqualAlias.selectProp : ( filter.substr(0, dateComparer).charAt(0) === "(" ? filter.substr(0, dateComparer) : self._sqlObject.fromAlias + "." + filter.substr(0, dateComparer))) +
+            opIndex = filter.indexOf("<=");
+            if (opIndex != -1) {
+                reference = filter.substr(0, opIndex);
+                whereString += self._whereJoin(reference) +
                     " <= '" +
-                    filter.substr(dateComparer + 2, filter.length - dateComparer - 1)+ //.replace(/-/g, "/") +
+                    filter.substr(opIndex + 2, filter.length - opIndex - 1)+ //.replace(/-/g, "/") +
                     "'";
@@ -202,8 +183,8 @@ continue;
 
-            dateComparer = filter.indexOf("<");
-            if (dateComparer != -1) {
-                var lessAlias = _.find(self._selectAliases, {alias: filter.substr(0, dateComparer)});
-                whereString += (lessAlias ? lessAlias.selectProp : ( filter.substr(0, dateComparer).charAt(0) === "(" ? filter.substr(0, dateComparer) : self._sqlObject.fromAlias + "." + filter.substr(0, dateComparer))) +
+            opIndex = filter.indexOf("<");
+            if (opIndex != -1) {
+                reference = filter.substr(0, opIndex);
+                whereString += self._whereJoin(reference) +
                     " < '" +
-                    filter.substr(dateComparer + 1, filter.length - dateComparer - 1) +
+                    filter.substr(opIndex + 1, filter.length - opIndex - 1) +
                     "'";
@@ -214,8 +195,8 @@ continue;
             // Dropdown filters
-            var equalComparer = filter.indexOf("==");
-            if (equalComparer != -1) {
-                var dropAlias = _.find(self._selectAliases,{alias: filter.substr(1, equalComparer - 1)});
-                whereString += "(" + (dropAlias ? dropAlias.selectProp : (self._sqlObject.fromAlias + "." + filter.substr(1, equalComparer - 1))) +
+            opIndex = filter.indexOf("==");
+            if (opIndex != -1) {
+                reference = filter.substr(1, opIndex - 1);
+                whereString += "(" + self._whereJoin(reference) +
                     " = " +
-                    convertTrueFalse(filter.substr(equalComparer + 2, filter.length - equalComparer - 3)) +
+                    convertTrueFalse(filter.substr(opIndex + 2, filter.length - opIndex - 3)) +
                     ")";
@@ -225,10 +206,8 @@ continue;
 
-            equalComparer = filter.indexOf('!=');
+            opIndex = filter.indexOf('!=');
             var pars;
-            if(equalComparer != - 1){
+            if(opIndex != - 1){
                 pars = filter.split('!=');
-
-                var notEqualAlias = self._selectProps[self._selectAliases.indexOf(pars[0])];
-                whereString += "(" + (notEqualAlias ? notEqualAlias : (self._sqlObject.fromAlias + "." + pars[0]));
-                whereString += pars[1].toUpperCase() === "NULL" ? " IS NOT " : " != ";
+                reference = pars[0];
+                whereString += "(" + self._whereJoin(reference) + (pars[1].toUpperCase() === "NULL" ? " IS NOT " : " != ");
                 if (isNaN(pars[1]) && pars[1].toUpperCase() !== "NULL") {
@@ -244,9 +223,7 @@ whereString += "'";
 
-            equalComparer = filter.indexOf("=");
-            if (equalComparer != -1) {
+            opIndex = filter.indexOf("=");
+            if (opIndex != -1) {
                 pars = filter.split('=');
-
-                var dropAlias2 = _.find(self._selectAliases, {alias: pars[0]});
-                whereString += "(" + (dropAlias2 ? dropAlias2.selectProp : (self._sqlObject.fromAlias + "." + pars[0]));
-                whereString += pars[1].toUpperCase() === "NULL" ? " IS " : " = ";
+                reference = pars[0];
+                whereString += "(" + self._whereJoin(reference) +  (pars[1].toUpperCase() === "NULL" ? " IS " : " = ");
                 if (isNaN(pars[1]) && pars[1].toUpperCase() !== "NULL") {
@@ -263,8 +240,7 @@ whereString += "'";
             // Yes/No filters
-            var bang = filter.indexOf("!");
-            var aliasLookup = bang == -1 ? filter : filter.substr(1);
-            var alias = _.find(self._selectAliases, {alias: filter.substr(1, equalComparer - 1)});
-            whereString += (alias ? alias.selectProp : (self._sqlObject.fromAlias + "." + aliasLookup)) +
+            opIndex = filter.indexOf("!");
+            reference = opIndex == -1 ? filter : filter.substr(1);
+            whereString += self._whereJoin(reference) +
                 " = " +
-                (bang == -1 ? "1" : "0");
+                (opIndex == -1 ? "1" : "0");
         }
@@ -278,3 +254,34 @@ return self._where(whereString, ' AND ');
 
+    self._whereJoin = function _whereJoin(reference) {
+        var retString;
+
+        var alias = _.find(self._selectAliases, {alias: reference});
+        if (alias) {
+            retString = alias.selectProp;
+        }
+        else {
+            if (reference.indexOf('.') !== -1 && reference[0] !== '(') {
+                var nameParts = reference.split('.');
+
+                if (self._sqlObject.joins.joinAlias.indexOf(nameParts[0]) !== -1) {
+                    reference = nameParts[0] + '.' + nameParts[1];
+                }
+                else if (self._options.entityToTableMap[nameParts[0]]) {
+                    reference = self._options.entityToTableMap[nameParts[0]] + '.' + nameParts[1];
+                } else {
+                    var table = self._checkTableName(nameParts[0]);
+                    reference = table + '.' + nameParts[1];
+                    if (table != self._sqlObject.from)
+                        self.leftJoin(table);
+                }
+                retString = reference;
+            }
+            else
+                retString = (reference[0] === "(" ? reference : self._sqlObject.fromAlias + "." + reference);
+        }
+
+        return retString;
+    };
+
     return self;
-};
+};

package.json

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

test/unit/processListRequest/processListRequestTest.js

@@ -525,3 +525,3 @@ /**
             .should.equal("WITH SelectedItems AS (SELECT al.Id, al.Name, al.Code, y.Name as [Yard.Name], ROW_NUMBER() OVER (ORDER BY al.Created) AS Position FROM AccountLocations al " +
-        "LEFT JOIN Yards y ON al.Yard_id = y.Id WHERE Yard.Name LIKE '%athen%') SELECT *, (Select MAX(Position) From SelectedItems) as 'TotalRows' " +
+        "LEFT JOIN Yards y ON al.Yard_id = y.Id WHERE Yards.Name LIKE '%athen%') SELECT *, (Select MAX(Position) From SelectedItems) as 'TotalRows' " +
                 "FROM SelectedItems WHERE Position > 0 AND Position <= 100 ");
@@ -844,3 +844,3 @@ done();
     });
-    
+
     it('Should handle multiple aliases to the same field', function (done) {
@@ -847,0 +847,0 @@ var selection = {

No alert changes

Improved metrics

Total package byte prevSize

207905

increased by0.26%

Lines of code

4354

increased by1.4%

No dependency changes