From d3a497c12f60567cbb20ea626fd909f94955ebac Mon Sep 17 00:00:00 2001
From: Michael Wallner <mike@php.net>
Date: Wed, 15 Oct 2014 10:34:22 +0200
Subject: [PATCH] support fkeys with more than one column

---
 lib/pq/Gateway/Table.php           | 33 +++++++++------
 lib/pq/Gateway/Table/Reference.php | 44 +++++++++++++------
 lib/pq/Gateway/Table/Relations.php | 68 ++++++++++++++++--------------
 3 files changed, 89 insertions(+), 56 deletions(-)

diff --git a/lib/pq/Gateway/Table.php b/lib/pq/Gateway/Table.php
index 472d003..e365e37 100644
--- a/lib/pq/Gateway/Table.php
+++ b/lib/pq/Gateway/Table.php
@@ -360,10 +360,12 @@ class Table implements \SplSubject
 			return $this->onResult(null);
 		}
 		
-		return $this->find(
-			array($rel->foreignColumn . "=" => $foreign->{$rel->referencedColumn}),
-			$order, $limit, $offset
-		);
+		$where = array();
+		foreach ($rel as $key => $ref) {
+			$where["$key="] = $foreign->$ref;
+		}
+		
+		return $this->find($where, $order, $limit, $offset);
 	}
 	
 	/**
@@ -379,9 +381,11 @@ class Table implements \SplSubject
 			return $this->onResult(null);
 		}
 		
-		return $this->find(
-			array($rel->referencedColumn . "=" => $foreign->{$rel->foreignColumn})
-		);
+		$where = array();
+		foreach ($rel as $key => $ref) {
+			$where["$ref="] = $foreign->$key;
+		}
+		return $this->find($where);
 	}
 	
 	/**
@@ -401,12 +405,15 @@ class Table implements \SplSubject
 			if (!($relation instanceof Table\Reference)) {
 				$relation = static::resolve($relation)->getRelation($this->getName());
 			}
-			$query->write("JOIN", $relation->foreignTable)->write("ON")->criteria(
-				array(
-					"{$relation->referencedTable}.{$relation->referencedColumn}=" => 
-						new QueryExpr("{$relation->foreignTable}.{$relation->foreignColumn}")
-				)
-			);
+			$query->write("JOIN", $relation->foreignTable)->write("ON");
+			foreach ($relation as $key => $ref) {
+				$query->criteria(
+					array(
+						"{$relation->referencedTable}.{$ref}=" => 
+							new QueryExpr("{$relation->foreignTable}.{$key}")
+					)
+				);
+			}
 		}
 		if ($where) {
 			$query->write("WHERE")->criteria($where);
diff --git a/lib/pq/Gateway/Table/Reference.php b/lib/pq/Gateway/Table/Reference.php
index 6f8e29c..d91d9c7 100644
--- a/lib/pq/Gateway/Table/Reference.php
+++ b/lib/pq/Gateway/Table/Reference.php
@@ -5,7 +5,7 @@ namespace pq\Gateway\Table;
 /**
  * Foreign key
  */
