Short: Order Result Sets by an Array

Here’s an easy way to add ordering by an array in your doctrine query. This is only useful in a handful of use cases, namely when a query is receiving a list of IDs in the order they should appear. The only way to order them correctly would be via a method like this.

An example of this would be if a request was made for the ids 12, 224, 1, and 14. How are you going to order your result set correctly? Like this:

  $ids = array(12, 224, 1, 4)

  $query->whereIn('id', $ids)->orderBy('id', $ids)->execute();

That’s it!

The actual SQL is a bit more complicated, and uses the MySQL FIELD function like so:

  SELECT * from my_table where id in (12, 224, 1, 4) ORDER BY FIELD(id, 12, 224, 1, 4) = 0 ASC, FIELD(id, 12, 224, 1, 4) ASC

Yes, this does work when using the addOrderBy function. Yes, you can pass ASC and DESC values to ORDER BY. And yes, you can use other column types in addition to primary keys / integers.

If you’re wondering why the query above contains TWO FIELD calls, here’s why: The first FIELD call is there so that any result not matched in the ORDER BY array is sorted after those matched in the array. I made this adjustment because it seems intuitive to me that if you don’t supply a whereIn argument, the passed ORDER BY array of values is first, followed by however else ordering is deemed necessary.

You can view the code on github. Thoughts?

Disclaimer: This has only been tested using a MySQL database

20 Jul 2010 | 6 comments »

6 comments

  • DuoSRX - July 20, 2010

    Sadly it doesn’t work with SQLite.
    I stumbled upon this because my unit tests use an in-memory SQLite database and failed each time I tried to use order by field.
    http://stackoverflow.com/questions/3091800/order-by-field-with-sqlite

  • Brent Shaffer - July 20, 2010

    Thank you for posting this. For SQLite, if you ever needed this functionality it would be faster to sort on the PHP level, using an array sort method like the one below:

    http://gist.github.com/483357

  • Phil Moorhouse - January 5, 2011

    Just a quick note for anyone who wasn’t clear how to get the correct query object; this works for me:

    Download the linked code from github, save it as:
    Doctrine_Query_Extra.class.php
    somewhere under your /lib dir

    then in your Table class:
    $q = Doctrine_Query_Extra::create(null, ‘Doctrine_Query_Extra’);

    Hope that helps :)

  • Brent Shaffer - January 20, 2011

    That will work, although it’s better to use the setAttribute function on the Doctrine_Manager object.
    This should be added via the configureDoctrine method in your ProjectConfiguration class. The full code looks something like this:

      /**
    * Configure the Doctrine engine
    */
  public function configureDoctrine(Doctrine_Manager $manager)
  {
    $manager->setAttribute(Doctrine_Core::ATTR_QUERY_CLASS, 'Doctrine_Query_Extra');
  }
  • Kye - November 20, 2011

    Thank you. You just solved a weeks work!

  • Marco de Krijger - February 8, 2012

    Too bad this is a Doctrine 1.x solution only. For now I’m using PDO to achieve something similar

Add comment