Adding support for Postgres <@, @>, && ARRAY operations

Scott Tadman · Scott Tadman · commit 86c88afbaad3 · 2014-03-31T17:45:10.000-04:00
diff --git a/lib/dialect/postgres.js b/lib/dialect/postgres.js
@@ -142,6 +142,9 @@ Postgres.prototype.visit = function(node) {
     case 'CASE'          : return this.visitCase(node);
     case 'AT'            : return this.visitAt(node);
     case 'SLICE'         : return this.visitSlice(node);
+    case 'CONTAINS'      : return this.visitContains(node);
+    case 'CONTAINED BY'  : return this.visitContainedBy(node);
+    case 'OVERLAP'       : return this.visitOverlap(node);
 
     case 'LIMIT' :
     case 'OFFSET':
@@ -399,6 +402,24 @@ Postgres.prototype.visitSlice = function(slice) {
   return [text];
 };
 
+Postgres.prototype.visitContains = function(contains) {
+  var text = this.visit(contains.value);
+  text += ' @> ' + this.visit(contains.set);
+  return [text];
+};
+
+Postgres.prototype.visitContainedBy = function(containedBy) {
+  var text = this.visit(containedBy.value);
+  text += ' <@ ' + this.visit(containedBy.set);
+  return [text];
+};
+
+Postgres.prototype.visitOverlap = function(overlap) {
+  var text = this.visit(overlap.value);
+  text += ' && ' + this.visit(overlap.set);
+  return [text];
+};
+
 Postgres.prototype.visitQuery = function(queryNode) {
   this._queryNode = queryNode;
   // need to sort the top level query nodes on visitation priority
diff --git a/lib/node/valueExpression.js b/lib/node/valueExpression.js
@@ -61,6 +61,18 @@ var ValueExpressionMixin = function() {
     return new SliceNode(this.toNode(), processParams(start), processParams(end));
   };
 
+  var containsMethod = function(set) {
+    return new ContainsNode(this.toNode(), processParams(set));
+  };
+
+  var containedByMethod = function(set) {
+    return new ContainedByNode(this.toNode(), processParams(set));
+  };
+
+  var overlapMethod = function(set) {
+    return new OverlapNode(this.toNode(), processParams(set));
+  };
+
   var castMethod = function(dataType) {
     return new CastNode(this.toNode(), dataType);
   };
@@ -120,6 +132,9 @@ var ValueExpressionMixin = function() {
     notIn      : binaryMethod('NOT IN'),
     between    : ternaryMethod('BETWEEN', 'AND'),
     at         : atMethod,
+    contains   : binaryMethod('@>'),
+    containedBy : binaryMethod('<@'),
+    overlap    : binaryMethod('&&'),
     slice      : sliceMethod,
     cast       : castMethod,
     descending : orderMethod('DESC'),
diff --git a/test/dialects/array-tests.js b/test/dialects/array-tests.js
@@ -16,6 +16,39 @@ Harness.test({
   params: ['nodejs']
 });
 
+Harness.test({
+  query: post.select(
+    post.tags.contains(Sql.array('nodejs', 'js'))
+  ),
+  pg: {
+    text  : 'SELECT ("post"."tags" @> ARRAY[$1, $2]) FROM "post"',
+    string: 'SELECT ("post"."tags" @> ARRAY[\'nodejs\', \'js\']) FROM "post"'
+  },
+  params: ['nodejs', 'js']
+});
+
+Harness.test({
+  query: post.select(
+    post.tags.containedBy(Sql.array('nodejs', 'js'))
+  ),
+  pg: {
+    text  : 'SELECT ("post"."tags" <@ ARRAY[$1, $2]) FROM "post"',
+    string: 'SELECT ("post"."tags" <@ ARRAY[\'nodejs\', \'js\']) FROM "post"'
+  },
+  params: ['nodejs', 'js']
+});
+
+Harness.test({
+  query: post.select(
+    post.tags.overlap(Sql.array('nodejs', 'js'))
+  ),
+  pg: {
+    text  : 'SELECT ("post"."tags" && ARRAY[$1, $2]) FROM "post"',
+    string: 'SELECT ("post"."tags" && ARRAY[\'nodejs\', \'js\']) FROM "post"'
+  },
+  params: ['nodejs', 'js']
+});
+
 Harness.test({
   query: post.select(post.tags.slice(2,3)),
   pg: {
