原则 2:如何使用子查询列(在 SELECT 子句中) [英] Doctrine 2: how do you use a subquery column (in the SELECT clause)
本文介绍了原则 2:如何使用子查询列(在 SELECT 子句中)的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
我正在尝试使用 SELECT 子句中的子查询执行简单的选择查询,但根本没有找到执行此操作的方法.我已经尝试过 DQL 和 QueryBuilder,但都不起作用.代码如下,请不要说我只能使用连接,这是一个简单的例子,只是为了说明问题,我有子查询的合法用例.
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:
/**
* @ORMManyToOne(targetEntity="Addresstype")
* @ORMJoinColumns({
* @ORMJoinColumn(name="addresstype_id", referencedColumnName="addresstype_id")
* })
*/
protected $addresstype;
在原生 SQL 中,查询如下所示:
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
有什么想法吗?
推荐答案
$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);
这篇关于原则 2:如何使用子查询列(在 SELECT 子句中)的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文