CODEWITHSUNDEEP
doctrine-ormsymfony
Bouki
Bouki

Reputation: 1359

Doctrine2 & Symfony2 multiple count in one query

I would like to make a request like this with symfony2 and doctrine :

SELECT 
(SELECT count(id) FROM table1) AS c1,
(SELECT count(id) FROM table2) AS c2,
(SELECT count(id) FROM table3) AS c3,
(SELECT count(id) FROM table4) AS c4

(note : this request is working in mysql) However I don't know how to do it with doctrine, I tried something like this :

$em = $this->getDoctrine()->getEntityManager();
$result = $em->createQuery('SELECT
    (SELECT count(id) FROM MyBundle:Table1) AS c1,
    (SELECT count(id) FROM MyBundle:Table2) AS c2,
    (SELECT count(id) FROM MyBundle:Table3) AS c3,
    (SELECT count(id) FROM MyBundle:Table4) AS c4'
)->getResult();

However it raises an exception :

("[Semantical Error] line 0, col 144 near ') AS c2,
': Error: ')' is already defined.") in

So is it possible to do what I want and if yes how ?

Any help will be appreciated :)

Upvotes: 2

Views: 1693

Answers (1)

julesbou
julesbou

Reputation: 5780

The error is beacuse with $em->createQuery($dql) you can only use DQL (Doctrine Query Language).

If you want to use SQL in a query, you have to do it with the Doctrine\Dbal\Connection, use $em->getConnection()->fetchAssoc($sql).

Hope this help you.

Upvotes: 2

Related Questions