Ordering date from n+1 to n-1 in Doctrine2

Question

I requested this yesterday : SQL Ordering Data from row "n+1" to "n-1"

But I'm using doctrine2 and in DQL, Case statement isn't available. So how could I do this in DQL ?

Thank you !

(I use doctrine in symfony2)

Answer 1

1. Fetch n^th object from database.
2. Fetch all objects from database ordered by ID.

3. Sort them on the PHP side:

   <?php
   
   $objects = range(1, 10); // objects from 2nd point
   $object = 6;             // object from 1st point
   
   $result = $higher = $lower = array();
   
   foreach ($objects as $o) {
       if ($o < $object) {
           $lower[] = $o;
       } else if ($o > $object) {
           $higher[] = $o;
       }
   }
   
   $result = array_merge($higher, $lower);
   
   print_r($result);
   

   Output:

   Array
   (
       [0] => 7
       [1] => 8
       [2] => 9
       [3] => 10
       [4] => 1
       [5] => 2
       [6] => 3
       [7] => 4
       [8] => 5
   )
   

It could be probably done shorter and in more efficient manner, but unless you're going to sort hundreds of objects it won't matter. At least the code is simple and easy to maintain.

Answer 2

You can just pass plain SQL via doctrine. That should be way faster than sorting the objects in PHP.

Answer 3

CASE expressions exist in Doctrine's DQL btw. ... but not that well documented.

you can aswell create a named native query in your entity using:

namespace MyProject\Model;
/**
 * @ORM\NamedNativeQueries({
 *      @ORM\NamedNativeQuery(
 *          name           = "customOrderAddresses",
 *          resultClass    = "Address",
 *          query          = "SELECT ID FROM adresses WHERE ID <> ? ORDER BY CASE WHEN ID > ? THEN 0 ELSE 1 END, ID"
 *      ),
 * })
 */
class Address
{
    // ....
}

Otherwise you could introduce a custom DQL function.