Advanced GroupBy and Aggregation in GraphQL using Nexus (+Prisma)

Question

I have a use case at the moment where I need to build a query that can either return a list of objects or return a list of objects grouped and aggregated for a dashboard. While I could spend a lot of time building a unique -GroupedByObject for each one with its own unique AggregatedObject, I feel like there must be a better way of doing this. I did some research and can see examples of GraphQL schemas that provide a generic AggregationFunction to each class which solves the provided arguments.

In Nexus and code-first this seems to be a much harder ordeal due to the strict typing. I'll show below what my half solution is so far and then outline the problem I'm facing maybe someone knows a better way around this? or maybe I'm missing something

ideal.graphql

type Query {
  devices(..., _groupBy: [String!]): [Device!]
}
type Device {
  _aggregation: AggregationFunction
  ...
}
type AggregationFunction {
  count: Number
  avg(field: String): Aggregation
  max(field: String): Aggregation
  ...
}
type Aggregation {
  field: String
  value: String
}

DeviceType.ts

export const DeviceType = objectType({
  name: "Device",
  definition(t) {
    t.string("device_id");
    t.string("type");
    t.string("version");
    t.field("_aggregation", { type: "AggregationFunction" });
  },
});

export const DeviceQuery = extendType({
  type: "Query",
  definition(t) {
    t.list.field("devices", {
      type: "Device",
      args: {
        limit: intArg(),
        _groupBy: list(nonNull(stringArg())),
      },
      async resolve(_, { limit, _groupBy }, { prisma }) {
        if (_groupBy) {
          const grouped = await prisma.device.groupBy({
            by: _groupBy as Prisma.DeviceScalarFieldEnum[],
            _count: { _all: true },
            _avg: { device_id: true },
            _max: {
              device_id: true,
              type: true,
              version: true,
            },
            _min: {
              device_id: true,
              type: true,
              version: true,
            },
          });
          return grouped.map((g) => ({
            ...g,
            _aggregation: {
              count: g._count._all,
              max: { ...g._max, field: "", value: "" },   // <-- Pass all the fields to the aggregation object❓
              avg: { ...g._avg, field: "", value: "" },   // <-- Pass all the fields to the aggregation object❓
            },
          }));
        }
        const devices = await prisma.device.findMany({
          take: limit || undefined,
        });
        return devices;
      },
    });
  },
});

GenericTypes.ts

export const AggregationFunctionType = objectType({
  name: "AggregationFunction",
  definition(t) {
    t.bigInt("count"); , // <-- Works correctly  
    t.field("max", {
      type: "Aggregated",
      args: { field: stringArg() },  // <-- Select the field from the object and return the value❓ 
      async resolve(par, args) {
        if (args.field) {
          return { field: args.field, value: (par as any).avg[args.field] };  // <-- How to access the full object passed❓
        }
        return null;
      },
    });
    ...
  },
});

export const AggregatedType = objectType({
  name: "Aggregated",
  definition(t) {
    t.nonNull.string("field");
    t.nonNull.string("value");
  },
});

Using Prisma for the database its fairly easy to solve the resolver for when the _groupBy argument is passed, however the issue I'm having is mapping the field to the argument provided to the Aggregated type. I've tried passing through all the values and the breaking all my type safety to access the object from within the resolver but this just ends up returning the empty string I provide in the query.

query Hardware($groupBy: [String!], $field: String) {
  devices(_groupBy: $groupBy) {
    type
    total: _aggregation {
      count
      maxVersion: max(field: $field){
        value
      }
      maxID: max(field: $field){
        value
      }
    }
  }
}

{
  "data": {
    "devices": [
      {
        "type": "LIFE_HIVE",
        "total": {
          "count": 1
          "maxVersion": {
             "value":""
          }
          "maxID": {
             "value":""
          }
        }
      },
      {
        "type": "LIFE_COMB",
        "total": {
          "count": 2
          "maxVersion": {
             "value":""
          }
          "maxID": {
             "value":""
          }
        }
      }
    ]
  }
}

