How can I select specific Columns with createQueryBuilder in Doctrine ORM?

Question

I'm using Doctrine createQueryBuilder() to construct queries in Symfony2. But, I don't want to take all columns in this entity. How can I select only the ID and Name?

$query = $this->getEntityManager()->createQueryBuilder();
        $query
            ->select('d')
            ->from('AcmeBundle:Demo', 'd')
            ->leftjoin('d.otherEntity', 'o');

        $query->setMaxResults(10);
        $results = $query->getQuery()->getResult();

Thank you so much,

Parfitt

#9465853 – Deci

Actually, the ORM is Doctrine2. Symfony is a web framework. – Twelve

Answer 1

Try following,

$fields = array('d.id', 'd.name', 'o.id');
//$fields = 'partial d.{id, name}, partial o.{id}';  //if you want to get entity object

$query = $this->getEntityManager()->createQueryBuilder();
$query
    ->select($fields)
    ->from('AcmeBundle:Demo', 'd')
    ->leftjoin('d.otherEntity', 'o');

$query->setMaxResults(10);
$results = $query->getQuery()->getResult();

Samsun

will it hydrate query result ? – Baucis

is there a way to set the fields from setParameter ? – Mistakable

partial alias.{comma, fields} is not working to me on symfony 2.4. Should it work or is it only for newer versions? – Sherrillsherrington

@Baucis see "Hydration Modes" here doctrine-project.org/projects/doctrine-orm/en/2.9/reference/… – Afrit

@Sherrillsherrington Doctrine version 2.10 or greater. See doctrine-project.org/projects/doctrine-orm/en/2.10/reference/… – Afrit

I think it's also worth mentioning that it's required to include "identifier/primary key column name" in the columns list for all the entities (e.g., id for both d and o in partial d.{id, name}, partial o.{id}) while using the partial keyword to get the join's primary entity object (e.g.,d) instance mapped with the partial result set. – Glaser

this solution does not work with distinct: $qb->distinct('d.id, d.name'). To make it work need to change it to this $qb->select('DISTINCT d.id, d.name') – Postfree