Having a strange issue. We are using MariaDB 5.5 and doctrine/orm 2.3.3, and trying to use the Doctrine Paginator with DQL. http://docs.doctrine-project.org/en/latest/tutorials/pagination.html
The DQL has an ORDER BY clause [see below for an illustration example]. However, the result is not sorted at all for a given page size. And, if we increase the page size to cover the entire result set, the sorting becomes correct.
$dql = "SELECT a, b FROM EntityA a JOIN a.propertyB b ORDER BY a.createdOn DESC";
$query = $this->em->createQuery($dql)
->setMaxResults($pageSize)
->setFirstResult($offset);
$paginator = new Paginator($query, $fetchJoinCollection=true);
....
I dumped the sql and manually ran it. The sql also gave the correct sorting. So something is causing the sorting issue inside Doctrine's Paginator class.
When I set $fetchJoinCollection=false and passed it to the Paginator constructor, the sorting became correct for any given $pageSize!
Read Doctrine source code [Doctrine/ORM/Tools/Pagination/Paginator.php]. With $fetchJoinCollection=true, doctrine uses a WhereInWalker to get the final result, which doesn't respect the ORDER By clause in the DQL, because the IN() clause doesn't generate the result in the same order as the ids inside the IN() clause.
A sorting solution for the IN() clause can be found in Ordering by the order of values in a SQL IN() clause. But I can't find Doctrine using that.
Anyone with Doctrine internal knowledge would shed some light?! Thanks!
