在nodeJS中使用Postgraphile，如何启用日期字段的聚合最大值？

Question

基于Postgresql数据库，我在NodeJS中使用postgraphile作为graphql。我需要获取max(date_field)，但是postgraphile在默认情况下不提供该选项。

如何在日期字段上启用max聚合？

我想要下面的东西。但是inspection_Date字段在max下不可用。

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

Answer 1

使用pg聚合自述的定义您自己的聚合部分中概述的方法的稍微修改的版本，您可以创建一个新的图形文件插件，该插件使用钩子来修改现有的"min“和"max”聚合规范，以使用包含时态类型和数值类型的不同isSuitableType函数：

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;

然后，只需在pg-聚合插件之后追加这个新插件：

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