Common Table Expressions within Rails

We use ActiveAdmin heavily at work.

And I have a love and hate relationship with it. While it’s an easy way to quickly spin up an admin dashboard in Rails, it is so opinionated that any time you want to do anything slightly out of the ordinary, it quickly becomes a nightmare for me. I guess people may say the same thing about Rails, but I digress…

I recently had to modify a CSV export within ActiveAdmin so that it includes two rows in the CSV export for each one row that’s in the database. For the sake of example, let’s say that we have a payments table where we have three columns.

In ActiveAdmin, you define how you want the csv export to look like in a “csv” block like this.

The above block of code will produce a csv file that will have one row per database row in the payments table. However, the requirement that I was given called for two csv rows per one database row with the processing_fee being displayed as amount. Since we can’t force ActiveAdmin’s CSV processor to produce two rows, we need to feed the CSV processor the collection with two records per one record already preprocessed. At first, I decided to attempt this with Common Table Expressions.

What’s a Common Table Expression (CTE)?

I think of Common Table Expressions as a temporary table that you construct within a query that you can query from. I think of it as a more temporary lite version of database views. Let’s say that you need to produce a result set with data coming from multiple tables. You could perform complex joins and then query from the joined result set, or you could create a CTE and simplify your queries. In my specific case, I needed to create a temporary table that contained two rows per one row in the database with the second row displaying the processing_fee as amount. How do we achieve this with Common Table Expression?

The query that I came up with was

The above query will construct a temporary table called cte_payments with the result set from the query contained inside the parenthesis. Then you can query the cte_payments table as you normally would. With the above query, we can do the following within ActiveAdmin to create a custom scope to feed into ActiveAdmin’s CSV processor.

The above in the if request.csv? block work in creating a collection of result sets that you want. However, find_by_sql method returns an Array rather than the ActiveRecord::Relation collection and unfortunately ActiveAdmin requires that you feed ActiveRecord::Relation into its CSV processor. If you are working outside of ActiveAdmin or in a situation where an Array will work fine for you, you can stop reading here. If you need your custom query that you execute within Rails to return an ActiveRecord::Relation, then read on.

If you must need to have your ActiveRecord query return an ActiveRecord::Relation collection, and you would like to stick with using CTEs, you can try using the postgres_ext gem that was created by the folks at DockYard. Unfortunately, the gem is no longer maintained. You can also dig into Arel and play in that world. There’s an excellent blog post on how to work in Arel to utilize Common Table Expressions here. I personally think that working directly with SQL is easier than working with a gem that’s no longer maintained and trying to figure out Arel, so I opted for rewriting my CTE query with a regular subquery that I can use with the ActiveRecord’s from method. If I rewrite the CTE query that I wrote above as a subquery, and use ActiveRecord to return an ActiveRecord::Relation collection, it would look like the following.

The above method will return an ActiveRecord::Relation instead of an Array that you get with using Common Table Expressions.