Fetch multi-row, single-column array using Doctrine

Question

I have a Doctrine fetch statement like this

$query = "SELECT id FROM table LIMIT 2";
$result = $db->fetchAll($query);

which returns the array like this:

Array
(
[0] => Array
    (
        [id] => 1
    )

[1] => Array
    (
        [id] => 2
    )
)

Since the only column I fetch is ID, I don't need the array scope do be that deep. Is there a convenient way of making Doctrine return the results in a "flat" array, similar to what what PDO does:

$result = $db->query($query)->fetchAll(PDO::FETCH_COLUMN);

will return

Array
(
    [0] => 1
    [1] => 2
)

Currently I am flattening it using

$result = call_user_func_array('array_merge', array_map("array_values", $result));

Whilst not the most elegant way of doing things, the call_user_func_array DOES work as expected and flattens a single getArrayResult correctly, if only one column is present — Justin Finkelstein, Mar 16 '15 at 14:51

score 14 · Answer 1 · answered Jul 28 '15 at 14:10

14

You can simply use the PDO functionality (at least if you have MySQL).

$ids = $db
    ->executeQuery($query)
    ->fetchAll(\PDO::FETCH_COLUMN)
;

answered Jul 28 '15 at 14:10

user3802042

209
3
4

score 6 · Answer 2 · edited Nov 24 '15 at 14:56

6

To resolve this problem you have to make your custom doctrine hydrator.

First: Make your own hydrator

<?php
namespace MyProject\Hydrators;

use Doctrine\ORM\Internal\Hydration\AbstractHydrator;

class CustomHydrator extends AbstractHydrator
{
    protected function _hydrateAll()
    {
        return $this->_stmt->fetchAll(PDO::FETCH_COLUMN);
    }
}

Add your hydrator to Doctrine configuration file :

<?php
$em->getConfiguration()->addCustomHydrationMode('CustomHydrator','MyProject\Hydrators\CustomHydrator');

Finaly you can use your cutom hidrator :

<?php
$query = $em->createQuery('SELECT u FROM CmsUser u');
$results = $query->getResult('CustomHydrator');

edited Nov 24 '15 at 14:56

fabpico

2,628
4
26
43

answered Dec 05 '13 at 00:00

Yassine EL MALKI

152
5

I need to do the same thing and I was hoping someone had already published their custom hydrator, but I can't seem to find any. Did either of you complete this task? – Jessica Apr 15 '14 at 18:58
Found it: https://www.techpunch.co.uk/development/create-custom-doctrine2-hydrator-symfony2 – Jessica Apr 15 '14 at 19:57

score 1 · Answer 3 · answered Dec 03 '20 at 04:08

I found a method called fetchFirstColumn which appears to do this. This was probably added in a later version of doctrine. I am currently on Doctrine ORM 2.7.4.

I am using it like this in Symfony:

$statement = $connection->prepare('SELECT foo FROM bar WHERE baz = :baz');
$statement->execute([':baz' => 1]);
$result = $statement->fetchFirstColumn();

The value of $result will be a numerically indexed array starting at 0 like this:

[
    0 => 'foo1', 
    1 => 'foo2'
];

score -1 · Answer 4 · edited Apr 11 '19 at 13:41

-1

Fetch the data using fetchAssoc:

$result = $db->query($query)->fetchAssoc(PDO::FETCH_COLUMN);

You will get an Array like this:

Array ( 
    [id] => 11
)

edited Apr 11 '19 at 13:41

barbsan

3,418
11
21
28

answered Apr 11 '19 at 13:21

henrik23

9
6

Fetch multi-row, single-column array using Doctrine

4 Answers4