Geting the number of rows in doctrine query

Question

A have a code like this:

$em = $this->getDoctrine()->getEntityManager();
    $query = $em->createQuery("SELECT p FROM AcmeBlogBundle:Publish p
         ORDER BY p.update_date DESC");

   $query->setFirstResult(($page-1)*$ads_on_page);
   $query->setMaxResults($ads_on_page);      
   $posts = $query->getResult();

Now I am trying to get the total number of rows in the query (not depending on setMeaResult course). I tried to use count() function but it doesn works...

So I did SELECT p, count(p.id) as p_count FROM AcmeBlogBundle:Publish p ORDER BY update_date DESC and it works, but I see that now I have only one row [I think that it because count() return only one row]. So I did two queries, like this:

$em = $this->getDoctrine()->getEntityManager();
    $query = $em->createQuery("SELECT p FROM AcmeBlogBundle:Publish p
            ORDER BY p.update_date DESC");
   $query->setFirstResult(($page-1)*$posts_on_page);
   $query->setMaxResults($posts_on_page);

   $count_query = $em->createQuery("SELECT COUNT(p.id) AS p_count FROM AcmeBlogBundle:Publish p
            ORDER BY p.update_date DESC");


   $posts = $query->getResult();
   $count = $count_query->getResult();

It works well, but I would like to know is it the right way?

meze · Accepted Answer · 2012-04-29 21:26:29Z

1

Yes, it is. The first query will get only the number of records up to the number that is set with setMaxResults. The second query will count how many records a query may return. There's no another way to do two different things.

PS: If you want to build pagination, consider using DoctrineExtensions. It will let you write a query only once (but it still does two queries underneath).

edited Apr 29, 2012 at 21:26

answered Apr 29, 2012 at 21:17

meze

15.1k4 gold badges49 silver badges53 bronze badges

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

Comments

Yaron · Accepted Answer · 2013-07-13 23:50:54Z

0

Doctrine 2.2 includes a pagination class that should solve the problem: http://docs.doctrine-project.org/en/latest/tutorials/pagination.html

answered Jul 13, 2013 at 23:50

Yaron

1,99521 silver badges16 bronze badges

Comments

Claod · Accepted Answer · 2023-03-08 02:58:19Z

0

setMaxResults not working on OneToMany

Example use a subquery, join many linked entities, use setMaxResults... You'll get a limit for the whole query...

So if you want 2 articles, you fetch all their comments. Using setMaxResults 2

You'll have the first article with 2 comments.

This feature is not smart to be polite.

answered Mar 8, 2023 at 2:58

Claod

211 silver badge8 bronze badges

Collectives™ on Stack Overflow

Geting the number of rows in doctrine query

3 Answers 3

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related