Implement SQL Union Query using Query Builder in Doctrine Symfony2
Solution 1
I don't know how stupid I am when joining 2 queries without checking the conditions. This is the correct query :
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
I tried implement it through query builder :
$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();
How surprisingly it returns incorrect result ! So i tried to see what query was generated using :
var_dump($queryBuilder->getQuery());
And I really don't know why doctrine generates extra parenthesis as I get this result (carefully have a look at WHERE clause) :
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
So this must be the correct one if I add my own 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')
Feel a little stupid.
Solution 2
The best way to write such query without making to much hack on Doctrine2 itself would be to use NativeQuery
.
The native/sql QueryBuilder
uses the exactly same API as the ORM. And you are able to map result to an existing entity.
Comments
-
khiemnn about 2 years
I'm currently using Symfony2 framework. I don't know how to join 2 of my query by Query Builder in Symfony2, I just can join them using SQL UNION query. Below is the query that returns correct results.
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
I was trying to join this code in just 1 query (without UNION) to get the correct result but I failed.
So how can I implement this query using query builder?
Please advise, thanks.
-
Gigala almost 9 yearsdude you should use parameters when working with variables in query builder. dont just concatenate the string with your variable