CODEWITHSUNDEEP
phpmysqldoctrine
Ahmad Sajid
Ahmad Sajid

Reputation: 191

convert a sql to Doctrine query with union

Hi i am developing a symfony2 app and having issue with UNION as symfony2 doesnt support it and i can not use pdo statements to keep the entity in query. here is my working sql:

select ca.name, count(ca.name) as total
from content as co
inner join category as ca
on co.category_id=ca.id
where ca.id in (11,7,16,1,12,13)
group by ca.name
union
select 'Rest', count(ca.name) as total
from content as co
inner join category as ca
on co.category_id=ca.id
where ca.id not in (11,7,16,1,12,13);

the result is

"Audio Song",208
"Drama Serial",137
"Movie,824
"Movie Video Song",773
"Music Video",1225
"Single Drama",571
"Rest",900

now with symfony i am having problem with getting this Rest: here is my effort:

$query = $this->getEntityManager()
        ->createQueryBuilder()
        ->from('BbdBongoAppBundle:Category','cat')
        ->select('cat.name AS name')
        ->addSelect('(SELECT COUNT(c.id) FROM MyAppBundle:Content c WHERE c.category = cat.id) AS total')            
        ->where('cat.id IN (:id)')      
        ->setParameter('id', array(11,7,16,1,12,13))            
        ->getQuery();

how can i get the Rest from my query?

Upvotes: 0

Views: 1496

Answers (1)

Reformat Code
Reformat Code

Reputation: 307

You can use plain sql statements with doctrine as this example: https://codedump.io/share/sEIEbj8xwEq1/1

So in your case its like:

$query = "select ca.name, count(ca.name) as total
from content as co
inner join category as ca
on co.category_id=ca.id
where ca.id in (11,7,16,1,12,13)
group by ca.name
union
select 'Rest', count(ca.name) as total
from content as co
inner join category as ca
on co.category_id=ca.id
where ca.id not in (11,7,16,1,12,13);";
$stmt = $this->entityManager->getConnection()->prepare($query);
$stmt->execute();
$members = $stmt->fetchAll();

Upvotes: 1

Related Questions