adding groupBy and arrayAgg

Justin Reidy · Justin Reidy · commit 1354d7eb4769 · 2012-12-18T14:31:20.000-08:00
diff --git a/lib/column.js b/lib/column.js
@@ -62,6 +62,13 @@ Column.prototype.as = function(alias) {
   return new ColumnNode(context);
 }
 
+Column.prototype.arrayAgg = function(alias) {
+  var context = contextify(this);
+  context.asArray = true;
+  context.alias = alias || context.name + 's';
+  return new ColumnNode(context);
+}
+
 binaryMethod('equals', '=');
 binaryMethod('equal', '=');
 binaryMethod('notEqual', '<>');
diff --git a/lib/dialect/postgres.js b/lib/dialect/postgres.js
@@ -20,6 +20,7 @@ Postgres.prototype.visit = function(node) {
     case 'FROM': return this.visitFrom(node);
     case 'WHERE': return this.visitWhere(node);
     case 'ORDER BY': return this.visitOrderBy(node);
+    case 'GROUP BY': return this.visitGroupBy(node);
     case 'RETURNING': return this.visitReturning(node);
     case 'BINARY': return this.visitBinary(node);
     case 'TABLE': return this.visitTable(node);
@@ -28,7 +29,7 @@ Postgres.prototype.visit = function(node) {
     case 'TEXT': return node.text;
     case 'UNARY': return this.visitUnary(node);
     case 'PARAMETER': return this.visitParameter(node);
-    case 'LIMIT': 
+    case 'LIMIT':
     case 'OFFSET':
       return this.visitModifier(node);
     default: throw new Error("Unrecognized node type " + node.type);
@@ -106,6 +107,11 @@ Postgres.prototype.visitOrderBy = function(orderBy) {
   return result;
 }
 
+Postgres.prototype.visitGroupBy = function(groupBy) {
+  var result = ['GROUP BY', groupBy.nodes.map(this.visit.bind(this)).join(', ')];
+  return result;
+}
+
 Postgres.prototype.visitBinary = function(binary) {
   return '(' + this.visit(binary.left) + ' ' + binary.operator + ' ' + this.visit(binary.right) + ')';
 }
@@ -146,7 +152,11 @@ Postgres.prototype.visitTable = function(tableNode) {
 
 Postgres.prototype.visitColumn = function(columnNode) {
   var table = columnNode.table;
+  var inSelectClause = !this._selectOrDeleteEndIndex;
   var txt = "";
+  if(inSelectClause && columnNode.asArray) {
+    txt += 'array_agg(';
+  }
   if(!this._visitedInsert && !this._visitingUpdate) {
     if(table.alias) {
       txt = table.alias;
@@ -157,11 +167,14 @@ Postgres.prototype.visitColumn = function(columnNode) {
       }
       txt += this.quote(table.getName());
     }
-    txt += '.'
+    txt += '.';
   }
   txt += this.quote(columnNode.name);
-  var inSelectClause = !this._selectOrDeleteEndIndex;
-  if(inSelectClause && columnNode.alias) {
+  if(inSelectClause && columnNode.asArray) {
+    txt += ')';
+    txt += ' as ' + this.quote(columnNode.alias);
+  }
+  else if(inSelectClause && columnNode.alias) {
     txt += ' as ' + this.quote(columnNode.alias);
   }
   return txt;
diff --git a/lib/node/column.js b/lib/node/column.js
@@ -5,6 +5,7 @@ module.exports = Node.define({
   constructor: function(config) {
     this.name = config.name;
     this.alias = config.alias;
+    this.asArray = config.asArray;
     this.table = config.table;
     this.value = config.getValue();
   }
diff --git a/lib/node/groupBy.js b/lib/node/groupBy.js
@@ -0,0 +1,5 @@
+var Node = require(__dirname);
+
+module.exports = Node.define({
+  type: 'GROUP BY'
+});
diff --git a/lib/node/query.js b/lib/node/query.js
@@ -3,6 +3,7 @@ var Select = require(__dirname + '/select');
 var From = require(__dirname + '/from');
 var Where = require(__dirname + '/where');
 var OrderBy = require(__dirname + '/orderBy');
+var GroupBy = require(__dirname + '/groupBy');
 var Insert = require(__dirname + '/insert');
 var Update = require(__dirname + '/update');
 var Delete = require(__dirname + '/delete');
@@ -55,6 +56,14 @@ var Query = Node.define({
     });
     return this.add(orderBy);
   },
+  group: function() {
+    var args = Array.prototype.slice.call(arguments, 0);
+    var groupBy = new GroupBy();
+    var nodes = args.forEach(function(arg) {
+      groupBy.add(arg.toNode());
+    });
+    return this.add(groupBy);
+  },
   insert: function(o) {
     var self = this;
     var args = Array.prototype.slice.call(arguments, 0);
diff --git a/test/dialect-tests.js b/test/dialect-tests.js
@@ -93,8 +93,8 @@ test({
 });
 
 test({
-  query : user.select(user.name.as('user name'), user.id.as('user id')).from(user),
-  pg    : 'SELECT "user"."name" as "user name", "user"."id" as "user id" FROM "user"'
+  query : user.select(user.name, post.content).from(user.join(post).on(user.id.equals(post.userId))),
+  pg    : 'SELECT "user"."name", "post"."content" FROM "user" INNER JOIN "post" ON ("user"."id" = "post"."userId")'
 });
 
 test({
@@ -142,7 +142,7 @@ test({
 
 test({
   query : post.select(post.content).order(post.content),
-  pg    : 'SELECT "post"."content" FROM "post" ORDER BY "post"."content"',
+  pg    : 'SELECT "post"."content" FROM "post" ORDER BY "post"."content"'
 });
 
 test({
@@ -155,6 +155,23 @@ test({
   pg    : 'SELECT "post"."content" FROM "post" ORDER BY "post"."content", ("post"."userId"  DESC)'
 });
 
+//GROUP BY & AGGREGATES
+test({
+  query : post.select(post.content).group(post.userId),
+  pg    : 'SELECT "post"."content" FROM "post" GROUP BY "post"."userId"'
+});
+
+test({
+  query : post.select(post.content.arrayAgg()).group(post.userId),
+  pg    : 'SELECT array_agg("post"."content") as "contents" FROM "post" GROUP BY "post"."userId"'
+});
+
+test({
+  query : post.select(post.content.arrayAgg('post contents')).group(post.userId),
+  pg    : 'SELECT array_agg("post"."content") as "post contents" FROM "post" GROUP BY "post"."userId"'
+});
+
+
 
 test({
   query : post.insert(post.content.value('test'), post.userId.value(1)),

Original file line number	Diff line number	Diff line change
`@@ -5,6 +5,7 @@ module.exports = Node.define({`
`5`	`5`	`constructor: function(config) {`
`6`	`6`	`this.name = config.name;`
`7`	`7`	`this.alias = config.alias;`
	`8`	`+ this.asArray = config.asArray;`
`8`	`9`	`this.table = config.table;`
`9`	`10`	`this.value = config.getValue();`
`10`	`11`	`}`