In Rails, it’s easy to get a bunch of records from your database if you have their IDs:
But what if you wanted to get the records back in a different order? Maybe your search engine returns the most relevant IDs first. How do you keep your records in that order?
You could try
But that doesn’t work at all. So how do you get your records back in the right order?
The compatible way:
Just like Ruby, SQL supports
You don’t see it too often, but
case...when statements can almost act like hashes. You can map one value to another:
That case statement kind of looks like a hash:
So, you have a way to map keys to order they should appear in. And your database can sort and return your results by that arbitrary order.
Knowing that, you could put your IDs and their position into a
case statement, and use it in a SQL
So if you wanted your objects returned in the order [2, 1, 3], your SQL could look like this:
That way, your records are returned in the right order. The
CASE transforms each ID into the order it should be returned in.
Of course, that looks ridiculous. And you could imagine how annoying a clause like that would be to build by hand.
But you don’t have to build it by hand. That’s what Ruby’s for:
Exactly how we wanted it!
A cleaner, MySQL-specific way
If you use MySQL, there’s a cleaner way to do this. MySQL has special
ORDER BY FIELD syntax:
You could also generate that from Ruby:
So, if you’re using MySQL, and not too worried about compatibility, this is a good way to go. It’s a lot easier to read as those statements fly through your logs.
When you want to display records in a specific, arbitrary order, you don’t need to sort them in Ruby. With a little code snippet, you can let the database do what it’s good at: finding, sorting, and returning data to your app.