Doctrine 2：如何使用子查询列（在SELECT子句中） [英] Doctrine 2: how do you use a subquery column (in the SELECT clause)

问题描述

我试图用SELECT子句中的一个子查询来做一个简单的选择查询，而且根本找不到一个方法。我已经尝试使用DQL和QueryBuilder，都不工作。代码如下，请不要说我只能使用加入，这是一个简单的例子，只是为了说明问题，我有合法的用例子查询。

  //使用QueryBuilder 
 $ query = $ qb-> select（array（'a'，
'（SELECT at.addresstypeName 
 FROM e： 
 WHERE at.addresstypeId = a.addresstypeId 
）AS addresstypeName'））
  - > from（'e：Address'，'a'）
  - > where（'a.addressId =：addressId'）
  - > setParameter（'addressId'，1）; 
 
 //使用DQL 
 $ dql =SELECT a，
（SELECT at.addresstypeName 
 FROM e：Addresstype at 
 WHERE at.addresstypeId = a.addresstypeId 
）AS addresstypeName 
 FROM e：地址a 
 WHERE a.addressId =：addressId; 
 $ query = $ em-> createQuery（$ dql） - > setParameter（'：addressId'，1）; 
  

地址表中定义了以下关系：

$ b $
* @ ORM\JoinColumns（{
*）$ {
* @ ORM\ManyToOne（targetEntity =Addresstype @ ORM\JoinColumn（name =addresstype_id，referencedColumnName =addresstype_id）
*}）
* /
protected $ addresstype;

在本地SQL中，查询如下所示：

  SELECT 
 a。*，
（
 SELECT at.addresstype_name 
 FROM addresstype at 
 WHERE at。 addresstype_id = a.addresstype_id 
）AS addresstype_name 
 FROM地址a 
 WHERE a.address_id = 1 
  

任何想法？

解决方案

  $ query = $ qb - > select（'a'）
  - > addSelect（'（SELECT at.addresstypeName 
 FROM e：Addresstype at 
 WHERE at.addresstypeId = a.addresstypeId）AS addresstypeName'
）
  - > from（'e：Address'，'a'）
  - >其中（'a.addressId =：addressId'）
  - > setParameter 'addressId'，1）; 
  

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?

解决方案

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

这篇关于Doctrine 2：如何使用子查询列（在SELECT子句中）的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！