Aggregations in embedded resources

Ash Forum Ash Questions / Help
, ,
1

Hello,

I’d like to know what is the Ash way of doing some aggregations on embedded resources.

I have these resources:

  • Service
    • name. string
    • color: string
    • stages: {:array, Stage} ← This is the embedded resource
  • Stage
    • name. string
    • duration: integer

I need to calculate the total duration of the service by adding all the durations of the stages.

I have it working using a calculation:

  calculations do
    calculate :stage_count, :integer, expr(fragment("array_length(stages, 1)"))

    calculate :stage_duration,
              :integer,
              expr(fragment("SELECT SUM((s->>'duration')::int) FROM unnest(stages) AS s"))
  end

I wonder if this belongs to a calculation or a preparation.

Just for fun, I tried to achieve the same using a preparation based on this SQL query:

SELECT
  id, name, sum((stage->>'duration')::int) as duration
FROM 
  services,
  LATERAL unnest(stages) as stage
GROUP BY id;

But I wonder if this is even possible using using Ash.Query.build. I think I can’t add the LATERALin the FROM.

To sum up:

  1. For this specific case, is better using calculations or preparations?
  2. Is it possible to generate a SQL query like the one shown above using Ash?

Thanks!

2

The calculation way is the way :slight_smile: I’m not sure I understand about putting it in a preparation though.

1 Like
3

Ah I get your question. I it is not possible to do the query you showed with Ash. We may add first class tooling for aggregating JSON that may use similar tools but likely would not expose those directly. I’d be curious to hear how the lateral method stacks up against your calculations performance wise though.

1 Like
4

I’d be curious to hear how the lateral method stacks up against your calculations performance wise though.

Me too :slight_smile:

I don’t have data about performance, I could investigate in the future.

it is not possible to do the query you showed with Ash

Is it possible to do it with raw SQL somehow?

5

The primary issue is that the way the query builder works in Ash, it is in charge of your joining and the cardinality of your results. Tacking on a LATERAL like that requires that you GROUP BY which adds significant expense to any computed properties and joins that we add that can’t be automatically inferred to be unique by the primary key the way that name and id can in your example. More than expense, it just wouldn’t work to build queries that way in the generic.

You could always define a view in postgres.

1 Like