MySQL manually defined order the Arel Way

MySQL has the ability to retrieve a list of rows sorted by a manually defined order through the use of the FIELD() function. But what does that look like when using ActiveReceord’s Arel to compose SQL queries?

If you’re very committed to using Arel’s design-specific language, there exists a way to use it for this purpose. It involves creating a NamedFunction using Arel. This will allow you to continue to use Arel’s syntax to compose a SQL query that isn’t supported out of the box by ActiveRecord’s query interface.

What’s the field() function?

The FIELD() function rather conveniently returns the index position of a search string within a given set. For me, it’s useful for sorting rows where some need to be sticky to the top, i.e. when I need to use a custom sort order based on a predefined set of identifiers.

Let’s say you wanted to display a list of latest blog posts, but you wanted to make sure that certain posts were stuck to the top of the list in a custom order. This could be accomplished if you have a list of identifiers for those sticky posts.

SELECT id, slug, title
FROM   blog_posts
ORDER  BY FIELD(slug, "sticky-2", "sticky-1") DESC,
          created_at DESC
LIMIT  3

+-----+------------+---------------------------+
| id  | slug       | title                     |
+-----+------------+---------------------------+
| 433 | sticky-1   | My top sticky post        |
| 249 | sticky-2   | My second top sticky post |
| 549 | other-post | My latest post            |
+-----+------------+---------------------------+

In this example, you can see that by defining a list of slugs in the FIELD() function, it’s causing those found slugs to be sorted higher in the result.

Something else to point out is the slugs are queried in the reverse order that they appear in the result. This is because the FIELD() function returns the index position of the current field in the given list, and returns 0 for fields not in the list. Whichever value we want to appear first in the result should appear last in the call to the FIELD() function.

Additionally, it’s worth pointing out the FIELD() function is great for situations where you want to hand-pick the order of only the first or last couple rows returned. If you’re just trying to select a few rows by ID, you could obviously use a simple WHERE clause.

In Arel

Ideally, I want whatever I write to be re-usable, so this example will start out as a method call. This method could belong in a model. Here is how I was able to compose a SQL query with Arel that uses the FIELD() function.

def by_slugs(*slugs)
  to_sql = ->(slug) { Arel::Nodes::SqlLiteral.new("'#{slug}'") }
  Arel::Nodes::NamedFunction.new(
    "FIELD",
    [BlogPost.arel_table[:slug], *slugs.reverse.map(&to_sql)]
  ).desc
end

BlogPost.order(by_slugs('sticky-1', 'sticky-2'))

BlogPost Load (0.3ms) SELECT `blog_posts`.* FROM `blog_posts` ORDER BY FIELD(`blog_posts`.`slug`, 'sticky-2', 'sticky-1') DESC

Very nice! So let’s unpack what’s happening here. We started by creating a Proc to_sql as a way to make this method a little easier to read. It’s being used in the .map() function a little lower.

The real meaty part of this method is the call to Arel::Nodes::NamedFunction. This is a built-in way to describe a function that isn’t part of Arel, but can still be used to compose a query. We’re passing two arguments: the name of the function and arguments for that function.

The reason we’re calling .reverse on the slugs is to account for the index position ordering discussed above. I wanted the function to make intuitive sense to the person using it, so I decided to allow writing slugs in the order one would expect to receive them, and it “just works.”

Caution

One important caveat here is the FIELDS() function is not available in other databases, namely PostgreSQL. So if a huge selling point for using Arel its ability to translate its composed queries to any given database engine, you lose that here. To me, it’s very low risk to use Arel this way given how rare it is to actually switch database vendors.