Using a calculation in grouping for an aggregate

Ash Forum Ash Questions / Help
,
1

Question
Is it possible to use a calculation in the grouping for an aggregate?

Problem
I am attempting to group by a calculated value within an aggregate. Specifically calculating the sum of a field value for each type_name by calendar date.

I have attempted to define the aggregate within the resource as well as within the query and am unable to get any further with this.

Example:

Example resource:

defmodule Example.TimeSeriesResource do
  
  use Ash.Resource,
    data_layer: AshPostgres.DataLayer

  postgres do
    table "type_values"
    repo Example.Repo
  end
  
  attributes do
    integer_primary_key :id

    attribute :date_time, :datetime do
      allow_nil? false
    end

    attribute :type_name, :string do
      allow_nil? false
    end

    attribute :value, :float do
      allow_nil? false
    end
  end

  calculations do
    calculate :calendar_date, :date, expr(type(:date_time, :date))
  end
end

If I define an aggregate within the resource, such as:

aggregates do
  sum :total_value, [:calendar_date, :type_name], :value
end

this fails to compile, returning the following explanation in the compilation error:

aggregates -> total_value:
  relationship referenced in aggregate `Example.TimeSeriesResource.calendar_date` does not exist

When I have attempted to use a custom aggregate in the query, such as:

query = Example.TimeSeriesResource
  |> Ash.Query.for_read(:read)
  |> Ash.Query.load(:calendar_date)
  |> Ash.Query.aggregate(:value, :sum, [:calendar_date, :type_name])

I get the following error:

** (ArgumentError) `nil` is not a Spark DSL module.

    nil.entities([:actions])
    (spark 1.1.54) lib/spark/dsl/extension.ex:171: Spark.Dsl.Extension.get_entities/2
    (ash 2.18.2) lib/ash/resource/info.ex:553: Ash.Resource.Info.primary_action/2
    (ash 2.18.2) lib/ash/resource/info.ex:539: Ash.Resource.Info.primary_action!/2
    (ash 2.18.2) lib/ash/query/query.ex:2030: Ash.Query.aggregate/14

where the stacktrace shows an attempt to call Ash.Query.aggregate/14 rather than the expected Ash.Query.aggregate/4.

2

Ash does not support grouping aggregates currently. Aggregates are “per-record”. To do grouping you’ll need to use Ecto. Example.TimeSeriesResource is an ecto schema automatically, so you can directly query it. Additionally, you can turn an ash query into the relevant ecto query:

Resource
|> Ash.Query.filter(...)
|> Ash.Query.data_layer_query()
|> case do
  {:ok, query} -> from row in query, group_by: ..., select: max(....)
end
1 Like
3

Thanks @zachdaniel - I’ll give that a go