# Rails model for aggregate data instead of a real table

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.

I agree with you that this does not really fit a service nor a model. This might be more like a Query object.

class MonthlyUsageQuery
def intialize(relation: Project.all)
@relation = relation
end

def all
relation.select(select_monthly_data_and_dates)
.group('year', 'month', '"projects"."id"')
end

private

def 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

MonthlyUsage.all
MonthlyUsage.new(relation: Project.where(name: 'Stackoverflow')).all