Dimensions in filters (aggregation on aggregation)

[DoctorKatz]

Hi, Bogdan!

Thank you for this gem; it's awesome.

In one of my use cases, I added dimensions to filters, similar to how it’s done in the active_reporting gem. When using CTE (Common Table Expressions) tables, these dimensions provide an opportunity to perform aggregation on aggregation, similar to ROLAP.

Here’s a useful case, for example:

1. I'm building an accounting service and have a Product model. The Product has many Project models, and each Project has many ResourceConsumption models, which track resources like CPU, RAM, and ROM, recorded hourly.
2. I need to calculate the average consumption per day for the projects associated with a product, and then sum these averages to find the product's average daily consumption.

Sometimes, I also need to calculate the reverse case: summing all resource consumptions for each day and then taking an average.

To achieve this, I've added grouping by dimension in the filters.

module Datagrid
  module Filters
    module InstanceMethods
      def apply_filters(current_scope, filters)
        group_by_dimension(filters).inject(current_scope) do |result, (_, filters_grouped)|
          apply_filters_group(result, filters_grouped)
        end
      end

      def apply_filters_group(result_scope, filters)
        filters.inject(result_scope) do |result, filter|
          filter.apply(self, result, filter_value(filter))
        end
      end

      def group_by_dimension(filters)
        filters.group_by { _1.options[:dimension_level] }
      end
    end
  end
end

In model I've added scopes like this

class ConsumptionResource
  scope :per_day, -> { group(:record_date_id) }
  scope :per_consumer, -> { group(:product_id) }
  scope :cte_with_table_name, -> { klass.with("#{klass.table_name}": self) }
  scope :avg, -> { select("product_id, avg(cpu) as cpu, avg(ram) as ram") }
  scope :total_sum, -> { select("sum(cpu) as cpu, sum(ram) as ram") }
end

And datagrid filters, they look like this:

filter(:total_sum, :boolean, dimension_level: 2) do |_, scope|
  scope.cte_with_table_name.total_sum
end

filter(:avg_per_consumer, :boolean) do |_, scope|
  scope.avg.per_consumer
end

filter(:avg_per_days, :boolean) do |_, scope|
  scope.avg.per_day
end

So, I implemented this for Datagrid v1, but I have some time now, and I can rewrite it, write tests and make a pull request for Datagrid v2 if you're interested in this feature.

[bogdan]

I understood the code correctly, you are just controlling the order of filters when applied to scope, correct?

Datagrid currently support before and after options to control filters order, however I totally understand that it is not very flexible. I never used them in production, but can assume that having priority option can be more consistent in the long run.

Other thing is that currently datagrid doesn't differentiate the order of filters in the UI and the order of filters when applied to scope.
Do you need that flexibility?

[DoctorKatz]

Hmm, I think this options can help me in my case. I am going to check.

My plan is to make a query that looks like this, where I wrap first SELECT statement in CTE table and than use second SELECT statement on it:

WITH "lxc_consumptions" AS (SELECT project_id, avg(lxc_consumptions.used_cpu) as used_cpu, 
                                               avg(lxc_capacities.trdc_cpu) as trdc_cpu,
 FROM "lxc_consumptions" INNER JOIN "record_dates" ON "record_dates"."id" = "lxc_consumptions"."record_date_id" 
 INNER JOIN "lxc_capacities" ON "lxc_capacities"."record_date_id" = "record_dates"."id" 
 WHERE "lxc_consumptions"."recorded_at" BETWEEN '2025-02-26 21:00:00' AND '2025-02-27 20:59:59.999999' 
 AND "lxc_consumptions"."project_id" = 504 GROUP BY "lxc_consumptions"."project_id")
 SELECT sum(lxc_consumptions.used_cpu) as used_cpu
               sum(trdc_cpu) as trdc_cpu

and options after and before look much more consistent.
So, I will try this approach and provide feedback after a few days
Thank's!