Sorting a view by a list field's allowed values

There’s a neat feature in MySQL which lets you sort a result set by arbitrary field values. It’s the ORDER BY FIELD() function. Here’s how to leverage this in your Drupal views.

Let’s say you have a field in your Article content type called Status, and it has the following allowed values:

Draft
Pending Approval
Published
Postponed
Canceled

It can be very helpful to sort the articles by status. You could key your allowed values with alphabetical prefixes, numbers, etc. But let’s say you didn’t. Or don’t want to.

With bare MySQL, the query would look something like this (not an actual Drupal query, but used to illustrate how FIELD() works):

SELECT *
FROM articles
ORDER BY FIELD(status, 'Draft', 'Pending Approval', 'Published', 'Postponed', 'Canceled')

This is now possible in Drupal & Views with the Views List Sort module, which creates a sort handler that populates the FIELD() sort with the allowed values of a given “List (text)” field.

To use it is easy, just add the “List (text)” field to your sort criteria, and set “Sort by allowed values” to “yes”.