support fkeys with more than one column

diff --git a/lib/pq/Gateway/Table.php b/lib/pq/Gateway/Table.php
index 472d0038008a129184d6815ea232e75dc14fcadd..e365e3772f836ce19a58bc8f2e7a788b07a4f4ee 100644 (file)
--- 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->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->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());
                        }
                        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);
                }
                if ($where) {
                        $query->write("WHERE")->criteria($where);

diff --git a/lib/pq/Gateway/Table/Reference.php b/lib/pq/Gateway/Table/Reference.php
index 6f8e29c29caf5d116d2622a08c0b26d99d4fd70f..d91d9c7c0d31078436bf42b87ad771daf6c85730 100644 (file)
--- a/lib/pq/Gateway/Table/Reference.php
+++ b/lib/pq/Gateway/Table/Reference.php
@@ -5,7 +5,7 @@ namespace pq\Gateway\Table;
 /**
  * Foreign key
  */
 /**
  * Foreign key
  */

-class Reference
+class Reference implements \IteratorAggregate

 {
        /**
         * @var string
 {
        /**
         * @var string


@@ -18,9 +18,9 @@ class Reference
        public $foreignTable;
        
        /**
        public $foreignTable;
        
        /**

-        * @var string
+        * @var array

         */
         */

-       public $foreignColumn;
+       public $foreignColumns;

        
        /**
         * @var string
        
        /**
         * @var string


@@ -28,19 +28,19 @@ class Reference
        public $referencedTable;
        
        /**
        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);
        }
        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 700f3fcad524829daf7b009632ac6f669ea8a75a..b96ae661a8b12bd1a256d4775535163c0a25dfed 100644 (file)
--- 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;
 
 
 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
 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"
        ,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
 where
         cl1.relname  = \$1

+and co.contype    = 'f'

 and co.confrelid != 0
 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;
 
 /**
 SQL;
 
 /**


@@ -42,7 +47,7 @@ SQL;
 class Relations implements \Countable, \IteratorAggregate
 {
        /**
 class Relations implements \Countable, \IteratorAggregate
 {
        /**

-        * @var object
+        * @var array

         */
        protected $references;
        
         */
        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) {
                        $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);
                                                }
                                        }
                                        $cache->set("$table:relations", $this->references);


@@ -110,9 +116,9 @@ class Relations implements \Countable, \IteratorAggregate
        
        /**
         * Implements \IteratorAggregate
        
        /**
         * Implements \IteratorAggregate

-        * @return \RecursiveArrayIterator
+        * @return \ArrayIterator

         */
        function getIterator() {
         */
        function getIterator() {

-               return new \RecursiveArrayIterator($this->references);
+               return new \ArrayIterator($this->references);

        }
 }
        }
 }