add DISTINCT ON support for Postgres

Bergwinkl Thomas · Bergwinkl Thomas · commit d42d02448e8d · 2015-07-29T16:11:24.000+02:00
diff --git a/lib/dialect/postgres.js b/lib/dialect/postgres.js
@@ -109,6 +109,7 @@ Postgres.prototype.visit = function(node) {
     case 'DROP'            : return this.visitDrop(node);
     case 'TRUNCATE'        : return this.visitTruncate(node);
     case 'DISTINCT'        : return this.visitDistinct(node);
+    case 'DISTINCT ON'     : return this.visitDistinctOn(node);
     case 'ALIAS'           : return this.visitAlias(node);
     case 'ALTER'           : return this.visitAlter(node);
     case 'CAST'            : return this.visitCast(node);
@@ -180,9 +181,20 @@ Postgres.prototype.quote = function(word, quoteCharacter) {
 
 Postgres.prototype.visitSelect = function(select) {
   var result = ['SELECT']
+
   if (select.isDistinct) result.push('DISTINCT');
-  result.push(select.nodes.map(this.visit.bind(this)).join(', '));
+
+  var distinctOnNode = select.nodes.filter(function (node) {return node.type === 'DISTINCT ON';}).shift();
+  var nonDistinctOnNodes = select.nodes.filter(function (node) {return node.type !== 'DISTINCT ON';});
+
+  if (distinctOnNode) {
+    result.push(this.visit(distinctOnNode));
+  }
+
+  result.push(nonDistinctOnNodes.map(this.visit.bind(this)).join(', '));
+
   this._selectOrDeleteEndIndex = this.output.length + result.length;
+
   return result;
 };
 
@@ -295,6 +307,10 @@ Postgres.prototype.visitDistinct = function(truncate) {
   return [];
 };
 
+Postgres.prototype.visitDistinctOn = function(distinctOn) {
+  return ['DISTINCT ON('+distinctOn.nodes.map(this.visit.bind(this)).join(', ')+')'];
+};
+
 Postgres.prototype.visitAlias = function(alias) {
   var result = [this.visit(alias.value) + ' AS ' + this.quote(alias.alias)];
   return result;
diff --git a/lib/node/distinctOn.js b/lib/node/distinctOn.js
@@ -0,0 +1,7 @@
+'use strict';
+
+var Node = require(__dirname);
+
+module.exports = Node.define({
+  type: 'DISTINCT ON',
+});
diff --git a/lib/node/query.js b/lib/node/query.js
@@ -22,6 +22,7 @@ var Create          = require('./create');
 var Drop            = require('./drop');
 var Truncate        = require('./truncate');
 var Distinct        = require('./distinct');
+var DistinctOn      = require('./distinctOn');
 var Alter           = require('./alter');
 var AddColumn       = require('./addColumn');
 var DropColumn      = require('./dropColumn');
@@ -317,6 +318,33 @@ var Query = Node.define({
     return this.add(new Distinct());
   },
 
+  distinctOn: function() {
+    var distinctOn;
+    if (this._distinctOn) {
+      distinctOn = this._distinctOn;
+    } else {
+      var select = this.nodes.filter(function (node) {return node.type === 'SELECT';}).shift();
+
+      distinctOn = this._distinctOn = new DistinctOn();
+      select.add(distinctOn);
+    }
+
+    //allow things like .distinctOn(a.star(), [ a.id, a.name ])
+    //this will flatten them into a single array
+    var args = sliced(arguments).reduce(function(cur, next) {
+      if (util.isArray(next)) {
+        return cur.concat(next);
+      }
+
+      cur.push(next);
+      return cur;
+    }, []);
+
+    distinctOn.addAll(args);
+
+    return this;
+  },
+
   alter: function() {
     return this.add(new Alter());
   },
diff --git a/test/dialects/distinct-on-tests.js b/test/dialects/distinct-on-tests.js
@@ -0,0 +1,24 @@
+'use strict';
+
+var Harness = require('./support');
+var user = Harness.defineUserTable();
+var Sql = require('../../lib').setDialect('postgres');
+
+Harness.test({
+  query: user.select().distinctOn(user.id),
+  pg: {
+    text  : 'SELECT DISTINCT ON("user"."id") "user".* FROM "user"',
+    string: 'SELECT DISTINCT ON("user"."id") "user".* FROM "user"'
+  },
+  params: []
+});
+
+Harness.test({
+  query: user.select(user.id,user.name).distinctOn(user.id),
+  pg: {
+    text  : 'SELECT DISTINCT ON("user"."id") "user"."id", "user"."name" FROM "user"',
+    string: 'SELECT DISTINCT ON("user"."id") "user"."id", "user"."name" FROM "user"'
+  },
+  params: []
+});
+
