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 processing_fee as 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.