Change the getResult array key for the primary key value

Question

Is it possible to change the array key values for the getResult() in Doctrine2?

Example:

$qb->select('t.id, t.name')->from('Table', 't');

When I print this, I get, which is not what I wanted:

print_r($qb->getQuery()->getResult());

//Print result: Array ( [0] => Array ( [id] => 20 [name] => Name1 ) [1] => Array ( [id] => 21 [percentagem] => Name2 ) )

What I want is:

Array ( [20] => Array ( [id] => 20 [name] => Name1 ) [21] => Array ( [id] => 21 [percentagem] => Name2 ) )

Suggestions, hints would be appreciated.

Answer 1

I'm actually very happy about how cool this thing is:

$query = $this->getEntityManager()->createQuery('
            SELECT user FROM UserBundle:User user
            INDEX BY user.id
            WHERE user.id = 1
            '
        );

The INDEX BY construct is nothing that directly translates into SQL but that affects object and array hydration. After each FROM and JOIN clause you specify by which field this class should be indexed in the result. By default a result is incremented by numerical keys starting with 0. However with INDEX BY you can specify any other column to be the key of your result, it really only makes sense with primary or unique fields though.

Source: Doctrine ORM 2 Documentation Using INDEX BY

• Please use INDEX BY prior to WHERE

Answer 2

However, for the sake of completeness, you can do the same with the query builder as shown below:

$queryBuilder = $this->getEntityManager()->createQueryBuilder();

$queryBuilder
    ->select('user')
    ->from('UserBundle:User', 'user', 'user.id')
    ->where('user.id = :userId')
    ->setParameter('userId', $userId)
;

var_dump(
    $queryBuilder->getQuery()->getArrayResult()
);

As you can see the index by option is available as the third parameter of the query builder from method:

/**
 * Creates and adds a query root corresponding to the entity identified by the given alias,
 * forming a cartesian product with any existing query roots.
 *
 * <code>
 *     $qb = $em->createQueryBuilder()
 *         ->select('u')
 *         ->from('User', 'u')
 * </code>
 *
 * @param string $from    The class name.
 * @param string $alias   The alias of the class.
 * @param string $indexBy The index for the from.
 *
 * @return QueryBuilder This QueryBuilder instance.
 */
public function from($from, $alias, $indexBy = null)
{
    return $this->add('from', new Expr\From($from, $alias, $indexBy), true);
}