CODEWITHSUNDEEP
phpdoctrine-orm
Dawid Ohia
Dawid Ohia

Reputation: 16445

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

I want to get an array of values from the id column of the Auction table. If this was a raw SQL I would write:

SELECT id FROM auction

But when I do this in Doctrine and execute:

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

I get an array like this:

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

Instead, i'd like to get an array like this:

array(
    1,
    2
)

How can I do that using Doctrine?

Upvotes: 140

Views: 100202

Answers (6)

Thomas Landauer
Thomas Landauer

Reputation: 8355

Since doctrine/orm 2.10.0 there is a built-in solution for this:

use Doctrine\ORM\AbstractQuery;

$query = $em->createQuery("SELECT a.id FROM Auction a");
return $query->getSingleColumnResult();
// Same as:
return $query->getResult(AbstractQuery::HYDRATE_SCALAR_COLUMN);

Docs: https://www.doctrine-project.org/projects/doctrine-orm/en/2.10/reference/dql-doctrine-query-language.html#scalar-column-hydration

Upvotes: 21

Lionel Gaillard
Lionel Gaillard

Reputation: 3023

PHP < 5.5

You can use array_map, and since you only have on item per array, you can elegantly use 'current' as callback, instead of writing a closure.

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

See Petr Sobotka's answer below for additional info regarding memory usage.

PHP >= 5.5

As jcbwlkr's answered below, the recommended way it to use array_column.

Upvotes: 210

Ioan Badila
Ioan Badila

Reputation: 1051

A better solution is to use PDO:FETCH_COLUMN . To do so you need a custom 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 like this:

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

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

Upvotes: 105

jcbwlkr
jcbwlkr

Reputation: 7999

As of PHP 5.5 you can use array_column to solve this

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

Upvotes: 180

Petr Sobotka
Petr Sobotka

Reputation: 733

Ascarius' answer is elegant, but beware of memory usage! array_map() creates a copy of passed array and effectively doubles memory usage. If you work with hundreds of thousands of array items this can become an issue. Since PHP 5.4 call-time pass by reference has been removed so you cannot do

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

In that case you can go with obvious

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

Upvotes: 18

Minras
Minras

Reputation: 4346

I think it's impossible in Doctrine. Just transform result array into 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());

Upvotes: 4

Related Questions