Repository: doctrine/dbal (php)

Issue:
Add a fetch mode methods for "PDO::FETCH_KEY_PAIR"
### Feature Request

|    Q        |   A
|------------ | ------
| New Feature | yes
| RFC         | no
| BC Break    | no

#### Summary

Let's assume a sql, summarizing entity ids and a special count. Then I need the workflow like the default PDO does:

<pre lang=php>
public function countOrders()
{
    $sql = 'SELECT `e`.`id`, COUNT(`o`.`id`) 
        FROM `event` AS `e` 
        LEFT JOIN `order` AS `o` ON `o`.`event_id` = `e`.`id`
        WHERE `o`.`deleted` = 0
        GROUP BY `e`.`id` ';

    $dbConnection = $this->getEntityManager()->getConnection();
    $sqlStatement = $dbConnection->prepare($sql);
    $sqlStatement->execute();

    $counts = $sqlStatement->fetchAll(PDO::FETCH_KEY_PAIR); 
    $sqlStatement->free();

    return $counts;
}
</pre>

The result was an array with the ids as keys and counts as values, like:
<pre lang=php>
array (2) [
    11 => integer 10
    22 => integer 5
]
</pre>

Since the old fetch methods are deprecated, it would be great to implement 
an adequate method to the PDO feature to reduce boilerplate code.

Here is an quote from @morozov of #4019 
<pre lang=php>
$conn = new PDO('sqlite::memory:');
$stmt = $conn->query('SELECT 1');

$stmt->fetchAll(PDO::FETCH_KEY_PAIR);

// PDOStatement::fetchAll(): SQLSTATE[HY000]: General error: PDO::FETCH_KEY_PAIR fetch mode requires the result set to contain extactly 2 columns
</pre>

As @morozov mentioned:
> Looks like PDO is strict enough about the number of columns. I think we can implement this in DBAL at the wrapper level which will automatically cover all supported drivers.



Pull request description:
Add Result::fetchAllKeyValue() and ::iterateKeyValue()
|      Q       |   A
|------------- | -----------
| Type         | improvement
| BC Break     | no

Fixes #4289.

The behavior of the newly added methods is a replacement of `PDO::FETCH_KEY_PAIR` which is implemented at the wrapper level and is available for all supported drivers.

Unlike PDO which requires the result to have exactly two columns, DBAL will require at least two columns in order to allow the ROWNUM columns used by the platforms that do not natively support LIIMIT queries (Oracle, IBM DB2).

Patch:
diff --git a/docs/en/reference/data-retrieval-and-manipulation.rst b/docs/en/reference/data-retrieval-and-manipulation.rst
index 8a88deb6a..0efba4c46 100644
--- a/docs/en/reference/data-retrieval-and-manipulation.rst
+++ b/docs/en/reference/data-retrieval-and-manipulation.rst
@@ -379,6 +379,25 @@ Execute the query and fetch all results into an array:
     )
     */
 
+fetchAllKeyValue()
+~~~~~~~~~~~~~~~~~~
+
+Execute the query and fetch the first two columns into an associative array as keys and values respectively:
+
+.. code-block:: php
+
+    <?php
+    $users = $conn->fetchAllKeyValue('SELECT username, password FROM user');
+
+    /*
+    array(
+      'jwage' => 'changeme',
+    )
+    */
+
+.. note::
+   All additional columns will be ignored and are only allowed to be selected by DBAL for its internal purposes.
+
 fetchNumeric()
 ~~~~~~~~~~~~~~
 
@@ -425,6 +444,21 @@ Retrieve associative array of the first result row.
 
 There are also convenience methods for data manipulation queries:
 
+iterateKeyValue()
+~~~~~~~~~~~~~~~~~
+
+Execute the query and iterate over the first two columns as keys and values respectively:
+
+.. code-block:: php
+
+    <?php
+    foreach ($conn->iterateKeyValue('SELECT username, password FROM user') as $username => $password) {
+        // ...
+    }
+
+.. note::
+   All additional columns will be ignored and are only allowed to be selected by DBAL for its internal purposes.
+
 delete()
 ~~~~~~~~~
 
