symfony框架_Symfony2中SQL UNION查询的例子

更新时间:2020-09-19    来源:Symfony    手机版     字体:

【www.bbyears.com--Symfony】

Symfony2的,我不知道如何加入2我的查询通过查询生成器在Symfony2中,我只可以加入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查询(不带),以得到正确的结果,但我失败了。 所以,我怎么能这个查询生成器? 请指教,谢谢。
本文地址 :CodeGo.net/7568427/
-------------------------------------------------------------------------------------------------------------------------
1.最好的办法未做得多hacker对Doctrine2本身会写这样的查询NativeQuery。 本机/ SQLQueryBuilder使用的ORM的确切的API。而你能结果映射到现有实体。
2. 我不知道我有多么愚蠢是不检查的条件加入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());
我真的不知道为什么doctrine产生额外的括号,因为我得到这样的结果(仔细看一下地点:
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
所以这一定是正确的,如果我想补充我自己的parenthsis:
$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")

本文来源:http://www.bbyears.com/jiaocheng/99585.html