How to fetch values likes PDO::FETCH_COLUMN in Doctrine?

Question

I have the following code:

$repository = $this->entitymanager->getRepository('Intlist\Entity\Location');
$query = $repository->createQueryBuilder('l')
    ->select('l.country')
    ->groupBy('l.country')
    ->where('l.country != :empty_country')
    ->setParameter('empty_country', "")
    ->getQuery();

And I would like to obtain something like [ 'EN', 'FR', 'US' ], like PDO::FETCH_COLUMN would return. I tried getScalarResult() but it returns the same result as getArrayResult():

array (size=2)
  0 => 
    array (size=1)
      'country' => string 'DE' (length=2)
  1 => 
    array (size=1)
      'country' => string 'MM' (length=2)

I tried to use execute() as I have seen on some examples but it returns the same result as getArrayResult() and not a PDO statement.

Any idea?

Veve · Answer 1 · 2015-01-07T22:14:07.263

You can extract values from your query result to get the array you want :

$repository = $this->entitymanager->getRepository('Intlist\Entity\Location');
$query = $repository->createQueryBuilder('l')
    ->select('l.country')
    ->groupBy('l.country')
    ->where('l.country != :empty_country')
    ->setParameter('empty_country', "")
    ->getScalarResult();

$country_codes = array();
foreach($query as $result){
    $country_codes[] = $result['country'];
}

If you want native results, you can make a custom hydration mode, as explained in Doctrine doc.

Create a class extending AbstractHydrator:

namespace MyProject\Hydrators;

use Doctrine\ORM\Internal\Hydration\AbstractHydrator;

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

Add the class to the ORM configuration:

$em->getConfiguration()->addCustomHydrationMode('ColumnHydrator', 'MyProject\Hydrators\ColumnHydrator');

Then use it:

$repository = $this->entitymanager->getRepository('Intlist\Entity\Location');
$query = $repository->createQueryBuilder('l')
    ->select('l.country')
    ->groupBy('l.country')
    ->where('l.country != :empty_country')
    ->setParameter('empty_country', "")
    ->getQuery();
$results = $query->getResult('ColumnHydrator');

Sorry, I should have said that I'm looking for a native function, like `PDO::fetchAll()` with `\PDO::FETCH_COLUMN`, I don't want to parse the result. — olvlvl, Jan 07 '15 at 15:47
Thanks @Veve, looks good. Too bad this hydrator is not packed with Doctrine. — olvlvl, Jan 09 '15 at 11:26

score 3 · Accepted Answer · answered Nov 23 '21 at 11:49

Hello this possibility was added in Doctrine ORM 2.10+ you can now do (following your example) :

$repository = $this->entitymanager->getRepository('Intlist\Entity\Location');
$query = $repository->createQueryBuilder('l')
    ->select('l.country')
    ->groupBy('l.country')
    ->where('l.country != :empty_country')
    ->setParameter('empty_country', "")
    ->getQuery()
    ->getSingleColumnResult();

References :

Documentation : https://www.doctrine-project.org/projects/doctrine-orm/en/2.10/reference/dql-doctrine-query-language.html#scalar-column-hydration
Merge request : https://github.com/doctrine/orm/pull/8919/files

How to fetch values likes PDO::FETCH_COLUMN in Doctrine?

2 Answers2

Linked