diff --git a/src/Abstraction/Result.php b/src/Abstraction/Result.php
index 93d0e4643..b63ce8e56 100644
--- a/src/Abstraction/Result.php
+++ b/src/Abstraction/Result.php
@@ -14,6 +14,17 @@ use Traversable;
  */
 interface Result extends DriverResult
 {
+    /**
+     * Returns an associative array with the keys mapped to the first column and the values mapped to the second column.
+     *
+     * The result must contain at least two columns.
+     *
+     * @return array<mixed,mixed>
+     *
+     * @throws Exception
+     */
+    public function fetchAllKeyValue(): array;
+
     /**
      * Returns an iterator over the result set rows represented as numeric arrays.
      *
@@ -32,6 +43,18 @@ interface Result extends DriverResult
      */
     public function iterateAssociative(): Traversable;
 
+    /**
+     * Returns an iterator over the result set with the keys mapped to the first column
+     * and the values mapped to the second column.
+     *
+     * The result must contain at least two columns.
+     *
+     * @return Traversable<mixed,mixed>
+     *
+     * @throws Exception
+     */
+    public function iterateKeyValue(): Traversable;
+
     /**
      * Returns an iterator over the values of the first column of the result set.
      *
diff --git a/src/Connection.php b/src/Connection.php
index 715ab0f69..096ee99f8 100644
--- a/src/Connection.php
+++ b/src/Connection.php
@@ -817,6 +817,23 @@ class Connection
         }
     }
 
+    /**
+     * Prepares and executes an SQL query and returns the result as an associative array with the keys
+     * mapped to the first column and the values mapped to the second column.
+     *
+     * @param string                                           $query  The SQL query.
+     * @param array<int, mixed>|array<string, mixed>           $params The query parameters.
+     * @param array<int, int|string>|array<string, int|string> $types  The query parameter types.
+     *
+     * @return array<mixed,mixed>
+     *
+     * @throws Exception
+     */
+    public function fetchAllKeyValue(string $query, array $params = [], array $types = []): array
+    {
+        return $this->executeQuery($query, $params, $types)->fetchAllKeyValue();
+    }
+
     /**
      * Prepares and executes an SQL query and returns the result as an array of the first column values.
      *
@@ -886,6 +903,23 @@ class Connection
         }
     }
 
+    /**
+     * Prepares and executes an SQL query and returns the result as an iterator with the keys
+     * mapped to the first column and the values mapped to the second column.
+     *
+     * @param string                                           $query  The SQL query.
+     * @param array<int, mixed>|array<string, mixed>           $params The query parameters.
+     * @param array<int, int|string>|array<string, int|string> $types  The query parameter types.
+     *
+     * @return Traversable<mixed,mixed>
+     *
+     * @throws Exception
+     */
+    public function iterateKeyValue(string $query, array $params = [], array $types = []): Traversable
+    {
+        return $this->executeQuery($query, $params, $types)->iterateKeyValue();
+    }
+
     /**
      * Prepares and executes an SQL query and returns the result as an iterator over the first column values.
      *
diff --git a/src/Exception/NoKeyValue.php b/src/Exception/NoKeyValue.php
new file mode 100644
index 000000000..34093704a
--- /dev/null
+++ b/src/Exception/NoKeyValue.php
@@ -0,0 +1,25 @@
+<?php
+
+namespace Doctrine\DBAL\Exception;
+
+use Doctrine\DBAL\Exception;
+
+use function sprintf;
+
+/**
+ * @internal
+ *
+ * @psalm-immutable
+ */
+final class NoKeyValue extends Exception
+{
+    public static function fromColumnCount(int $columnCount): self
+    {
+        return new self(
+            sprintf(
+                'Fetching as key-value pairs requires the result to contain at least 2 columns, %d given.',
+                $columnCount
+            )
+        );
+    }
+}
diff --git a/src/Result.php b/src/Result.php
index fca81846b..80d3a4ba6 100644
--- a/src/Result.php
+++ b/src/Result.php
@@ -7,6 +7,7 @@ namespace Doctrine\DBAL;
 use Doctrine\DBAL\Abstraction\Result as ResultInterface;
 use Doctrine\DBAL\Driver\Exception as DriverException;
 use Doctrine\DBAL\Driver\Result as DriverResult;
+use Doctrine\DBAL\Exception\NoKeyValue;
 use Traversable;
 
 final class Result implements ResultInterface
@@ -96,6 +97,24 @@ final class Result implements ResultInterface
         }
     }
 
