I have a rails app with a Project model. Each project has usages which is numeric data.
I need to get all the usage for each month and year. I also need to get usage for specific months and years across multiple projects.
Instead of writing one large service file with raw SQL queries to handle this, I have decided to extract a lot of the code to a model which isn't based on any table, but instead gets the data by using certain SQL aggregation features.
This allows me to take (almost) full advantage of ActiveRecord in consumers to extend the default query to get specific fields (e.g. where the month is May or the project is project #15).
Here is the example of the code I wrote:
class MonthlyUsage < ActiveRecord::Base self.table_name = 'projects' default_scope do select(select_monthly_data_and_dates) .group('year', 'month', '"projects"."id"') end def self.select_monthly_data_and_dates <<~SQL.squish "projects"."id" AS "project_id", SUM("usages"."usage") AS "monthly_usage", date_part('year', "usages"."timestamp")::INT AS "year", date_part('month', "usages"."timestamp")::INT AS "month" SQL end end
In reality there are a few joins and extra steps to this code which I'm omitting here since they aren't relevant.
Basically I'm of two minds on this. On the one hand I think it allows for powerful querying and fits the definition of a model, but on the other hand I think it might be too "hacky" and not really much clearer than just using a service file (e.g. setting the table name as 'projects' even though that's not really the real table name). That's why I'm asking for code review here, to get some opinions on whether this is good or bad, as well as how you would do it.