Thursday, November 12, 2015

Symfony and Doctrine: Pagination

In my previous post I've wrote a custom DQL to retrieve all the ads from the database. This is not really the situation in a normal application with thousands of classifieds ads, pagination should be used to limit the number of rows returned from database and keep track of the next sets of rows.

I will first implement pagination in a similar way as I did for CPANABasicBlogBundle in this post.

Documentation:
http://doctrine-orm.readthedocs.org/projects/doctrine-orm/en/latest/tutorials/pagination.html
http://anil.io/post/41/symfony-2-and-doctrine-pagination-with-twig  - tutorial

Starting with version 2.2 Doctrine ships with a Paginator for DQL queries. It has a very simple API
and implements the SPL interfaces Countable and IteratorAggregate.

I. Pagination using Paginator class

1. First modify the path.
The path should have a parameter "currentPage" which can be optional, and the default value is 1:

cpana_classifieds_public_ads_show_all:
    path:     /classifieds/ads/showall/{currentPage}
    defaults: { _controller: CPANAClassifiedsBundle:Pub\Ads:showAll,currentPage:1}


       
2. Modify the repository :

    public function findAllAdsCustom($currentPage = 1, $limit)
    {
        //retrieve the associated Query object with the processed DQL
        $qb= $this->getEntityManager()
            ->createQuery(
                'SELECT  a.idAds, a.title, a.description, a.price
                ,u.name
                ,cat.categoryName
                ,c.currencyName
                ,ca.cityAreaName
                ,city.cityName
                FROM CPANAClassifiedsBundle:Ads a
                JOIN a.idUser u
                JOIN a.idCategory cat
                JOIN a.idCurrency c
                JOIN a.idCityArea ca
                JOIN ca.idCity city'
               
            )
            ->getQuery();
           
        $paginator = $this->paginate($query, $currentPage, $limit);

        return $paginator;
    }
   
    public function paginate($dql, $page = 1, $limit = 3)
    {
        $paginator = new Paginator($dql);
        $paginator->setUseOutputWalkers(false);
       
        $paginator->getQuery()
            ->setFirstResult($limit * ($page - 1)) // Offset
            ->setMaxResults($limit); // Limit

        return $paginator;
    }

-------------------------------------------
If you try to run the code without this line " $paginator->setUseOutputWalkers(false);" you get this  error "Error: Not all identifier properties can be found in the ResultSetMapping:idAds"

http://www.doctrine-project.org/jira/browse/DDC-1927
http://stackoverflow.com/questions/24837913/doctrine-2-pagination
https://groups.google.com/forum/#!topic/doctrine-user/AVbeIyaGvfQ

3. In the controller add:
....
use Doctrine\ORM\Tools\Pagination\Paginator
...

    public function showAllAction($currentPage)
    {
        $limit = 3;
        $em = $this->getDoctrine()->getManager();
        $ads = $em->getRepository('CPANAClassifiedsBundle:Ads')->findAllAdsCustom($currentPage, $limit);
       
        $allSpecificItems = $em->getRepository('CPANAClassifiedsBundle:SpecificItems')->findAllItemsCustom();
       
         $maxPages = ceil($ads->count()/$limit);
        $thisPage = $currentPage;
       
       
        if (!$ads) {
            throw $this->createNotFoundException('Unable to find ads.');
        }
       
        return $this->render('CPANAClassifiedsBundle:Pub\Ads:show_all.html.twig', array(
            'all_ads' => $ads,
            'maxPages'=>$maxPages,
            'thisPage' => $thisPage,
            'all_items' => $allSpecificItems
        ) );
    }
----------------------

4. View
-------
{% block content %}

<table style="width:100%">
    {% for ad in all_ads %}
        <tr><hr></tr>
        <tr><h1> {{ ad['title'] }}</h1></tr>
        <tr><p><b>Posted by: </b> {{ ad['name'] }}</p></tr>

        .....

