Add relation aggregates to aggregates about pg-aggregates HOT 6 CLOSED

As a comment on a long closed issue, this will get lost. I don't plan to address your concerns any time soon (though you could, or you could sponsor us to), but if you'd like them to be tracked you should open a new issue.

from pg-aggregates.

{
  allAccounts {
    aggregates {
      sum {
        price }}}}

^ This is quite clear; something like: select sum(accounts.price) from accounts

{
  allAccounts {
    aggregates {
      sum {
        usersByAccountId {
          price } } } } }

^ This is less clear, is this a single value across all accounts:

select sum(users.price)
from accounts
inner join users
on (accounts.id = users.account_id)

?

Or is it grouped by the account ID, so for each account id you get a value:

select accounts.id, sum(users.price)
from accounts
inner join users
on (accounts.id = users.account_id)
group by accounts.id

?

from pg-aggregates.

[semi-automated message] Hi, there has been no activity in this issue for a while so I'm closing it to keep the issues/pull requests manageable. If this is still an issue, please re-open with additional details.

from pg-aggregates.

Hi benjie! Thanks for another great addition to the postgraphile ecosystem!

The second example above is the critical use case; the desire is to group and aggregate from two different tables. I'd go so far as to say this is a universal desire since it is common in database design to store values in a separate table from metadata (so that the metadata need not be repeated for every value).

I'm sure there are several possibilities for the query shape, with various tradeoffs. One possibility is the groupBy argument accepting something akin to what's used in postgraphile-plugin-connection-filter in place of the enum that's used currently.

I'd be curious to hear whether you think this might ever be in scope. I've been poking around the source to see if it might be possible.

from pg-aggregates.

I think you can do that by adding a custom grouping which is a subquery to fetch the related data.

from pg-aggregates.

I'm trying to do exactly this, but currently there are some obstacles:

• isSuitableType is a check based only on a type, while it should be type + column, so you can e.g. add grouping only for id:

  select sum(users.price)
  from users
  group by (select accounts.id from accounts where accounts.id = users.account_id)

  Currently isSuitableType makes this grouping available for all number-like columns (if you enable it for account_id), which is meaningless.
• pgAggregateGroupBySpecs enables aggregation only for single column, i.e. sqlFrag = __local_123__.<column>. If in prev. example you want to join by more than one column, you can't (I know a workaround with pg settings, but it is super ugly and verbose)
• In general, "group by arbitrary expression" would be great to have. Currently it's doable, but there are a lot of ugliness

from pg-aggregates.