Computed columns with the find_by_sql method in a Rails application

Introduction

Ruby is a beautiful expressive programming language. Like many other programming languages, there are many ways to accomplish the same task. In this article, I will share how I used the find_by_sql method in Rails to write a SQL query to replace a less efficient way of generating computed values. First, I will share the business case, my original solution, and finally, my updated solution. Let's dive in!

The Business Case

The business requirements state that we must sort customer expenses in descending order by the date the expense record is created. The expense records are displayed on the customer's dashboard. We can do something like this to get the results:

current_user.expenses.where(created_at: Date.current.all_month).order(created_at: :desc)

The Problem

After diving into the code, we learn the customer can update the date for a given expense in the customer dashboard. However, the created_at date field does not change when the customer updates the expense record. Instead, a back_date field is updated with the new date.

We need to ensure that our sorted expenses are sorted by created_at unless the back_date field is truthy. If the back_date is available, we need to sort the expenses using the back_date. Although this specific example is fictional, it is similar to a problem I faced while building a side project.

Solution #1

# app/models/expense.rb

  scope :back_dated, -> { where.not(back_date: nil) }
  scope :not_back_dated, -> { where(back_date: nil) }

  def self.w_computed_date   
back_dated_w_computed_date.concat(not_back_dated_w_computed_date).map(&:symbolize_keys).sort_by(&:date).reverse
  end

  def self.back_dated_w_computed_date
    back_dated.map { |e| e.as_json.merge({ date: e[:back_date] }) }
  end

  def self.not_back_dated_w_computed_date
    not_back_dated.map { |e| e.as_json.merge({ date: e[:created_at] }) }
  end

# app/controllers/expenses_controller.rb

def index
  @expenses = current_user.expenses.w_computed_date
end

This works, but it can cause some performance issues on our server. Let's solve this with a better solution.

Solution #2

ActiveRecord provides the find_by_sql method to write custom SQL. Before, we implement this method, let's make sure we can get the correct results with SQL.

We could do something like this:

SELECT *,
       coalesce(back_date, created_at) as date
FROM expenses
WHERE user_id = 1
ORDER BY date DESC

We select all expenses for the user. If the expense is back-dated, we return the back_date as date.If the expense is not back-dated, we return created_at as date .This is great, but our business requirements state we need to sort the expenses for a given date range. This gives us all the expense records. If you are not very familiar with writing SQL code, like I was when I faced this problem, you may try to solve the problem like this:

SELECT *,
       coalesce(back_date, created_at) as date
FROM expenses
WHERE user_id = 1;
  AND date >= '2023-01-01'
  AND date <= '2023-01-31'

This will not work. The SQL code is not executed in the order that it reads.

SELECT * 
FROM expenses
WHERE ...

The SQL is executed as:

FROM expenses
WHERE...
SELECT * 

So in the case of our query, we cannot filter by date because the computed date is only available in the execution context after the WHERE clause is read. Thankfully, there is a way to gain more control over the execution of the SQL using sub-queries.

SELECT *
FROM (SELECT *,
             COALESCE(back_date, created_at) as date
      FROM expenses) AS sub_query
WHERE user_id = 1
  AND date >= '2023-02-01'
  AND date <= '2023-02-28'
ORDER BY date DESC;

By wrapping the initial query in parenthesis, we tell the database to read this query first. Then, any values in the subquery will be available in the parent query, which includes date.

Now, let's convert the SQL to something that Rails understands.

# app/models/expense.rb

  def self.w_computed_date
    select('* , COALESCE(back_date, created_at) as date').order(date: :desc)
  end

  def self.for_month(date)
    query = <<-SQL
        SELECT * 
        FROM (:subquery) as w_computed_date
        WHERE date >= :date_start
        AND date <= :date_end
    SQL

    placeholders =  {
      subquery: w_computed_date,
      date_start: date.beginning_of_month,
      date_end: date.end_of_month
    }

    find_by_sql [query, placeholders]
  end

The find_by_sql method allows you to use placeholders as symbols and then pass a hash as the second argument, including the placeholders' values. Placeholders increase the readability of our query and are preferable to using string interpolation.

Also, you may have noticed that we did not add WHERE user_id = 1 to the SQL string. We don't need to since the user table has a one-to-many association with the expenses table, and we call this method on an instance of the user object.

We can use our new method in the controller to send the expenses with the computed date to the front end.

# app/controllers/expenses_controller.rb

def index
  @date = params['date']
  @expenses = current_user.expenses.w_computed_date 
end

Conclusion

In this article, I share my business case, the problem, my original solution, and a better solution. The final solution allowed me to solve our problem with less code, requiring fewer tests and being easier to maintain. I learned so much while working on this problem, and I hope it helps you somewhere during your programming journey. Thanks for reading.