How to aggregate for multiple levels of embedded resources?

[stefan-girlich]

I am using Supabase and PostgREST to aggregate sums by a nested property.

Resource hierarchy

• locations
  • have many output_fractions of which each
    • has weight
    • belongs to a single batch (batches) with a project_type
    • has many shipments with another column weight

In short: locations have output_fractions which have one of batches and many shipments

I want to query data for locations and aggregate nested weight sums:

Case 1: Aggregate over 1 level

I want to aggregate weight for output_fractions by batches.project_type which works fine:

output_fractions(
    weight.sum(),
    batches!inner(project_type)
)

Case 2: Aggregating over more levels

I want to aggregate weight for shipments by batches.project_type.
I wonder if I could modify this code to achieve the desired results:

fractions2:output_fractions!inner(
  shipments!inner(
    weight.sum(),
    ...output_fractions!inner(batches!inner(project_type))
  )
)

Expected: fractions2 contains one item per project_type
Actual: fractions2 contains one item per output_fractions item

Ideally, I could reference shipments directly as an embedded resource, but this is not possible since it is does not relate directly to locations, but only indirectly via output_fractions.
Is there an idiomatic way to achieve this? If not, how would tackle this problem?

Thanks!

[laurenceisla]

Is there an idiomatic way to achieve this? If not, how would tackle this problem?

Since this query has nested aggregates/group by mixed in between "has-many" and "has-one" relationships, it is too complex for a PostgREST query to resolve it. I'd go with a VIEW or FUNCTION instead, that way you can build the query according to your needs.