SQL query with UNION in Doctrine Symfony
UNION is not supported within DQL, but you can issue your query using RAW SQL ->
$q = Doctrine_Manager::getInstance()->getCurrentConnection();
$result = $q->execute(" -- RAW SQL HERE -- ");
Union with Doctrine
Unfortunately UNION
is not possible within Doctrine
. You have to fetch two sets from database and do "union" manually on php side or use native sql.
Check this issue for more information
How to make a UNION with Doctrine?
I decided to breaking in two searches and giving a marge in results
public function findByAdOwner($ownerId)
{
$qb = $this->getEntityManager()->createQueryBuilder('n');
return $qb->select('n')
->from('DelivveWebBundle:UserAd', 'n')
->join('n.ad', 'ad')
->where('ad.owner = :ownerId')
->setParameter('ownerId', $ownerId)
->setMaxResults(20)
->getQuery()
->getResult();
}
public function findByUserNotify($userId)
{
$qb = $this->getEntityManager()->createQueryBuilder('n');
return $qb->select('n')
->from('DelivveWebBundle:UserAd', 'n')
->join('n.ad', 'ad')
->where('n.user = :userId')
->andWhere('ad.status = :status')
->setParameter('userId', $userId)
->setParameter('status', Constant::AD_IN_PROGRESS)
->setMaxResults(20)
->getQuery()
->getResult();
}
public function findNotifcations($userId){
$notification = $this->findByAdOwner($userId);
$append = $this->findByUserNotify($userId);
return array_merge($notification, $append);
}
To become more readable'll just put after something that distinguishes the two types of notice to do the treatment on the page.
I discovered that there is a way to add commands to the doctrine that does not exist, but appears to be quite complex if anyone knows do this, put the answer please.
Implement SQL Union Query using Query Builder in Doctrine Symfony2
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.
How to write UNION in Doctrine 2.0
Well, I found maybe the best solution:
/**
* @Entity
* @InheritanceType("JOINED")
* @DiscriminatorColumn(name="discr", type="string")
* @DiscriminatorMap({"group" = "NotificationGroup", "event" = "NotificationEvent"})
*/
class Notification {
// ...
}
And then two classes (NotificationGroup and NotificationEvent) extending Notification:
/**
* @Entity
*/
class NotificationGroup extends Notification {
//...
}
/**
* @Entity
*/
class NotificationEvent extends Notification {
//...
}
How to write union query in doctrine?
Well i have found a solution
We can use this query with RSM as following
"Usman is basically table name and class"
$rsm = new ResultSetMapping();
$rsm->addEntityResult('Usmans', 'u');
$rsm->addFieldResult('u', 'orderid', 'orderid');
$rsm->addFieldResult('u', 'tutorialId', 'tutorialid');
$rsm->addFieldResult('u', 'points', 'points');
$query = $this->Doctrine->createNativeQuery('SELECT * FROM usman', $rsm);
$tutorial_tests = $query->getResult();
AND we can use without ORM as
$testQuery = "
select * from (
select orderid,
tutorialId,
points,
allow_multiple,
question,
answer1,
image1,
correct1,
answer2,
image2,
correct2,
answer3,
image3,
correct3,
answer4,
image4,
correct4,
answer5,
image5,
correct5,
'1' as istest,
'' as content,
'' as media,
'' as media_type_id
from tutorial_test
union
select orderid,
tutorialId,
'0' as istest,
content,
media,
media_type_id,
'' as points,
'' as allow_multiple,
'' as question,
'' as answer1,
'' as image1,
'' as correct1,
'' as answer2,
'' as image2,
'' as correct2,
'' as answer3,
'' as image3,
'' as correct3,
'' as answer4,
'' as image4,
'' as correct4,
'' as answer5,
'' as image5,
'' as correct5
from tutorial_elements
) a
where a. tutorialId = $tutorial_id
order by orderid asc
";
$resultSets = $this->Doctrine->getConnection()->fetchAll($testQuery);
how to use union in doctrine query symfony 1.4?
I believe that UNION is not supported using DQL (doctrine query language). But it looks like someone was able to get around the issue, using a nasty heredoc.
Or, you could just add a custom method in an object peer class and write the query in native sql.
-- Edit --
Use the following as an example (untested):
// Assuming you have the default doctrine setup, where non-object classes are
// appended with Table (your object being Foo)
class FooTable
{
public static function getFooBarUnion()
{
$sql = "select a.x, a.y, a.foo, sum(a.bar) as bar_sum from mytable a groupby a.x union select a.x, a.y, a.bar, sum(a.foo) as foo_sum from mytable a groupby a.y";
$results = Doctrine_Query::create()->query($sql);
return $results;
}
}
Related Topics
Time Zone Conversion in SQL Query
How to Remove Duplicates from Table Using SQL Query
Deleting a Row with a Self-Referencing Foreign Key
SQL - Use a Reference of a Cte to Another Cte
Table Valued Function Where Did My Query Plan Go
How to Create a Postgres Table with Unique Combined Primary Key
Array Combinations Without Repetition
SQL Developer "Disconnected from the Rest of the Join Graph"
Find Start and Stop Date for Contiguous Dates in Multiple Rows
Django.Db.Utils.Integrityerror: Column "Venue_City" Contains Null Values
Ssrs - Keep a Table the Same Width When Hiding Columns Dynamically