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:

CPANABasicBlogBundle for Symfony2

Having as a starter point the http://tutorial.symblog.co.uk/ ( about which I wrote here ) I developed blog bundle having the following main features:

Frontside
- view all blog posts with pagination
- view individual blog posts
- add comments

Admin
- view a list of all blog posts with Edit and Delete options.
- view the list of comments with options to Approve/Unapprove or Delete.

Adding comments form

In the Symblog tutorial there was a add comment functionality but for me didn't worked throwing an error like "Catchable Fatal Error: Object of class XYZ could not be converted to string in Doctrine\DBAL\Statement.php" ( see this post about it ). To solve the issue I've added to Entity\Blog.php the following function:

    public function __toString()
    {
        return strval($this->id);
    }

Also I modified the CommentType.php to make all the comments unapproved initially:

// \src\CPANA\BasicBlogBundle\Form\CommentType.php

public function buildForm(FormBuilderInterface $builder, array $options)
    {
        $builder
            ->add('user')
            ->add('comment')
            ->add('approved','hidden', array('data' => '0',))
         ;
    }

Uploading photos

What I want to implement: upload photo, copy the file in a specific path  and save the name of the file on the database

Make sure you are pointing to the right path in the template. In src\CPANA\BasicBlogBundle\Resources\views\Blog\show.html.twig:

<img src="{{ asset(['bundles/basicblogbundle/images/', blog.image]|join) }}" alt="{{ blog.title }} image not found"  />

At this path: 'bundles/basicblogbundle/images/' I should upload the files in the controller.

Make sure to indicate that the "file" field is not mapped to the Entity.

->add('image', 'file', array('mapped'=>false))

Upload photo and save in database only the name
------------------------------------------------
if ($form->isValid()) {
            $newFilename = $form['attachment']->getData()->getClientOriginalName();
            // Handle picture upload process
            $uploadDir=dirname($this->container->getParameter('kernel.root_dir')) . '/web/bundles/basicblogbundle/images/';
            $form['image']->getData()->move($uploadDir,$newFilename);
            // End of upload
           
            $blog->setImage($newFilename);
            $em = $this->getDoctrine()->getManager();
            $em->persist($blog);
            $em->flush();

This works fine but we should make sure the file names are unique so they do not conflict when you try to upload a file with same name.

We should add random string function in a class. I do no think this should be considered a service, so I will not register it as a service in the Dependency Injection container.
http://symfony.com/doc/current/best_practices/business-logic.html

Create an Utils folder. Place there RandomString.php, add a static function: randomStr

namespace CPANA\BasicBlogBundle\Utils;

class RandomString
{

    public static function randomStr($length = 10)
    {
        $characters = '0123456789abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ';
        $charactersLength = strlen($characters);
        $randomString = '';
        for ($i = 0; $i < $length; $i++) {
            $randomString .= $characters[rand(0, $charactersLength - 1)];
        }
        return $randomString;
   
    }


After this include the randomStr in the fileName:

$newFilename =RandomString::randomStr() . $form['image']->getData()->getClientOriginalName();
---------------------------
when testing the new feature I noticed that the photo is not available when browsing the blog, reason: the name included in the template is trimmed. I looked in the database , in the "image" column, same there. Checking the type of column I noticed the column is set to     varchar(20)!!!! that's the problem right there.

Solution:
modify in Entity\Blog.php the ORM annotation for $image from
@ORM\Column(type="string", length=20)
to
@ORM\Column(type="string", length=255)

After this update the DB schema using Symfony 2 command line:

php app/console cache:clear
php app/console doctrine:schema:update --force

 Pagination

In the home view of the blog I should list all the blog posts. I want to limit to 3 per page an have buttons to navigate though posts.

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

CPANABasicBlogBundle_homepage:
    pattern:  /blog/{currentPage}
    defaults: { _controller: CPANABasicBlogBundle:Blog:blogHome, currentPage: 1 }
    requirements:
        _method:  GET
        currentPage: \d+

--------------
Modify the repository as seen here - http://anil.io/post/41/symfony-2-and-doctrine-pagination-with-twig:

        public function getAllPosts($currentPage = 1)
        {
            // Create our query
            $query = $this->createQueryBuilder('p')
                ->orderBy('p.created', 'DESC')
                ->getQuery();

            $paginator = $this->paginate($query, $currentPage);

            return $paginator;
        }

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

            return $paginator;
        }
----------------------------------------------------
In controller retrieve posts and pass them to view

    public function blogHomeAction($currentPage=1)
    {
        $em = $this->getDoctrine()
            ->getEntityManager();

        $posts = $em->getRepository('CPANABasicBlogBundle:Blog')
            ->getAllPosts($currentPage);
       
        $iterator=$posts->getIterator();
        $limit = 3;
        $maxPages = ceil($posts->count()/$limit);
        $thisPage = $currentPage;
       
        return $this->render(
            'CPANABasicBlogBundle:Blog:home.html.twig', array(
            'blogs' => $iterator,
            'maxPages'=>$maxPages,
            'thisPage' => $thisPage,
            )
        );

In template showing the posts is the same we just need to add pagination buttons:
This will look nice with some css :)

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

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

The photo should not be a mandatory information. I had to do the following modifications to implement that:

Add in controller to the form builder 'required' => false :
 ->add('image', 'file', array('mapped'=>false,'required' => false,))

Also in controller handle new uploaded file name and path only if it was any file selected in the form:

if (!is_null($form['image']->getData())) {....}
 
modify the Entity/Blog.php to accept NULL values for image field.

 * @ORM\Column(type="string", length=255, nullable=true)
Update database using console command.
        php app/console cache:clear
        php app/console doctrine:schema:update --force

Fine tuning - allow deleting blog posts even if they have comments

While browsing happily and testing the functionality I discovered I cannot delete an article having comments, receiving some nasty error: "SQLSTATE[23000]: Integrity constraint violation: 1451 Cannot delete or update a parent row: a foreign key constraint fails ..."

I believe it should be good that an admin to be able to delete an article even if it has comments added to it. So I will modify the Database structure to allow this behavior.

There are two kinds of cascades in Doctrine:

1) ORM level - uses cascade={"remove"} in the association - this is a calculation that is done in the UnitOfWork and does not affect the database structure. When you remove an object, the UnitOfWork will iterate over all objects in the association and remove them.

2) Database level - uses onDelete="CASCADE" on the association's joinColumn - this will add On Delete Cascade to the foreign key column in the database:

@ORM\JoinColumn(name="father_id", referencedColumnName="id", onDelete="CASCADE")


Update the Entity/Comment.php
    /**
     * @ORM\ManyToOne(targetEntity="Blog", inversedBy="comments")
     * @ORM\JoinColumn(name="blog_id", referencedColumnName="id",  onDelete="CASCADE")
     */
    protected $blog;

Update the database using console:
    php app/console cache:clear
    php app/console doctrine:schema:update --force

I will soon add the BasicBlogBundle on Github and Packagist.