Rails: ActiveRecord add calculated columns to query result ActiveRecord::Relation / ActiveRecord::Base

Question

For a RoR application I'm maintaining I will be adding reporting style views with a lot of aggregation and analytics that will be calculated in SQL where possible.

However sometimes it's not very convenient to calculate it in SQL as not everything is supported in ActiveRecord/Arel and therefor I am leveraging find_by_sql often.

My main problem though, regardless of the path I choose, is that I haven't found a way to manipulate the ActiveRecord results, whether in Array and/or ActiveRecord::Relation form, in order to add extra calculated fields/columns to the output for easy use in the view.

Here is what I've tried:

• Convert each record r in the result to a hash (or these days, just use r.attributes), however you lose the very easy r.field capability in the view, instead having to revert to r['field']
  • Even converting the entire result with result.as_json turns it into an Array of Hashes and gives you the same problem
• Hacky way by adding '' AS column_name to the SQL/ActiveRecord select, which afterwards allows me to reassign the values easily:

'' as calculated_test_col,
0 as mnd6,
0 as mnd9,
0 as mnd12

• Try to add the column to each record by just assigning a column name/value which results in an error:

# o is an ActiveRecord::Relation dataset
# o.first is an Order model record
o.first.new_column = "bike"

> NoMethodError (undefined method 'new_column=' for #<Order:0x00007fba51628a00>)

o.first << new_column = "bike"

> (undefined method '<<' for #<Order:0x00007fba51628a00>)

o.first < new_column = "bike"

> NoMethodError (undefined method '<' for #<Order:0x00007fba51628a00>)

# I even tried to manipulate the attributes as there is a attributes= method
o.first.attributes= o.first.attributes.merge({"new_column": "bike"})

> ActiveModel::UnknownAttributeError (unknown attribute 'new_column' for Order.)

I also searched for ActiveRecord extension gems that might have features like this, however couldn't find anything.

Preferably I just get the ActiveRecords results, add/manipulate the data and then display them in the view as if it is still that result as if it has the same record.fieldname capability (and things like date/time features).

ps: these records will not be changed/saved back to the database, read only.

Update

• I could convert every record to an OpenStruct, giving me at least the record.fieldname capability back, however it feels like a waste of resources/additional coding.
• I cannot add attr_reader to the model/class (don't know if you are able/allowed to), because on average each model has 100+ query's, don't want to add these fields for just the reporting queries to all.

What am I missing / how have you solved this issue?

Thank you

Answer 1

After much experimentation and searching, it finally hit me that the Model is just a class and I can add attr_accessor.

Due to the size of the current model and the unneeded confusion and overhead of adding fields for current and future query's, I decided to split analytics query's like these into there own classes and create a separate class for each.

In order to keep them separate from normal Models, I created a dedicated file in models/concerns folder and in this file have split the query's into classes that extend the original model.

At first using attr_accessor I got pretty far, however when setting defaults I had an issue..

With some searching I finally found the answer that in ActiveRecord you have after_find, which I leveraged to set the default value to 0.0 for floats

The solution worked, but looks a little bloated:

attr_accessor :mnd6
attr_accessor :mnd9
attr_accessor :mnd12

# set defaults
after_find do |o|
  o.mnd6 = 0.0
  o.mnd9 = 0.0
  o.mnd12 = 0.0
end

I decided to dig in deeper and looked more closely at ActiveRecord attributes, which is when I finally stumbled upon a much cleaner solution:

attribute :mnd6, :float, default: 0.0
attribute :mnd9, :float, default: 0.0
attribute :mnd12, :float, default: 0.0

I later on discovered there already was something posted like this on StackOverflow: Non persistent ActiveRecord model attributes

And here is the Ruby on Rails API page describing it: ActiveRecord Attributes