TransWikia.com

Rails model for aggregate data instead of a real table

Code Review Asked by KNejad on November 28, 2021

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.

One Answer

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
  
  attr_reader :relation

  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

https://medium.com/selleo/essential-rubyonrails-patterns-part-2-query-objects-4b253f4f4539 https://medium.flatstack.com/query-object-in-ruby-on-rails-56ea434365f0

Answered by Christian Bruckmayer on November 28, 2021

Add your own answers!

Ask a Question

Get help from others!

© 2024 TransWikia.com. All rights reserved. Sites we Love: PCI Database, UKBizDB, Menu Kuliner, Sharing RPP