Formatting Enum columns into a human readable format with SQL
Enum columns can be really handy when working with Rails. There are a lot of built-in helper methods that allow for quickly writing code and they make type-checking simple. However, when working with raw SQL they can be difficult to work with as they’re stored as integers and as such lose their meaning. Keep reading to dig into a shortcut for staying SQL-land while returning an Enum in a human readable format.
First, let’s say we have the following schema and model:
Now if we use SQL to select data from the above table we’d end up with integers in the race_type field. These literally mean nothing to us and can’t really be used for display.
We could iterate over the results and convert them to the corresponding value using Rail’s built-in magic.
This is sufficient for most situations but let’s assume for a one second that the above needs to be done in raw sql. Like a more complicated query with associations and aggregates. The following is a simple example but gets the point across.
If we needed to return the race_type value from the query, how would we know which integer belongs to which value without having to check every record? Using a SQL case statement is a good solution for known key to value mappings.
Alright, we’re done right? Not quite.
The above works correctly but doesn’t plan for a future where new race_type Enums
could be added to Race.race_types. We want something that ensures the above
SQL continues to work even with changes.
We can use a joint ruby-sql solution in order to achieve a flexible query that is
considerate of future changes.
The above uses the plural Enum helper for the column of race_type by calling
Race.race_types. This gives us access to both the integer key along with the
corresponding value. From that we can use a bit of meta-programming to construct
the SQL case statement.
Now the above query should look like:
We can further improve the method’s readability using something like #tap or
#inject.
Now we’re ready for the future!
Got another Enum trick? How about a single table inheritance one? I’d love to talk about it in the comments below.
Join the conversation