Using Postgraphile in nodeJS, how to enable aggregate max of date field?

Question

I am using postgraphile in NodeJS for graphql API based on Postgresql database. I need to get max(date_field), but postgraphile does not provide that option by default.

How can I enable aggregation of max on a date field?

I want something as follows. But inspection_Date field is not available under max

query Query {
  allRooms {
    aggregates {
      max {
        inspection_date
      }
    }
  }
}

Answer 1

Using a slightly modified version of the approach outlined in the defining your own aggregates section of the pg-aggregates readme, you can create a new graphile plugin that uses a hook to modify the existing aggregate specs for "min" and "max" to use a different isSuitableType function that includes temporal types as well as numeric types:

import type { Plugin } from "graphile-build";
import type { AggregateSpec } from "@graphile/pg-aggregates/dist/interfaces";
import type { PgType } from "graphile-build-pg";

const addTemporalAggregatesPlugin: Plugin = (builder) => {
  builder.hook(
    "build",
    (build) => {
      const { pgAggregateSpecs } = build;

      const isNumberLikeOrTemporal = (pgType: PgType): boolean =>
        pgType.category === "N" || pgType.category === "D";

      // modify isSuitableType for max and min aggregates
      // to include temporal types see: https://www.postgresql.org/docs/current/catalog-pg-type.html
      const specs = (pgAggregateSpecs as AggregateSpec[] | undefined)?.map(
        (spec) => {
          if (spec.id === "min" || spec.id === "max") {
            return {
              ...spec,
              isSuitableType: isNumberLikeOrTemporal,
            };
          }
          return spec;
        }
      );

      if (!specs) {
        throw Error(
          "Please that the pg-aggregates plugin is present and that AddTemporalAggregatesPlugin is appended AFTER it!"
        );
      }

      const newBuild = build.extend(build, {});
      // add modified aggregate specs to the build
      newBuild.pgAggregateSpecs = specs;
      return newBuild;
    },
    ["AddTemporalAggregatesPlugin"],
    // ensure this hook fires before other hooks in the pg-aggregates plugin
    // that may depend on the "pgAggregatesSpecs" extension.
    ["AddGroupByAggregateEnumsPlugin"],
    []
  );
};

export default addTemporalAggregatesPlugin;

Then just append this new plugin after the pg-aggregates plugin:

postgraphile(pool, "my_schema", {
    pluginHook,
    appendPlugins: [
      PgAggregatesPlugin,
      AddTemporalAggregatesPlugin,
    ],
    // ...
})