Ad-hoc fields in AR queries

Sometimes there's a need to crunch some custom data beyond what's stored in a model's table, for a report or somesuch. There are several ways to do this with differing benefits and trade-offs. Let's take an overview of available strategies. We'll be using a naive example where the custom data column is the length of a User record's email field.

1. (naive) Ruby method

Just define model instance method! This is easy to implement, test and understand. Suffices for most cases, but can result in N+1 queries if you'll be accessing some association data, doing counting, summing etc.

class User
  def email_length
    email.size
  end
end

2. Custom select

Pushes the processing into SQL for performance, becomes harder to maintain and use. Can cause headache if several such custom selects need to be used.

class User
  scope :with_email_length, -> {
    select_sql = <<~SQL
      length(email) AS email_length
    SQL

    all.select("#{table_name}.*", select_sql)
  }
end

User.relevant.with_email_length.first.email_length

3. Custom join

Goes even further into the SQL, but puts the custom data querying in a JOIN, rather the toplevel SELECT part, giving some flexibility as to the selects being used (even allows aliasing!). Can come with a performance hit because join will be evaluated for all records, before WHERE...

class User
  scope :with_email_length, -> {
    join_sql = <<~SQL
      LEFT JOIN (
        SELECT
          id AS id
          length(email) AS email_length
        FROM #{table_name}
      ) AS email_length_data ON email_length_data.id = #{table_name}.id      
    SQL

    all.joins(join_sql).select(
      "#{table_name}.*",
      "email_length_data.email_length AS email_length"
    )
  }
end

# Data access pattern is the same as in SELECT case,
# but will work better when chaining several such custom field scopes or doing more custom selecting
User.relevant.with_email_length.first.email_length

4. CTE

I haven't explored this much yet, but all.with(email_length_data: email_length_data) where email_length_data is some AR query object can also be a thing, especially if the gathered data need to be used by several WHERE?SELECT clauses in the main query, a way to DRY SQL.