Fixes #14658 - Regression on multi table query (#14681)

Added functionality to generate SELECT query with JOINs of requested tables. It was mentioned that it's just a bug and it was working in previous versions, but I did not see any code fragments that would make it possible. Please, feel free to suggest any better solution if you don't like that one.

Fixes #14658

Signed-off-by: Aleš Buľko <ales.bulko@student.tuke.sk>
(cherry picked from commit 23ce7cec521f830736cb6ee177ec4c3d03516df2)
Signed-off-by: Maurício Meneghini Fauth <mauriciofauth@gmail.com>

2 files changed, 48 insertions, 0 deletions

diff --git a/libraries/classes/DatabaseInterface.php b/libraries/classes/DatabaseInterface.php
index 095545020f..78172e4618 100644
--- a/libraries/classes/DatabaseInterface.php
+++ b/libraries/classes/DatabaseInterface.php
@@ -392,6 +392,43 @@ class DatabaseInterface
         return $tables;
     }
 
+
+    /**
+     * returns
+     *
+     * @param string $database name of database
+     * @param array  $tables   list of tables to search for for relations
+     * @param int    $link     mysql link resource|object
+     *
+     * @return array           array of found foreign keys
+     */
+    public function getForeignKeyConstrains($database, array $tables, $link = DatabaseInterface::CONNECT_USER)
+    {
+        $tablesListForQuery = '';
+        foreach($tables as $table){
+            $tablesListForQuery .= "'" . $this->escapeString($table) . "',";
+        }
+        $tablesListForQuery = rtrim($tablesListForQuery, ',');
+
+        $foreignKeyConstrains = $this->fetchResult(
+            "SELECT"
+                    . " TABLE_NAME,"
+                    . " COLUMN_NAME,"
+                    . " REFERENCED_TABLE_NAME,"
+                    . " REFERENCED_COLUMN_NAME"
+                . " FROM information_schema.key_column_usage"
+                . " WHERE referenced_table_name IS NOT NULL"
+                    . " AND TABLE_SCHEMA = '" . $this->escapeString($database) . "'"
+                    . " AND TABLE_NAME IN (" . $tablesListForQuery . ")"
+                    . " AND REFERENCED_TABLE_NAME IN (" . $tablesListForQuery . ");",
+            null,
+            null,
+            $link,
+            self::QUERY_STORE
+        );
+        return $foreignKeyConstrains;
+    }
+
     /**
      * returns a segment of the SQL WHERE clause regarding table name and type
      *
diff --git a/libraries/dbi/dbi_dummy.inc.php b/libraries/dbi/dbi_dummy.inc.php
index 75434e7439..0d29c989f3 100644
--- a/libraries/dbi/dbi_dummy.inc.php
+++ b/libraries/dbi/dbi_dummy.inc.php
@@ -868,6 +868,17 @@ $GLOBALS['dummy_queries'] = array(
         'query' => "SELECT *, `COLUMN_NAME` AS `Field`, `COLUMN_TYPE` AS `Type`, `COLLATION_NAME` AS `Collation`, `IS_NULLABLE` AS `Null`, `COLUMN_KEY` AS `Key`, `COLUMN_DEFAULT` AS `Default`, `EXTRA` AS `Extra`, `PRIVILEGES` AS `Privileges`, `COLUMN_COMMENT` AS `Comment` FROM `information_schema`.`COLUMNS` WHERE `TABLE_SCHEMA` = 'information_schema' AND `TABLE_NAME` = 'PMA'",
         'result' => array(),
     ),
+    [
+        'query' => "SELECT TABLE_NAME, COLUMN_NAME, REFERENCED_TABLE_NAME, REFERENCED_COLUMN_NAME FROM information_schema.key_column_usage WHERE referenced_table_name IS NOT NULL AND TABLE_SCHEMA = 'test' AND TABLE_NAME IN ('table1','table2') AND REFERENCED_TABLE_NAME IN ('table1','table2');",
+        'result' => [
+            [
+                'TABLE_NAME' => 'table2',
+                'COLUMN_NAME' => 'idtable2',
+                'REFERENCED_TABLE_NAME' => 'table1',
+                'REFERENCED_COLUMN_NAME' => 'idtable1',
+            ]
+        ],
+    ],
 );
 /**
  * Current database.