+    /**
+     * {@inheritDoc}
+     *
+     * @throws Exception
+     */
+    public function fetchAllKeyValue(): array
+    {
+        $this->ensureHasKeyValue();
+
+        $data = [];
+
+        foreach ($this->fetchAllNumeric() as [$key, $value]) {
+            $data[$key] = $value;
+        }
+
+        return $data;
+    }
+
     /**
      * {@inheritDoc}
      *
@@ -142,6 +161,20 @@ final class Result implements ResultInterface
         }
     }
 
+    /**
+     * {@inheritDoc}
+     *
+     * @throws Exception
+     */
+    public function iterateKeyValue(): Traversable
+    {
+        $this->ensureHasKeyValue();
+
+        foreach ($this->iterateNumeric() as [$key, $value]) {
+            yield $key => $value;
+        }
+    }
+
     /**
      * @return Traversable<int,mixed>
      *
@@ -158,18 +191,44 @@ final class Result implements ResultInterface
         }
     }
 
+    /**
+     * @throws Exception
+     */
     public function rowCount(): int
     {
-        return $this->result->rowCount();
+        try {
+            return $this->result->rowCount();
+        } catch (DriverException $e) {
+            throw $this->connection->convertException($e);
+        }
     }
 
+    /**
+     * @throws Exception
+     */
     public function columnCount(): int
     {
-        return $this->result->columnCount();
+        try {
+            return $this->result->columnCount();
+        } catch (DriverException $e) {
+            throw $this->connection->convertException($e);
+        }
     }
 
     public function free(): void
     {
         $this->result->free();
     }
+
+    /**
+     * @throws Exception
+     */
+    private function ensureHasKeyValue(): void
+    {
+        $columnCount = $this->columnCount();
+
+        if ($columnCount < 2) {
+            throw NoKeyValue::fromColumnCount($columnCount);
+        }
+    }
 }


Test patch:
diff --git a/tests/Functional/Connection/FetchTest.php b/tests/Functional/Connection/FetchTest.php
index ce6b5a372..a6a5d341c 100644
--- a/tests/Functional/Connection/FetchTest.php
+++ b/tests/Functional/Connection/FetchTest.php
@@ -2,6 +2,8 @@
 
 namespace Doctrine\DBAL\Tests\Functional\Connection;
 
+use Doctrine\DBAL\Exception\NoKeyValue;
+use Doctrine\DBAL\Platforms\SQLServer2012Platform;
 use Doctrine\DBAL\Tests\FunctionalTestCase;
 use Doctrine\DBAL\Tests\TestUtil;
 
@@ -77,6 +79,41 @@ class FetchTest extends FunctionalTestCase
         ], $this->connection->fetchAllAssociative($this->query));
     }
 
+    public function testFetchAllKeyValue(): void
+    {
+        self::assertEquals([
+            'foo' => 1,
+            'bar' => 2,
+            'baz' => 3,
+        ], $this->connection->fetchAllKeyValue($this->query));
+    }
+
+    /**
+     * This test covers the requirement for the statement result to have at least two columns,
+     * not exactly two as PDO requires.
+     */
+    public function testFetchAllKeyValueWithLimit(): void
+    {
+        $platform = $this->connection->getDatabasePlatform();
+
+        if ($platform instanceof SQLServer2012Platform) {
+            self::markTestSkipped('See https://github.com/doctrine/dbal/issues/2374');
+        }
+
+        $query = $platform->modifyLimitQuery($this->query, 1, 1);
+
+        self::assertEquals(['bar' => 2], $this->connection->fetchAllKeyValue($query));
+    }
+
+    public function testFetchAllKeyValueOneColumn(): void
+    {
+        $sql = $this->connection->getDatabasePlatform()
+            ->getDummySelectSQL();
+
+        $this->expectException(NoKeyValue::class);
+        $this->connection->fetchAllKeyValue($sql);
+    }
+
     public function testFetchFirstColumn(): void
     {
         self::assertEquals([
@@ -113,6 +150,24 @@ class FetchTest extends FunctionalTestCase
         ], iterator_to_array($this->connection->iterateAssociative($this->query)));
     }
 
+    public function testIterateKeyValue(): void
+    {
+        self::assertEquals([
+            'foo' => 1,
+            'bar' => 2,
+            'baz' => 3,
+        ], iterator_to_array($this->connection->iterateKeyValue($this->query)));
+    }
+
+    public function testIterateKeyValueOneColumn(): void
+    {
+        $sql = $this->connection->getDatabasePlatform()
+            ->getDummySelectSQL();
+
+        $this->expectException(NoKeyValue::class);
+        iterator_to_array($this->connection->iterateKeyValue($sql));
+    }
+
     public function testIterateColumn(): void
     {
         self::assertEquals([