Repository: doctrine/dbal (php)

Issue:
FetchMode::GROUP, FetchMode::UNIQUE are missing
### BC Break Report

|    Q        |   A
|------------ | ------
| BC Break    | yes
| Version     | 2.7

#### Summary

My code spits this: `PHP Deprecated: Using a PDO fetch mode or their combination (196610 given) is deprecated and will cause an error in Doctrine DBAL 3.0`, so I'm trying to [upgrade](https://github.com/doctrine/dbal/blob/v2.7.0/UPGRADE.md#deprecation-direct-usage-of-the-pdo-apis-in-the-dbal-api)

This should be the part where my code is unfit:

`$stmt->fetchAll(\PDO::FETCH_GROUP|\PDO::FETCH_UNIQUE|\PDO::FETCH_ASSOC)`

As suggested in the upgrade.MD, I tried to replace it with this:

`$stmt->fetchAll(FetchMode::GROUP|FetchMode::UNIQUE|FetchMode::ASSOCIATIVE)`

But it doesn't work, because the `FetchMode` class doesn't define neither FetchMode::GROUP nor FetchMode::UNIQUE 

What am I missing here?

Thanks!

Pull request:
Add Statement::fetchAllIndexedAssociative() and ::iterateIndexedAssociative()
|      Q       |   A
|------------- | -----------
| Type         | improvement
| BC Break     | no

Closes #4206.

The fetch mode is analogous to the PDO's `FETCH_GROUP|FETCH_UNIQUE|FETCH_ASSOC`. Based on the `fetchIndexed()` name proposed in https://github.com/doctrine/dbal/issues/4206#issuecomment-706617488, I decided to name the method as `fetchAllIndexedAssociative()` for the following reasons:
1. It should have the "all" prefix for consistency with the rest of `fetchAll*()` methods.
2. It should have an "associative" suffix to be more specific and allow for the future addition of `fetchAllIndexedNumeric()` (although it's less likely to be requested).
3. It's more developer-friendly than `fetchAllAssociativeIndexed()` from the code completion standpoint since it shares fewer leading characters with `fetchAllAssociative()`.

Patch:
diff --git a/docs/en/reference/data-retrieval-and-manipulation.rst b/docs/en/reference/data-retrieval-and-manipulation.rst
index 0efba4c46..7e85e3c3a 100644
--- a/docs/en/reference/data-retrieval-and-manipulation.rst
+++ b/docs/en/reference/data-retrieval-and-manipulation.rst
@@ -398,6 +398,26 @@ Execute the query and fetch the first two columns into an associative array as k
 .. note::
    All additional columns will be ignored and are only allowed to be selected by DBAL for its internal purposes.
 
+fetchAllAssociativeIndexed()
+~~~~~~~~~~~~~~~~~~~~~~~~~~~~
+
+Execute the query and fetch the data as an associative array where the key represents the first column and the value is
+an associative array of the rest of the columns and their values:
+
+.. code-block:: php
+
+    <?php
+    $users = $conn->fetchAllAssociativeIndexed('SELECT id, username, password FROM user');
+
+    /*
+    array(
+        1 => array(
+          'username' => 'jwage',
+          'password' => 'changeme',
+        )
+    )
+    */
+
 fetchNumeric()
 ~~~~~~~~~~~~~~
 
@@ -459,6 +479,19 @@ Execute the query and iterate over the first two columns as keys and values resp
 .. note::
    All additional columns will be ignored and are only allowed to be selected by DBAL for its internal purposes.
 
+iterateAssociativeIndexed()
+~~~~~~~~~~~~~~~~~~~~~~~~~~~
+
+Execute the query and iterate over the result with the key representing the first column and the value being
+an associative array of the rest of the columns and their values:
+
+.. code-block:: php
+
+    <?php
+    foreach ($conn->iterateAssociativeIndexed('SELECT id, username, password FROM user') as $id => $data) {
+        // ...
+    }
+
 delete()
 ~~~~~~~~~
 
diff --git a/lib/Doctrine/DBAL/Connection.php b/lib/Doctrine/DBAL/Connection.php
index 1d9477431..2d5d4402b 100644
--- a/lib/Doctrine/DBAL/Connection.php
+++ b/lib/Doctrine/DBAL/Connection.php
@@ -25,6 +25,7 @@ use Throwable;
 use Traversable;
 
 use function array_key_exists;
+use function array_shift;
 use function assert;
 use function func_get_args;
 use function implode;
@@ -990,9 +991,9 @@ class Connection implements DriverConnection
      * 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.
+     * @param string                                           $query  SQL query
+     * @param array<int, mixed>|array<string, mixed>           $params Query parameters
+     * @param array<int, int|string>|array<string, int|string> $types  Parameter types
      *
      * @return array<mixed,mixed>
      *
@@ -1013,6 +1014,32 @@ class Connection implements DriverConnection
         return $data;
     }
 
