Common Table Expressions w/ Rails
Some applications rely heavily on ActiveAdmin.
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.
payments ------------ id amount processing_fee
In ActiveAdmin, you define how you want the csv export to look like in a "csv" block like this.
csv do column :id column :amount column :processing_fee end
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
amount. How do we achieve this with Common Table Expression?
The query that I came up with was
with cte_payments as( select id, amount from payments union select id, processing_fee as amount from payments where processing_fee is not null ) select id, amount from cte_payments
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.
ActiveAdmin.register Payment do controller do if request.csv? query = <<-SQL.squish with cte_payments as( select id, amount from payments union select id, processing_fee as amount from payments where processing_fee is not null ) select id, amount from cte_payments SQL super.find_by_sql(query) else super end end end
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.
ActiveAdmin.register Payment do controller do def scoped_collection if request.format.csv? query = <<-SQL.squish (select id, amount from payments union select id, processing_fee as amount from payments ) payments SQL super.from(query) else super end end end end
The above method will return an
ActiveRecord::Relation instead of an
Array that you get with using Common Table Expressions.