Feature: `@pgAggregate` directive for specifying custom aggregations

[marshall007]

As a solution to https://github.com/graphile/postgraphile/issues/685, it would be nice if we could leverage makeExtendSchemaPlugin to specify custom aggregations. This would be very similar to existing usage of the @pgQuery directive.

For example:

extend type PetsConnection {
  dogCount: Int @pgAggregate(
    withQueryBuilder: ${embed((queryBuilder) => queryBuilder
      .select(sql.fragment`count(1)`)
      .where(sql.fragment`${queryBuilder.getTableAlias()}.type = 'dog'`),
    )}
  )
  catCount: Int @pgAggregate(
    withQueryBuilder: ${embed((queryBuilder) => queryBuilder
      .select(sql.fragment`count(1)`)
      .where(sql.fragment`${queryBuilder.getTableAlias()}.type = 'cat'`),
    )}
  )
}

[marshall007]

As couple "nice-to-haves" on top of this:

• [ ] QueryBuilder.where(...) to optionally accept a plain object (e.g. .where({ type: 'cat' }), automatically prefixing the table alias and wrapping key/value pairs in sql.identifier/value respectively
• [ ] leverage ^ to expose shorthand helpers as an alternative to withQueryBuilder
  • @pgAggregate(count: { type: 'cat' })
  • @pgAggregate(sum: 'quantity')

[benjie]

👍 I prefer @pgCount(where: {type: "cat"}) and @pgSum(column: "quantity"); effectively @pgAggregate will be the do-all that powers these simpler helpers.

[benjie]

Better to use pg-aggregates where possible; or the new aggregation setup that V5 enables.