Set LIMIT with doctrine 2?

Question

I trying to write a query (with subquery) but i don't know how set a limit in my subquery. My query:

$query_ids = $this->getEntityManager()       ->createQuery(         "SELECT e_.id         FROM MuzichCoreBundle:Element e_         WHERE [...]         GROUP BY e_.id")      ->setMaxResults(5)     ;  $query_select = "SELECT e       FROM MuzichCoreBundle:Element e        WHERE e.id IN (".$query_ids->getDql().")       ORDER BY e.created DESC, e.name DESC"     ;  $query = $this->getEntityManager()       ->createQuery($query_select)       ->setParameters($params)     ; 

But ->setMaxResults(5) doesn't work. No 'LIMIT' in the SQL query. Can we do simple LIMIT with doctrine 2 ?

Answer 1

$query_ids = $this->getEntityManager()       ->createQuery(         "SELECT e_.id         FROM MuzichCoreBundle:Element e_         WHERE [...]         GROUP BY e_.id")      ->setMaxResults(5)      ->setMaxResults($limit)      ; 

HERE in the second query the result of the first query should be passed ..

$query_select = "SELECT e       FROM MuzichCoreBundle:Element e        WHERE e.id IN (".$query_ids->getResult().")       ORDER BY e.created DESC, e.name DESC"     ;   $query = $this->getEntityManager()       ->createQuery($query_select)       ->setParameters($params)       ->setMaxResults($limit);     ;  $resultCollection = $query->getResult(); 

Answer 2

I use Doctrine\ORM\Tools\Pagination\Paginator for this, and it works perfectly (doctrine 2.2).

$dql = "SELECT p, c FROM BlogPost p JOIN p.comments c"; $query = $entityManager->createQuery($dql)                        ->setFirstResult(0)                        ->setMaxResults(10);  $paginator = new Paginator($query, $fetchJoinCollection = true);