The second issue is that in the Aggregated type I set it to String but in reality the fields could be returning back different types such as number, date, float etc But I'm not sure how to safely pass back the value as a generic For the time being, I can work with the successfully returning _sum value but as I work further into this schema I would need to be able to great some additional aggregation functions between different queries.

If you have any ideas, please let me know as I'm loving making my schema with Nexus and Prisma, but am running into a few edge-cases that might make or break my application towards the end ❤️

Answer 1

TL;DR

I solved this issue by creating a _raw fields which hold a json object with the returned grouped by aggregations. Then in the resolver for each aggregation function I could full out the value based on the passed field argument. While this does expose the raw calculations to the client side, it also allows the client to pick and choose whats on the _raw field since passing data about the fields on the object wasn't possible in pure nexus.

More Details

I ended up posting the same question in the nexus github so there is more information and some other suggestions in the git issue

The main thought I had (without learning how to make nexus plugins) was that I needed to be able to access the JSON result of the prisma .groupBy function:

export const AggregationFunctionType = objectType({
  name: "AggregationFunction",
  definition(t) {
    t.json("_raw");  // <----- store all the results in this _raw field
    t.field("avg", {
      type: "Aggregated",
      args: { field: stringArg() },
      async resolve(par, args) {
        if (args.field) {
          return { field: args.field, value: par._raw._avg[args.field] };  //  <------ access the field inside the _raw using the parent argument
        }
        return null;
      },
    });
    ...
  },
});

export const DeviceFieldEnum = n.enumType({
  name: "DeviceFieldEnum",
  members: Prisma.DeviceScalarFieldEnum,
});

export const DeviceQuery = extendType({
  type: "Query",
  definition(t) {
    t.list.field("devices", {
      type: "Device",
      args: {
        limit: intArg(),
        _groupBy: n.list(n.nonNull(n.arg({ type: "DeviceFieldEnum" }))),
      },
      async resolve(_, { limit, _groupBy }, { prisma }) {
        if (_groupBy) {
          const grouped = await prisma.device.groupBy({
            by: _groupBy,  // <---- Used a FieldEnum value so I know this is valid
            _count: { _all: true },
            _avg: { device_id: true },
            _max: {
              device_id: true,
              type: true,
              version: true,
            },
            _min: {
              device_id: true,
              type: true,
              version: true,
            },
          });
          return grouped.map((g) => ({
            ...g,
            _aggregation: {
              _raw: g  // <----- pass the result of groupBy into the _raw field
            },
          }));
        }
        const devices = await prisma.device.findMany({
          take: limit || undefined,
        });
        return devices;
      },
    });
  },
});

Here it is then possible to use a generic AggregationFunction which can select the fields with the raw using the field argument. The main issue here is that I'm still passing back a string for the value which might not be true for things like count or average. And it is still hard for the client to get autocomplete on the fields that are available on the _raw object. But at least the client can check the _raw beforehand in debugging and see what should be returned ‍♂️

Answer 2

Thanks for the above info, @lloyd-richards. Whilst I'm not answering your answer directly: your work helped me solve my issue, so it might also help someone else in the future.

I was able to do the following:

export const Queries = extendType({
  type: 'Query',
  definition(t) {
    t.list.field('getLikesForEvents', {
      type: 'SiteEventCount',
      args: {
        ids: nonNull(list(arg({ type: 'String' }))),
      },
      authorize: loggedIn,
      resolve: resolvers.getLikesForEvents,
    })
  },
})

export const SiteEventCount = objectType({
  name: 'SiteEventCount',
  definition(t) {
    t.nonNull.string('eventId')
    t.field({ name: '_count', type: '_count' })
  },
})

export const GenericCountByGroup = objectType({
  name: '_count',
  definition(t) {
    t.int('id')
  },
})

which then gave me

  SiteEventCount: { // field return type
    _count: NexusGenRootTypes['_count'] | null; // _count
    eventId: string; // String!
  }

  _count: { // field return type
    id: number | null; // Int
  }

query A($ids: [String!]!) {
  getLikesForEvents(ids: $ids) {
    __typename
    eventId
    _count {
      __typename
      id
    }
  }
}