Postgraphile: Feature request: `groupBy` Queries support

[crubier]

How complicated would it be to add a groupBy feature on connections?
I guess pretty complicated, but the value would be immense...
This is not something we can do on the client-side for large lists

Strapi seems to do it with this API

postsConnection {
  groupBy {
    published: {
      key
      connection {
        aggregate {
          avg {
            nb_likes
          }
        }
      }
    }
  }
}

https://strapi.io/blog/release-v3-alpha-14-graphql-aggregations-customization-security

[Queatz]

Looking for this as well.

[crubier]

For reference:

• Hasura https://hasura.io/docs/1.0/graphql/core/queries/aggregation-queries.html#aggregate-fields
• Prisma https://www.prisma.io/docs/concepts/components/prisma-client/aggregation-grouping-summarizing
• Strapi https://strapi.io/blog/release-v3-alpha-14-graphql-aggregations-customization-security

i'd love this feature :-)

[crubier]

Wow good synchronization, I saw things moving there https://github.com/graphile/pg-aggregates/ !

[benjie]

Yeah this is done now; min/max/avg/stddev/variance/distinctCount/sum, groupBy multiple things, having filters, order by aggregates, add your own aggregates, add your own groupings, group derivatives (e.g. group by timestamp truncated to hour/day) with add your own too. Would love to know how you all get on with the above as I want to move it out of experimental status.

[benjie]

Thanks for the links @crubier; I think ours are more powerful than all of the above 🙌

I should have mentioned above that the aggregates work over both regular columns and computed column functions, and you can also group by computed column functions too 🚀

[bbigras]

Is my "own groupings" the way to go for the equivalent of ARRAY_AGG?

[benjie]

It'd be an aggregate rather than a grouping for array_agg; see: https://github.com/graphile/pg-aggregates/blob/cceccbb1884c09d8bce308ab79a947f8ef5257a8/src/AggregateSpecsPlugin.ts#L46 your pgTypeAndModifierModifier would have to specify a PG array type for the relevant incoming type. Please file questions about @graphile/pg-aggregates on the pg-aggregates repo.