Select One column Doctrine DQL

Question

I need a simple column for a table.

By example a table "project", with column id, name and year.

If I do:

$q = Doctrine_Query::create()
        ->select('a.pro_id')
        ->from('fndr_proyecto a')
        ->where('a.pro_id =?',1);
    $pro = $q->execute();
    json_encode($pro->toArray());

The answer is all column like

{"id":1,"name":"Project name","year":2013}

but I need only one column. I expect:

{"id":1}

It is with DQL because with native SQL work fine.

The ORM is build automaticaly with a Visual Paradigm.

score 41 · Accepted Answer · edited Feb 11 '20 at 12:24

This is because Doctrine hydrate the response with all the object information, so all columns.

You need to use a different hydration method, there are many one, but let's focus on 5 of them:

HYDRATE_RECORD, the default one
HYDRATE_ARRAY
HYDRATE_NONE
HYDRATE_SCALAR
HYDRATE_ARRAY_SHALLOW

You need the HYDRATE_ARRAY_SHALLOW hydration method. Here's why.

HYDRATE_RECORD

$q = Doctrine_Query::create()
    ->select('a.pro_id')
    ->from('fndr_proyecto a')
    ->where('a.pro_id = ?',1);
$pro = $q->execute(array(), Doctrine_Core::HYDRATE_RECORD);
var_dump(json_encode($pro->toArray()));

This will hydrate the result using object, and also hydrate relations (if you use a leftJoin inside your query). Since it returns object, we need to call toArray() to be able to send a propre json:

[{"id":1,"name":"Project name","year":2013}]"

HYDRATE_ARRAY

$q = Doctrine_Query::create()
    ->select('a.pro_id')
    ->from('fndr_proyecto a')
    ->where('a.pro_id = ?',1);
$pro = $q->execute(array(), Doctrine_Core::HYDRATE_ARRAY);
var_dump(json_encode($pro));

This will hydrate result as an array an automatically add the primary key:

[{"id":"1","pro_id":"1"}]"

HYDRATE_NONE

$q = Doctrine_Query::create()
    ->select('a.pro_id')
    ->from('fndr_proyecto a')
    ->where('a.pro_id = ?',1);
$pro = $q->execute(array(), Doctrine_Core::HYDRATE_NONE);
var_dump(json_encode($pro));

This won't hydrate result, and return just values:

[["1"]]"

HYDRATE_SCALAR

$q = Doctrine_Query::create()
    ->select('a.pro_id')
    ->from('fndr_proyecto a')
    ->where('a.pro_id = ?',1);
$pro = $q->execute(array(), Doctrine_Core::HYDRATE_SCALAR);
var_dump(json_encode($pro));

This will hydrate result from the select but with key index as the column name with the table alias:

[{"a_pro_id":"1"}]"

HYDRATE_ARRAY_SHALLOW

$q = Doctrine_Query::create()
    ->select('a.pro_id')
    ->from('fndr_proyecto a')
    ->where('a.pro_id = ?',1);
$pro = $q->execute(array(), Doctrine_Core::HYDRATE_ARRAY_SHALLOW);
var_dump(json_encode($pro));

This will hydrate result from the select but with key index as the column name without the table alias:

"[{"pro_id":"1"}]"

Of course! Now find the chapter in the documentation "Data Hydrators". I did not know of that it was about the subject. Thank you very much! ;) — h3g0r_, Jan 22 '13 at 13:31
@h3g0r_ try them, array hydration is often faster and lighter than record hydration ! — j0k, Jan 22 '13 at 13:35
Very good explanation, I've always been puzzled about how works the hydrators, but now all is clear in my mind. Thanks ! — TwystO, Mar 03 '15 at 09:45

score 3 · Answer 2 · answered Jul 27 '22 at 15:03

As of Doctrine 2.10, you can use Scalar Column Hydration:

$query = $em->createQuery('SELECT a.id FROM CmsUser u');
$ids = $query->getResult(Query::HYDRATE_SCALAR_COLUMN);

or

$ids = $query->getSingleColumnResult();

and this results in a flat array

[412, 959, 1234]

score 2 · Answer 3 · answered Mar 10 '14 at 21:10

I'm not sure what version of Doctrine j0k was using. It provided some answers, but I did have trouble finding Doctrine_Query and Doctrine_Core classes. I am using Doctrine 2.3.4. The following worked for me.

public static function getAllEventIDs($em) {
    return parent::getAllFromColumn('\path\to\Entity\entityName', 'id', $em);
}

public static function getAllFromColumn($tableName, $columnName, $em) {
    $q = $em->createQueryBuilder('t')
    ->select("t.$columnName")
    ->from($tableName, 't');

    $q = $q->getQuery();

    $result = $q->getResult(\Doctrine\ORM\Query::HYDRATE_SCALAR);

    return $result;
}

This did however return a array of arrays. ie, the id of the first event was is

$result[0]['id'];

Select One column Doctrine DQL

3 Answers3