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?
RSS Feed
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
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