Doctrine2 & Symfony2 multiple count in one query

Question

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 :)

julesbou · Accepted Answer · 2011-07-31 15:14:36Z

2

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.

answered Jul 31, 2011 at 15:14

julesbou

5,7804 gold badges34 silver badges36 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Doctrine2 & Symfony2 multiple count in one query

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related