fixing random pagination results in knp_paginator and mysql

KnpPaginatorQuerySubscriber

<?php

namespace General\GeneralBundle\EventListener;

use Symfony\Component\EventDispatcher\EventSubscriberInterface;
use Knp\Component\Pager\Event\ItemsEvent;
use Doctrine\ORM\Query;
use Doctrine\ORM\QueryBuilder;

class KnpPaginatorQuerySubscriber implements EventSubscriberInterface
{
    /**
     * @param ItemsEvent $event
     * @throws \Exception
     */
    public function items(ItemsEvent $event)
    {
        // NOTE: added sort by ID (primary key) as secondary ordering, because of mysql randomizing results during pagination
        // https://github.com/KnpLabs/KnpPaginatorBundle/issues/274
        // "If it is important to ensure the same row order with and without LIMIT, include additional columns in the ORDER BY clause to make the order deterministic"
        // http://dev.mysql.com/doc/refman/5.6/en/limit-optimization.html

        if ($event->target instanceof QueryBuilder) {
            $entity = $event->target->getRootEntities()[0]; // main entity
            $identifier = $event->target->getEntityManager()->getClassMetadata($entity)->getSingleIdentifierFieldName(); //getSingleIdentifierColumnName();
            $alias = $event->target->getRootAliases()[0]; // main alias
            $event->target->addOrderBy($alias . '.' . $identifier, 'desc'); // in most cases we want older information first
        }
        if ($event->target instanceof Query) {
            throw new \Exception('Always use QueryBuilder in pagination');
        }
    }

    /**
     * @return array
     */
    public static function getSubscribedEvents()
    {
        return array(
            'knp_pager.items' => array('items', 255), // make sure that our subscriber is the first one
        );
    }
}

services.yml

services:
    general.knp_paginator_query_subscriber:
        class: General\GeneralBundle\EventListener\KnpPaginatorQuerySubscriber
        tags:
            - { name: knp_paginator.subscriber }
        scope: request