-class Reference
+class Reference implements \IteratorAggregate
 {
 	/**
 	 * @var string
@@ -18,9 +18,9 @@ class Reference
 	public $foreignTable;
 	
 	/**
-	 * @var string
+	 * @var array
 	 */
-	public $foreignColumn;
+	public $foreignColumns;
 	
 	/**
 	 * @var string
@@ -28,19 +28,19 @@ class Reference
 	public $referencedTable;
 	
 	/**
-	 * @var string
+	 * @var array
 	 */
-	public $referencedColumn;
+	public $referencedColumns;
 	
 	/**
-	 * @param array $state
+	 * @param array $ref
 	 */
-	function __construct($state) {
-		$this->name = $state["name"];
-		$this->foreignColumn = $state["foreignColumn"];
-		$this->foreignTable = $state["foreignTable"];
-		$this->referencedColumn = $state["referencedColumn"];
-		$this->referencedTable = $state["referencedTable"];
+	function __construct($ref) {
+		$this->name = self::name($ref);
+		$this->foreignTable = $ref["foreignTable"];
+		$this->foreignColumns = $ref["foreignColumns"];
+		$this->referencedTable = $ref["referencedTable"];
+		$this->referencedColumns = $ref["referencedColumns"];
 	}
 	
 	/**
@@ -50,4 +50,24 @@ class Reference
 	static function __set_state($state) {
 		return new static($state);
 	}
+	
+	/**
+	 * Compose an identifying name
+	 * @param array $ref
+	 * @return string
+	 */
+	static function name($ref) {
+		return implode("_", array_map(function($ck, $cr) {
+			return preg_replace("/_$cr\$/", "", $ck);
+		}, $ref["foreignColumns"], $ref["referencedColumns"]));
+	}
+	
+	/**
+	 * Implements IteratorAggregate
+	 * @return \ArrayIterator
+	 */
+	function getIterator() {
+		return new \ArrayIterator(array_combine(
+			$this->foreignColumns, $this->referencedColumns));
+	}
 }
diff --git a/lib/pq/Gateway/Table/Relations.php b/lib/pq/Gateway/Table/Relations.php
index 700f3fc..b96ae66 100644
--- a/lib/pq/Gateway/Table/Relations.php
+++ b/lib/pq/Gateway/Table/Relations.php
@@ -4,36 +4,41 @@ namespace pq\Gateway\Table;
 
 use \pq\Gateway\Table;
 
-/*
- * 	 case when att1.attname like '%\_'||att2.attname then
-		substring(att1.attname from '^.*(?=_'||att2.attname||'$)')
-	 else
-		att1.attname
-	 end
- */
 const RELATION_SQL = <<<SQL
 select
-	regexp_replace(att1.attname, '_'||att2.attname||'$', '')
-                  as "name"
-	,cl1.relname  as "foreignTable"
-	,att1.attname as "foreignColumn"
+	 cl1.relname  as "foreignTable"
+	,array(
+		select 
+			 attname
+			from pg_attribute,
+			generate_subscripts(conkey,1) index
+		where 
+			attrelid = cl1.oid 
+		and attnum   = any(conkey) 
+		and conkey[index] = attnum
+		order by
+			 index
+	)             as "foreignColumns"
 	,cl2.relname  as "referencedTable"
-	,att2.attname as "referencedColumn"
-from
-     pg_constraint co
-    ,pg_class      cl1
-    ,pg_class      cl2
-    ,pg_attribute  att1
-    ,pg_attribute  att2
+	,array(
+		select 
+			 attname
+			from pg_attribute,
+			generate_subscripts(confkey,1) index
+		where 
+			attrelid = cl2.oid 
+		and attnum   = any(confkey) 
+		and confkey[index] = attnum
+		order by
+			 index
+	)             as "referencedColumns"
+from pg_constraint co
+join pg_class      cl1 on cl1.oid = co.conrelid
+join pg_class      cl2 on cl2.oid = co.confrelid
 where
 	 cl1.relname  = \$1
+and co.contype    = 'f'
 and co.confrelid != 0
-and co.conrelid   = cl1.oid
-and co.conkey[1]  = att1.attnum and cl1.oid = att1.attrelid
-and co.confrelid  = cl2.oid
-and co.confkey[1] = att2.attnum and cl2.oid = att2.attrelid
-order by 
-	att1.attnum
 SQL;
 
 /**
@@ -42,7 +47,7 @@ SQL;
 class Relations implements \Countable, \IteratorAggregate
 {
 	/**
-	 * @var object
+	 * @var array
 	 */
 	protected $references;
 	
@@ -55,10 +60,11 @@ class Relations implements \Countable, \IteratorAggregate
 			$table->getQueryExecutor()->execute(
 				new \pq\Query\Writer(RELATION_SQL, array($table->getName())),
 				function($result) use($table, $cache) {
-					$rel = $result->map([3,0], null, \pq\Result::FETCH_ASSOC);
-					foreach ($rel as $table => $reference) {
-						foreach ($reference as $name => $ref) {
-							$this->references[$table][$name] = new Reference($ref);
+					$rel = $result->map([1,2], null, \pq\Result::FETCH_ASSOC);
+					foreach ($rel as $ref) {
+						foreach ($ref as $table => $key) {
+							$reference = new Reference($key);
+							$this->references[$table][$reference->name] = $reference;
 						}
 					}
 					$cache->set("$table:relations", $this->references);
@@ -110,9 +116,9 @@ class Relations implements \Countable, \IteratorAggregate
 	
 	/**
 	 * Implements \IteratorAggregate
-	 * @return \RecursiveArrayIterator
+	 * @return \ArrayIterator
 	 */
 	function getIterator() {
-		return new \RecursiveArrayIterator($this->references);
+		return new \ArrayIterator($this->references);
 	}
 }
-- 
2.30.2