Doctrine 2: how do you use a subquery column (in the SELECT clause)

Question

I'm trying to do a simple select query with a subquery in the SELECT clause and have simply not found a way to do it. I've tried with both DQL and with the QueryBuilder, neither work. The code follows, please don't say I could just use a join, this is a simplified example just to illustrate the problem, I have legitimate use cases for subqueries.

// With QueryBuilder $query = $qb->select(array('a',                             '(SELECT at.addresstypeName                                 FROM e:Addresstype at                                WHERE at.addresstypeId = a.addresstypeId                             ) AS addresstypeName'))             ->from('e:Address', 'a')             ->where('a.addressId = :addressId')             ->setParameter('addressId', 1);  // With DQL $dql = "SELECT a,                 (SELECT at.addresstypeName                    FROM e:Addresstype at                   WHERE at.addresstypeId = a.addresstypeId                ) AS addresstypeName            FROM e:Address a           WHERE a.addressId = :addressId"; $query = $em->createQuery($dql)->setParameter(':addressId', 1); 

The following relationship is defined on the Address table:

/**  * @ORM\ManyToOne(targetEntity="Addresstype")  * @ORM\JoinColumns({  *   @ORM\JoinColumn(name="addresstype_id", referencedColumnName="addresstype_id")  * })  */ protected $addresstype; 

In native SQL, the query would look like this:

SELECT     a.*,     (         SELECT at.addresstype_name         FROM addresstype at         WHERE at.addresstype_id = a.addresstype_id     ) AS addresstype_name FROM address a WHERE a.address_id = 1 

Any ideas?

Answer 1

$query = $qb->select('a')     ->addSelect('(SELECT at.addresstypeName             FROM e:Addresstype at             WHERE at.addresstypeId = a.addresstypeId) AS addresstypeName'         )     ->from('e:Address', 'a')     ->where('a.addressId = :addressId')     ->setParameter('addressId', 1);