Aggregate Table Computed Fields

[gjacobrobertson]

Is your proposal related to a problem?

I am having difficulty aggregating over indirect relationships, Take for example the following schema:

CREATE TABLE authors (
  id uuid PRIMARY KEY,
  name text
);

CREATE TABLE articles (
  id uuid PRIMARY KEY,
  author_id uuid NOT NULL REFERENCES authors(id),
  title text not null,
  body text not null
);

CREATE TABLE article_ratings (
  id uuid PRIMARY KEY,
  article_id uuid NOT NULL REFERENCES articles(id),
  rating int
)

with each table tracked and foreign key exposed as an object and corresponding array relationship.

The generated GQL schema can aggregate ratings by article, e.g.

query {
  articles {
    title
    article_ratings_aggregate {
      aggregate {
        count
        avg { rating }
      }
    }
  }
}

but as is, there is no way to query of the form "For each author, return their average rating across all articles they have written"

Describe the solution you'd like

It would be useful if _aggregate fields were generated corresponding to Table Computed Fields, since Table Computed Fields can be used to express denormalized relationships, e.g.

CREATE FUNCTION author_ratings(author_row authors)
RETURNS SETOF article_ratings
LANGUAGE sql STABLE
AS $$
  SELECT article_ratings.*
  FROM article_ratings
  INNER JOIN articles ON article_rating.article_id = articles.id
  WHERE articles.author_id = author_row.id
$$

And create a computed field authors.article_ratings from that function.

The desired aggregation could be expressed in GQL as

query {
  authors {
    article_ratings_aggregate {
      aggregate {
        count
        avg { rating }
      }
    }
  }
}

Given that Custom Functions returning SETOF tables get exposed root-level aggregate fields (https://hasura.io/docs/latest/graphql/core/databases/postgres/schema/custom-functions/#aggregations-on-custom-functions), and Table Computed Fields are otherwise exposed similarly to array relationships (they accept where, limit, order_by, etc clauses), it seems reasonable that Table Computed Fields could be aggregated.

Describe alternatives you've considered

I have considered an alternative approach of using views to express denormalized relationships, e.g.

CREATE VIEW article_ratings_with_author AS (
   SELECT
     article_ratings.*,
     articles.author_id AS author_id
  FROM article_ratings
  INNER JOIN articles ON article_ratings.article_id = articles.id
)

Where one could track a author.article_ratings field as a plain array relationship. This seems feasible but adds complexity to schema definition and maintenance. It's great to be able to express denormalized relationships as Table Custom Fields without impact on table/view definition.

If the feature is approved, would you be willing to submit a PR?

No, at least not in the near future.

[osdiab]

I have another circumstance, where we have a computed amount field on a financial table, and we allow filtering and paginating the table so only some of the data is shown on screen at once. We want to show the "total" values for that column in a footer (which can ultimately differ based on your permissions), but even though we display this computed amount value in the cells with a simple GQL query, we can't do the same aggregation to sum them directly through GQL.

[osdiab]

the main "workaround" i can come up with at the moment is to make an API endpoint that actually fetches the full dataset and then aggregates it in the backend, sending just the data we want to the frontend. But obviously that's pretty ugly.

[osdiab]

i guess what I'm describing is somewhat different though, it's just that computed fields aren't showing up as a column to aggregate. i'll search more for the proper issue to piggy back on.

[osdiab]

made a separate issue #9646