</table>

    {% if maxPages > 1 %}
        <ul>
            {%if thisPage > 1 %}
                <li >
                    <a href="{{ path('cpana_classifieds_public_ads_show_all', {currentPage: thisPage-1 < 1 ? 1 : thisPage-1}) }}">«</a>
                </li>
            {% endif %}
      
            {# Render each page number #}
            {% for i in 1..maxPages %}
                <li>
                    <a href="{{ path('cpana_classifieds_public_ads_show_all', {currentPage: i}) }}">{{ i }}</a>
                </li>
            {% endfor %}

            {# `»` arrow #}
            {%if thisPage < maxPages %}
                <li>
                    <a href="{{ path('cpana_classifieds_public_ads_show_all', {currentPage: thisPage+1 <= maxPages ? thisPage+1 : thisPage}) }}">»</a>
                </li>
            {% endif %}
        </ul>
    {% endif %}

{% endblock %}


I have only 6 ads so I modified the limit=1 to get the following:

--------------------------------------------------------------------------------------------------

 II. Pagination directly from DQL


http://doctrine-orm.readthedocs.org/projects/doctrine-orm/en/latest/reference/dql-doctrine-query-language.html

14.7.6.5. First and Max Result Items (DQL Query Only)

You can limit the number of results returned from a DQL query as well as specify the starting offset, Doctrine then uses a strategy of manipulating the select query to return only the requested number of results:

    Query::setMaxResults($maxResults)
    Query::setFirstResult($offset)

If your query contains a fetch-joined collection specifying the result limit methods are not working as you would expect. Set Max Results restricts the number of database result rows, however in the case  of fetch-joined collections one root entity might appear in many rows, effectively hydrating less than  the specified number of results.
---------------------------------------------------------------------------------------------------------

1. Modify AdsRepository.php by adding:

    public function findAllAdsMyPagination($currentPage = 1, $limit)
    {
       
        $query= $this->getEntityManager()
            ->createQuery(
                'SELECT  a.idAds, a.title, a.description, a.price
                ,u.name
                ,cat.categoryName
                ,c.currencyName
                ,ca.cityAreaName
                ,city.cityName
                FROM CPANAClassifiedsBundle:Ads a
                JOIN a.idUser u
                JOIN a.idCategory cat
                JOIN a.idCurrency c
                JOIN a.idCityArea ca
                JOIN ca.idCity city'
               
            );
        $query->setFirstResult($limit * ($currentPage - 1)) // Offset
            ->setMaxResults($limit); // Limit
       
        $result = $query->getResult();
        return $result;
    }
   
    public function countAllAds()
    {
        $query= $this->getEntityManager()
            ->createQuery(
                'SELECT  count(a.idAds) FROM CPANAClassifiedsBundle:Ads a');
               
        $count = $query->getSingleScalarResult();;
        return $count;
    }



Note:  As you can see I've added a new method which is counting all the records in ads table.

2. Modify controller to use the new  method findAllAdsMyPagination():

public function showAllAction($currentPage)
    {
        $limit = 3;
        $em = $this->getDoctrine()->getManager();
       
        $ads = $em->getRepository('CPANAClassifiedsBundle:Ads')->findAllAdsMyPagination($currentPage, $limit);
        $totalAds = $em->getRepository('CPANAClassifiedsBundle:Ads')->countAllAds();
        $allSpecificItems = $em->getRepository('CPANAClassifiedsBundle:SpecificItems')->findAllItemsCustom();
       

        $maxPages = ceil($totalAds/$limit);
        $thisPage = $currentPage;

        if (!$ads) {
            throw $this->createNotFoundException('Unable to find ads.');
        }
       
        return $this->render('CPANAClassifiedsBundle:Pub\Ads:show_all.html.twig', array(
            'all_ads' => $ads,
            'maxPages'=>$maxPages,
            'thisPage' => $thisPage,
            'all_items' => $allSpecificItems
        ) );
    }
-----------------------
 Nothing changes in the view
 -------------------------------

III. Loading information from the many side of the Many-To-One relation.

 Now let's improve also the way I retrieve the associated SpecificItems. At this moment I retrieve the entire table,  but I should load only the values for the set of ads listed on one page (after applying pagination).

 1. In "SpecificItemsRepository" I created a new method which receive as parameter a list of ads as an array of arrays.

 

    public function findItemsForAdsList($ads)
    {
        $ads_id_list = array();
        foreach($ads as $ad) {
            $ads_id_list[]=$ad['idAds'];
        }
        $query = $this->getEntityManager()
            ->createQuery(
                'SELECT  si.itemValue
                        ,IDENTITY(si.idAds)
                        ,ty.itemTypeName
                FROM CPANAClassifiedsBundle:SpecificItems si
                JOIN si.idSpecificItemType ty
                WHERE IDENTITY(si.idAds) IN (:list)'
            );
        $query->setParameter('list', $ads_id_list);
      
        return $query->getResult();
    }

 ------
 2. In controller just replace FindAllItemsCustom() with 

 $allSpecificItems = $em->getRepository('CPANAClassifiedsBundle:SpecificItems')->findItemsForAdsList($ads);

3.  Nothing changes in the view.

The SQL queries generated by Doctrine are the following:

No comments:

Post a Comment