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.