我目前正在使用Symfony2框架。我不知道如何在Symfony2中通过Query Builder连接我的查询的2个,我只能使用SQL UNION查询连接它们。下面是返回正确结果的查询。
SELECT * FROM (SELECT m.id, m.subject, m.date
FROM message m JOIN message_incoming mi ON m.id = mi.id
WHERE m.recipient_id = 1
AND mi.trash = 1
AND mi.deleted = 0) AS y
UNION
SELECT * FROM (SELECT m.id, m.subject, m.date
FROM message m JOIN message_outgoing mo ON m.id = mo.id
WHERE m.originator_id = 1
AND mo.trash = 1
AND mo.sent = 1
AND mo.deleted = 0) AS z
ORDER BY date DESC
我试图在 1 个查询(没有 UNION)中加入此代码以获得正确的结果,但我失败了。
那么如何使用查询生成器实现此查询呢?
请指教,谢谢。
编写此类查询而不对 Doctrine2 本身进行太多破解的最佳方法是使用 NativeQuery
。
本机/sql QueryBuilder
使用与 ORM 完全相同的 API。并且您可以将结果映射到现有实体。
我不知道
在没有检查条件的情况下加入 2 个查询时我有多愚蠢。这是正确的查询:
SELECT m.id, m.subject, m.date
FROM message m
JOIN message_incoming mi ON m.id = mi.id
JOIN message_outgoing mo ON m.id = mo.id
WHERE m.recipient_id = 1
AND mi .trash = 1
AND mi .deleted = 0
OR m.originator_id = 1
AND mo .trash = 1
AND mo .deleted =0
AND mo .sent = 1
ORDER by date DESC
我尝试通过查询构建器实现它:
$queryBuilder = $this->entityManager->getRepository('EzxWebmailBundle:Message')
->createQueryBuilder('m')
->select('m.id','m.subject','m.date')
->join('m.message_outgoing','mo','ON','m.id = mo.id')
->join('m.message_incoming','mi','ON','m.id = mi.id')
->where('m.recipient_id = '.$userId)
->andWhere('mi.trash = 1')
->andWhere('mi.deleted = 0')
->orWhere('m.originator_id = '.$userId)
->andWhere('mo.trash = 1')
->andWhere('mo.deleted = 0')
->andWhere('mo.sent = 1')
->orderBy('m.date','DESC');
$result = $queryBuilder->getQuery()->getResult();
令人惊讶的是,它返回了不正确的结果!所以我试图看看使用什么查询生成:
var_dump($queryBuilder->getQuery());
而且我真的不知道为什么教义在我得到这个结果时会产生额外的括号(仔细看看 WHERE 子句):
SELECT m.id, m.subject, m.date FROM message m
INNER JOIN message_outgoing mo ON m.id = mo.id
INNER JOIN message_incoming mi ON m.id = mi.id
WHERE ((m.recipient_id = 1 AND mi.trash = 1 AND mi.deleted = 0) OR m.originator_id = 1) AND mo.trash = 1 AND mo.deleted = 0 AND mo.sent = 1
ORDER BY m.date DESC
所以如果我添加自己的括号,这一定是正确的:
$queryBuilder = $this->entityManager->getRepository('EzxWebmailBundle:Message')
->createQueryBuilder('m')
->select('m.id','m.subject','m.date')
->join('m.message_outgoing','mo','ON','m.id = mo.id')
->join('m.message_incoming','mi','ON','m.id = mi.id')
->where('(m.recipient_id = '.$userId)
->andWhere('mi.trash = 1')
->andWhere('mi.deleted = 0)')
->orWhere('(m.originator_id = '.$userId)
->andWhere('mo.trash = 1')
->andWhere('mo.deleted = 0')
->andWhere('mo.sent = 1)')
->orderBy('m.date','DESC')
感觉有点傻。