How to get a one-dimensional scalar array as a result of the dql query doctrine?

Question

I want to get an array of values ​​from the id column of the auction table. If it were raw SQL, I would write:

SELECT id FROM auction 

But when I do this in the Doctrine and do:

 $em->createQuery("SELECT a.id FROM Auction a")->getScalarResult(); 

I get an array like this:

 array( array('id' => 1), array('id' => 2), ) 

Instead, I would like to get an array like this:

 array( 1, 2 ) 

How can I do this with Doctrine?

Answer 1

PHP <5.5

You can use array_map , and since you only have an element per array, you can elegantly use 'current' as a callback instead of writing a close .

 $result = $em->createQuery("SELECT a.id FROM Auction a")->getScalarResult(); $ids = array_map('current', $result); 

See Peter Sobotka below for more information on memory usage.

PHP> = 5.5

As jcbwlkr answered below , it is recommended to use its array_column .

Answer 2

Starting with PHP 5.5 you can use array_column to solve this problem

 $result = $em->createQuery("SELECT a.id FROM Auction a")->getScalarResult(); $ids = array_column($result, "id"); 

Answer 3

The best solution is to use PDO:FETCH_COLUMN . For this you need a special hydrator:

 //MyProject/Hydrators/ColumnHydrator.php namespace DoctrineExtensions\Hydrators\Mysql; use Doctrine\ORM\Internal\Hydration\AbstractHydrator, PDO; class ColumnHydrator extends AbstractHydrator { protected function hydrateAllData() { return $this->_stmt->fetchAll(PDO::FETCH_COLUMN); } } 

Add it to Doctrine:

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

And you can use it as follows:

 $em->createQuery("SELECT a.id FROM Auction a")->getResult("COLUMN_HYDRATOR"); 

Additional information: http://docs.doctrine-project.org/projects/doctrine-orm/en/latest/reference/dql-doctrine-query-language.html#custom-hydration-modes

Answer 4

Askarius' answer is elegant, but beware of memory usage! array_map() creates a copy of the passed array and effectively doubles the memory usage. If you work with hundreds of thousands of array elements, this can be a problem. Since PHP 5.4, call time by reference is removed, so you cannot execute

 // note the ampersand $ids = array_map('current', &$result); 

In this case, you can go with the obvious

 $ids = array(); foreach($result as $item) { $ids[] = $item['id']; } 

Answer 5

I think this is not possible in the Doctrine. Just convert the array of results to the data structure you want using PHP:

 $transform = function($item) { return $item['id']; }; $result = array_map($transform, $em->createQuery("SELECT a.id FROM Auction a")->getScalarResult());