doctrine-orm - 教义 2 : how do you use a subquery column (in the SELECT clause)

Question

我正在尝试使用 SELECT 子句中的子查询执行简单的选择查询，但根本没有找到执行此操作的方法。我已经尝试过 DQL 和 QueryBuilder，但都不起作用。代码如下，请不要说我只能使用连接，这是一个简化的示例，只是为了说明问题，我有子查询的合法用例。

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

地址表上定义了以下关系:

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

在 native SQL 中，查询将如下所示:

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

有任何想法吗？

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