Add a fetch mode methods for "PDO::FETCH_KEY_PAIR"

[tswcode]

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:

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;
}

The result was an array with the ids as keys and counts as values, like:

array (2) [
    11 => integer 10
    22 => integer 5
]

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

$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

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.

[morozov]

$sqlStatement->fetchAll(PDO::FETCH_KEY_PAIR, 0);

@tswcode what does the second argument in this call mean? I cannot find any documentation for it.

[tswcode]

Oh sorry, that was copy and paste. I guess it previously was an PDO::FETCH_COLUMN. I'll edit the first post.

[Gamesh]

here is a good documentation of this and all the other modes that are now not accessible from DBAL https://phpdelusions.net/pdo/fetch_modes

[morozov]

Closed via #4338.

[uncaught]

@morozov I am a bit confused about the interfaces returned by executeQuery() and I am not getting code completion for fetchAllKeyValue(). Is the method missing somewhere? I am not seeing it in Doctrine\DBAL\Driver\PDOStatement nor in Doctrine\DBAL\Driver\Result where I find the other mapping fetches like fetchAllAssociative().

(I'm still on 2.12.x, but not seeing it in the 2.13 branch, either)

Edit: Sorry, never mind, didn't realize they are all on the connection now directly!

[github-actions]

This thread has been automatically locked since there has not been any recent activity after it was closed. Please open a new issue for related bugs.