Merge pull request brianc#68 from brianc/auto-join

brianc · brianc · commit 569bcf1bd72e · 2013-05-15T13:57:21.000-07:00
Auto join
diff --git a/lib/joiner.js b/lib/joiner.js
@@ -0,0 +1,39 @@
+var getPrimaryKeyColumn = function(table) {
+  for(var i = 0; i < table.columns.length; i++) {
+    var col = table.columns[i];
+    if(col.primaryKey) {
+      return col;
+    } 
+  }
+};
+
+var findReference = function(left, right) {
+  //find reference
+  for(var i = 0; i < right.columns.length; i++) {
+    var col = right.columns[i];
+    if(col.references) {
+      var leftName = left.getName();
+      if(col.references == leftName || col.references.table == leftName) {
+        var leftCol = left[col.references.column] || getPrimaryKeyColumn(left);
+        return {
+          left: leftCol, 
+          right: col
+        };
+      }
+    }
+  }
+};
+
+module.exports = {
+  //auto-join two tables based on column properties
+  //requires one column to have { references: {table: 'foreignTableName', column: 'foreignColumnName'}}
+  //or to have { references: 'foreignTableName'} -- in which case the foreign table's primary key is assumed
+  leftJoin: function(left, right) {
+    var leftCol, rightCol;
+    var ref = findReference(left, right);
+    if(!ref) {
+      ref = findReference(right, left);
+    }
+    return left.join(right).on(ref.left.equals(ref.right));
+  }
+}
diff --git a/lib/table.js b/lib/table.js
@@ -1,9 +1,12 @@
 'use strict';
 
+var util = require('util');
+
 var Query = require(__dirname + '/node/query');
 var Column = require(__dirname + '/column');
 var TableNode = require(__dirname + '/node/table');
 var JoinNode = require(__dirname + '/node/join');
+var Joiner = require(__dirname + '/joiner');
 
 var Table = function(config) {
   this._schema = config.schema;
@@ -15,6 +18,18 @@ var Table = function(config) {
 
 Table.define = function(config) {
   var table = new Table(config);
+
+  //allow hash of columns as well as array
+  if(config.columns && !util.isArray(config.columns)) {
+    var cols = []
+    for(var key in config.columns) {
+      var col = config.columns[key];
+      col.name = key;
+      cols.push(col);
+    }
+    config.columns = cols;
+  }
+
   for (var i = 0; i < config.columns.length; i++) {
     table.addColumn(config.columns[i]);
   }
@@ -156,6 +171,11 @@ Table.prototype.leftJoin = function(other) {
   return new JoinNode('LEFT', this.toNode(), other.toNode());
 };
 
+//auto-join tables based on column intropsection
+Table.prototype.joinTo = function(other) {
+  return Joiner.leftJoin(this, other);
+};
+
 Table.prototype.as = function(alias) {
   //TODO could this be cleaner?
   var t = Table.define(this._initialConfig);
diff --git a/test/dialects/join-to-tests.js b/test/dialects/join-to-tests.js
@@ -0,0 +1,54 @@
+'use strict';
+var sql = require(__dirname + '/../../lib');
+
+var Harness = require('./support');
+
+var user = sql.define({
+  name: 'user',
+  columns: {
+    id: { primaryKey: true }
+  }
+});
+
+var photo = sql.define({
+  name: 'photo',
+  columns: {
+    ownerId: {
+      references: 'user'
+    }
+  }
+});
+
+var post = sql.define({
+  name: 'post',
+  columns: {
+    id: { primaryKey: true },
+    ownerId: {
+      references: {
+        table: 'user',
+        column: 'id'
+      }
+    }
+  }
+});
+
+Harness.test({
+  query : user.joinTo(post),
+  pg    : '"user" INNER JOIN "post" ON ("user"."id" = "post"."ownerId")',
+  sqlite: '"user" INNER JOIN "post" ON ("user"."id" = "post"."ownerId")',
+  mysql : '`user` INNER JOIN `post` ON (`user`.`id` = `post`.`ownerId`)'
+});
+
+Harness.test({
+  query : post.joinTo(user),
+  pg    : '"post" INNER JOIN "user" ON ("user"."id" = "post"."ownerId")',
+  sqlite: '"post" INNER JOIN "user" ON ("user"."id" = "post"."ownerId")',
+  mysql : '`post` INNER JOIN `user` ON (`user`.`id` = `post`.`ownerId`)'
+});
+
+Harness.test({
+  query : user.joinTo(photo),
+  pg    : '"user" INNER JOIN "photo" ON ("user"."id" = "photo"."ownerId")',
+  sqlite: '"user" INNER JOIN "photo" ON ("user"."id" = "photo"."ownerId")',
+  mysql : '`user` INNER JOIN `photo` ON (`user`.`id` = `photo`.`ownerId`)'
+});
diff --git a/test/table-tests.js b/test/table-tests.js
@@ -82,6 +82,37 @@ test('table with fancier column definitions', function() {
   assert.equal(email.anythingYouWant, 'awesome');
 });
 
+test('table with object structured column definitions', function() {
+  var table = Table.define({
+    name: 'blah',
+    columns: {
+      id: {
+        type: 'serial',
+        notNull: true,
+        primaryKey: true
+      }, 
+      email: {
+        type: 'text',
+        notNull: true,
+        unique: true,
+        anythingYouWant: 'awesome'
+      }
+    }
+  });
+  var cols = table.columns;
+  assert.equal(cols.length, 2);
+  var id = cols[0];
+  assert.equal(id.name, 'id');
+  assert.equal(id.type, 'serial');
+  assert.equal(id.notNull, true);
+  assert.equal(id.primaryKey, true);
+  var email = cols[1];
+  assert.equal(email.name, 'email');
+  assert.equal(email.type, 'text');
+  assert.equal(email.notNull, true);
+  assert.equal(email.unique, true);
+  assert.equal(email.anythingYouWant, 'awesome');
+});
 test('table with dynamic column definition', function() {
   var table = Table.define({ name: 'foo', columns: [] });
   assert.equal(table.columns.length, 0);