+    /**
+     * 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 being an associative array representing the rest of the columns
+     * and their values.
+     *
+     * @param string                                           $query  SQL query
+     * @param array<int, mixed>|array<string, mixed>           $params Query parameters
+     * @param array<int, int|string>|array<string, int|string> $types  Parameter types
+     *
+     * @return array<mixed,array<string,mixed>>
+     *
+     * @throws Exception
+     */
+    public function fetchAllAssociativeIndexed(string $query, array $params = [], array $types = []): array
+    {
+        $stmt = $this->executeQuery($query, $params, $types);
+
+        $data = [];
+
+        foreach ($stmt->fetchAll(FetchMode::ASSOCIATIVE) as $row) {
+            $data[array_shift($row)] = $row;
+        }
+
+        return $data;
+    }
+
     /**
      * Prepares and executes an SQL query and returns the result as an array of the first column values.
      *
@@ -1100,9 +1127,9 @@ class Connection implements DriverConnection
      * 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.
+     * @param string                                           $query  SQL query
+     * @param array<int, mixed>|array<string, mixed>           $params Query parameters
+     * @param array<int, int|string>|array<string, int|string> $types  Parameter types
      *
      * @return Traversable<mixed,mixed>
      *
@@ -1119,6 +1146,28 @@ class Connection implements DriverConnection
         }
     }
 
+    /**
+     * Prepares and executes an SQL query and returns the result as an iterator with the keys mapped
+     * to the first column and the values being an associative array representing the rest of the columns
+     * and their values.
+     *
+     * @param string                                           $query  SQL query
+     * @param array<int, mixed>|array<string, mixed>           $params Query parameters
+     * @param array<int, int|string>|array<string, int|string> $types  Parameter types
+     *
+     * @return Traversable<mixed,array<string,mixed>>
+     *
+     * @throws Exception
+     */
+    public function iterateAssociativeIndexed(string $query, array $params = [], array $types = []): Traversable
+    {
+        $stmt = $this->executeQuery($query, $params, $types);
+
+        while (($row = $stmt->fetch(FetchMode::ASSOCIATIVE)) !== false) {
+            yield array_shift($row) => $row;
+        }
+    }
+
     /**
      * Prepares and executes an SQL query and returns the result as an iterator over the first column values.
      *
diff --git a/lib/Doctrine/DBAL/Statement.php b/lib/Doctrine/DBAL/Statement.php
index ff27cd13a..ad5d29b9a 100644
--- a/lib/Doctrine/DBAL/Statement.php
+++ b/lib/Doctrine/DBAL/Statement.php
@@ -13,6 +13,7 @@ use PDO;
 use Throwable;
 use Traversable;
 
+use function array_shift;
 use function is_array;
 use function is_string;
 
@@ -398,6 +399,25 @@ class Statement implements IteratorAggregate, DriverStatement, Result
         return $data;
     }
 
+    /**
+     * Returns an associative array with the keys mapped to the first column and the values being
+     * an associative array representing the rest of the columns and their values.
+     *
+     * @return array<mixed,array<string,mixed>>
+     *
+     * @throws Exception
+     */
+    public function fetchAllAssociativeIndexed(): array
+    {
+        $data = [];
+
+        foreach ($this->fetchAll(FetchMode::ASSOCIATIVE) as $row) {
+            $data[array_shift($row)] = $row;
+        }
+
+        return $data;
+    }
+
     /**
      * {@inheritdoc}
      *
@@ -483,6 +503,21 @@ class Statement implements IteratorAggregate, DriverStatement, Result
         }
     }
 
+    /**
+     * Returns an iterator over the result set with the keys mapped to the first column and the values being
+     * an associative array representing the rest of the columns and their values.
+     *
+     * @return Traversable<mixed,array<string,mixed>>
+     *
+     * @throws Exception
+     */
+    public function iterateAssociativeIndexed(): Traversable
+    {
+        while (($row = $this->stmt->fetch(FetchMode::ASSOCIATIVE)) !== false) {
+            yield array_shift($row) => $row;
+        }
+    }
+
     /**
      * {@inheritDoc}
      *


Test patch:
diff --git a/tests/Doctrine/Tests/DBAL/Functional/Connection/FetchTest.php b/tests/Doctrine/Tests/DBAL/Functional/Connection/FetchTest.php
index 49854b3e1..e5f4d2472 100644
--- a/tests/Doctrine/Tests/DBAL/Functional/Connection/FetchTest.php
+++ b/tests/Doctrine/Tests/DBAL/Functional/Connection/FetchTest.php
@@ -126,6 +126,27 @@ class FetchTest extends DbalFunctionalTestCase
         $this->connection->fetchAllKeyValue($sql);
     }
 
+    public function testFetchAllAssociativeIndexed(): void
+    {
+        self::assertEquals([
+            'foo' => ['b' => 1],
+            'bar' => ['b' => 2],
+            'baz' => ['b' => 3],
+        ], $this->connection->fetchAllAssociativeIndexed($this->query));
+    }
+
+    public function testStatementFetchAllAssociativeIndexed(): void
+    {
+        $stmt = $this->connection->prepare($this->query);
+        $stmt->execute();
+
+        self::assertEquals([
+            'foo' => ['b' => 1],
+            'bar' => ['b' => 2],
+            'baz' => ['b' => 3],
+        ], $stmt->fetchAllAssociativeIndexed());
+    }
+
     public function testFetchFirstColumn(): void
     {
         self::assertEquals([
@@ -171,7 +192,7 @@ class FetchTest extends DbalFunctionalTestCase
         ], iterator_to_array($this->connection->iterateKeyValue($this->query)));
     }
 
-    public function testStatementKeyValue(): void
+    public function testStatementIterateKeyValue(): void
     {
         $stmt = $this->connection->prepare($this->query);
         $stmt->execute();
@@ -192,6 +213,27 @@ class FetchTest extends DbalFunctionalTestCase
         iterator_to_array($this->connection->iterateKeyValue($sql));
     }
 
+    public function testIterateAssociativeIndexed(): void
+    {
+        self::assertEquals([
+            'foo' => ['b' => 1],
+            'bar' => ['b' => 2],
+            'baz' => ['b' => 3],
+        ], iterator_to_array($this->connection->iterateAssociativeIndexed($this->query)));
+    }
+
+    public function testStatementIterateAssociativeIndexed(): void
+    {
+        $stmt = $this->connection->prepare($this->query);
+        $stmt->execute();
+
+        self::assertEquals([
+            'foo' => ['b' => 1],
+            'bar' => ['b' => 2],
+            'baz' => ['b' => 3],
+        ], iterator_to_array($stmt->iterateAssociativeIndexed()));
+    }
+
     public function testIterateColumn(): void
     {
         self::assertEquals([