sqlquerybuilder
Advanced tools
Comparing version 0.0.11 to 0.0.12
var uuid = require('node-uuid'), pluralizer = require('pluralizer'), _ = require('lodash'); | ||
var options = {}; | ||
exports.queryBuilder = function () { | ||
return new QueryBuilder(); | ||
}; | ||
/** | ||
@@ -22,2 +17,6 @@ * Setup options for QueryBuilder | ||
exports.queryBuilder = function () { | ||
return new QueryBuilder(); | ||
}; | ||
function QueryBuilder() { | ||
@@ -221,2 +220,4 @@ var self = this; | ||
_sqlObject.joins.joinAlias.push(alias); | ||
_sqlObject.joins.joinOn.push(undefined); | ||
_sqlObject.joins.joinTo.push(undefined); | ||
@@ -273,2 +274,4 @@ return self; | ||
_sqlObject.joins.joinAlias.push(alias); | ||
_sqlObject.joins.joinOn.push(undefined); | ||
_sqlObject.joins.joinTo.push(undefined); | ||
return self; | ||
@@ -313,2 +316,4 @@ }; | ||
_sqlObject.joins.joinAlias.push(alias); | ||
_sqlObject.joins.joinOn.push(undefined); | ||
_sqlObject.joins.joinTo.push(undefined); | ||
return self; | ||
@@ -353,2 +358,4 @@ }; | ||
_sqlObject.joins.joinAlias.push(alias); | ||
_sqlObject.joins.joinOn.push(undefined); | ||
_sqlObject.joins.joinTo.push(undefined); | ||
return self; | ||
@@ -575,26 +582,2 @@ }; | ||
self.orderBy = function orderBy(orderByInput, direction) { | ||
// if(orderByInput && orderByInput.indexOf('.') !== -1){ | ||
// var parts = orderByInput.split('.'); | ||
// | ||
// var table = parts[0]; | ||
// if(table.length > 2 && table[table.length -1].toLowerCase() !== 's'){ | ||
// if(options.entityToTableMap[table]){ | ||
// table = options.entityToTableMap[table]; | ||
// }else{ | ||
// table = table.pluralize(); | ||
// } | ||
// } | ||
// orderByInput = table + '.' + parts[1]; | ||
// }else{ | ||
// orderByInput = _sqlObject.fromAlias + "." + orderByInput; | ||
// } | ||
// | ||
// _sqlObject.orderBy = orderByInput; | ||
// _sqlObject.direction = direction ? direction.toUpperCase() : "ASC"; | ||
// | ||
// return self; | ||
// new code | ||
var idx = _selectAliases.indexOf(orderByInput); | ||
@@ -654,3 +637,2 @@ if(idx !== -1){ | ||
gParts = gParts.concat(groupByInput.split('.')); | ||
console.log(gParts); | ||
} else { | ||
@@ -837,2 +819,5 @@ groupByOutput.push(groupByInput); | ||
} | ||
else if(_sqlObject.groupBy) { | ||
query += _sqlObject.groupBy; | ||
} | ||
else | ||
@@ -839,0 +824,0 @@ query += _sqlObject.fromAlias + ".Created"; |
{ | ||
"name": "sqlquerybuilder", | ||
"version": "0.0.11", | ||
"version": "0.0.12", | ||
"description": "Highly opinionated Sql Server Query Writer, mostly for internal use.", | ||
@@ -5,0 +5,0 @@ "main": "./lib/sqlQueryBuilder", |
@@ -116,2 +116,28 @@ /** | ||
}); | ||
//with JOIN SUBQUERY AFTER natural join | ||
it('it is called with a JOIN SUBQUERY AFTER natural join', function(done){ | ||
(sqlBuilder().select({"p.Name": "ProductName"}) | ||
.from("Products", "p") | ||
.join("Customers c") | ||
.leftJoinSubQuery(sqlBuilder().select("Id").from("Tickets").where("Id = 4").build(), "sq", "sq.Id = p.Ticket_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 Customers c ON p.Customer_id = c.Id LEFT JOIN (SELECT Id FROM Tickets WHERE Id = 4 ) sq ON sq.Id = p.Ticket_id WHERE p.Id = " + | ||
"(SELECT o.Product_id FROM OrderItems o WHERE o.Product_id = p.Id) "); | ||
done(); | ||
}); | ||
//with JOIN SUBQUERY BEFORE natural join | ||
it('it is called with a JOIN SUBQUERY BEFORE natural join', function(done){ | ||
(sqlBuilder().select({"p.Name": "ProductName"}) | ||
.from("Products", "p") | ||
.leftJoinSubQuery(sqlBuilder().select("Id").from("Tickets").where("Id = 4").build(), "sq", "sq.Id = p.Ticket_id") | ||
.join("Customers c") | ||
.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 LEFT JOIN (SELECT Id FROM Tickets WHERE Id = 4 ) sq ON sq.Id = p.Ticket_id JOIN Customers c ON p.Customer_id = c.Id WHERE p.Id = " + | ||
"(SELECT o.Product_id FROM OrderItems o WHERE o.Product_id = p.Id) "); | ||
done(); | ||
}); | ||
}); |
@@ -36,3 +36,4 @@ /** | ||
}, | ||
{ single: 'RemitTo', plural: 'RemitTos'} | ||
{ single: 'RemitTo', plural: 'RemitTos'}, | ||
{ single: 'VendorType', plural: 'VendorType'} | ||
] | ||
@@ -118,3 +119,16 @@ }); | ||
it('should not pluralize a table that is singluar in the map', function (done) { | ||
var qObj = { | ||
'Id': 'Id', | ||
Number: 'Number', | ||
'LongName': 'LongName', | ||
'VendorType.IsTaxAuthority': 'VendorType.IsTaxAuthority' | ||
}, | ||
wherobj = { 'IsActive': true, 'VendorType.IsTaxAuthority': true}; | ||
var query = sqlBuilder().from('RemitTos').selectJoin(qObj).where(wherobj).build(); | ||
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(); | ||
}) | ||
}); |
@@ -319,2 +319,10 @@ /** | ||
}); | ||
it('called with NOT Equals', function (done) { | ||
(sqlBuilder().select('*') | ||
.from('Tickets') | ||
.where({StatusDisplayName: 'Open'}, '<>').build()) | ||
.should.equal("SELECT * FROM Tickets WHERE Tickets.StatusDisplayName <> 'Open' "); | ||
done(); | ||
}); | ||
}); |
159299
3440