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

doctrine-orm

Solution

$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);

Problem

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?

Original source