Set LIMIT with doctrine 2?

57
votes

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 ?

5
doctrine-orm
MySQL doesn't yet support 'LIMIT & IN/ALL/ANY/SOME subquery' - Serhii Smirnov
prove link for 'doctrine does NOT support limit in subqyery' is : github.com/doctrine/orm/issues/3979 so use native direct sql query ! - Vladimir Ch

5 Answers

54
votes
$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();
22
votes

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);
8
votes

Your setMaxResults($limit) needs to be set on the object.

e.g. 

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

$query = $this->getDoctrine()->getEntityManager()->createQuery(
           '// your request')

->setMaxResults($limit);

 $results = $query->getResult();

// Done
0
votes
 $qb = $this->getDoctrine()->getManager()->createQueryBuilder();  
 $qb->select('p') ->from('Pandora\UserBundle\Entity\PhoneNumber', 'p');
$qb->where('p.number = :number');
$qb->OrWhere('p.validatedNumber=:number');
$qb->setMaxResults(1);
$qb->setParameter('number',$postParams['From'] );
$result = $qb->getQuery()->getResult();
 $data=$result[0];