Aggregations

When aggregations are enabled nestjs-query will expose a new query that enables using the following common aggregations count, avg, sum, min, and max.

note

Only fields decorated with @FilterableField will be exposed in aggregate queries.

All examples will be based on the following TodoItem DTO. All fields except age, because it is not decorated with @FilterableField or @IDField, will be exposed in aggregate queries.

todo-item/dto/todo-item.dto.ts

import { FilterableField, IDField } from '@nestjs-query/query-graphql';import { ObjectType, ID, GraphQLISODateTime, Field } from '@nestjs/graphql';
@ObjectType('TodoItem')export class TodoItemDTO {  @IDField(() => ID)  id!: number;
  @FilterableField()  title!: string;
  @FilterableField({ nullable: true })  description?: string;
  @FilterableField()  completed!: boolean;
  @FilterableField(() => GraphQLISODateTime)  created!: Date;
  @FilterableField(() => GraphQLISODateTime)  updated!: Date;
  @Field()  age!: number;
  @FilterableField()  priority!: number;}

Enabling Aggregate Queries

To enable aggregate queries you can set the enableAggregate option in your resolver

todo-item/todo-item.module.ts

import { NestjsQueryGraphQLModule } from '@nestjs-query/query-graphql';import { NestjsQueryTypeOrmModule } from '@nestjs-query/query-typeorm';import { Module } from '@nestjs/common';import { TodoItemInputDTO } from './dto/todo-item-input.dto';import { TodoItemUpdateDTO } from './dto/todo-item-update.dto';import { TodoItemDTO } from './dto/todo-item.dto';import { TodoItemEntity } from './todo-item.entity';
@Module({  imports: [    NestjsQueryGraphQLModule.forFeature({      imports: [NestjsQueryTypeOrmModule.forFeature([TodoItemEntity])],      resolvers: [        {          DTOClass: TodoItemDTO,          EntityClass: TodoItemEntity,          CreateDTOClass: TodoItemInputDTO,          UpdateDTOClass: TodoItemUpdateDTO,          enableAggregate: true        },      ],    }),  ],})export class TodoItemModule {}

All aggregate queries use the following naming convention ${objectName}Aggregate.

Below is a fragment from the generated schema for TodoItem

info

nestjs-query will only expose number fields for avg and sum.

type Query {  todoItem(id: ID!): TodoItem  todoItems(    paging: CursorPaging = { first: 10 }
    filter: TodoItemFilter = {}
    sorting: [TodoItemSort!] = []  ): TodoItemConnection!  todoItemAggregate(filter: TodoItemAggregateFilter): [TodoItemAggregateResponse!]!}
input TodoItemAggregateFilter {  and: [TodoItemAggregateFilter!]  or: [TodoItemAggregateFilter!]  id: IDFilterComparison  title: StringFieldComparison  description: StringFieldComparison  completed: BooleanFieldComparison  created: DateFieldComparison  updated: DateFieldComparison  priority: NumberFieldComparison}
type TodoItemAggregateResponse {  groupBy: TodoItemAggregateGroupBy  count: TodoItemCountAggregate  sum: TodoItemSumAggregate  avg: TodoItemAvgAggregate  min: TodoItemMinAggregate  max: TodoItemMaxAggregate}
type TodoItemAvgAggregate {  id: Float  priority: Float}
type TodoItemAggregateGroupBy {  id: ID  title: String  description: String  completed: Boolean  created: DateTime  updated: DateTime  priority: Float  createdBy: String  updatedBy: String}
type TodoItemCountAggregate {  id: Int  title: Int  description: Int  completed: Int  created: Int  updated: Int  priority: Int}
type TodoItemMaxAggregate {  id: ID  title: String  description: String  created: DateTime  updated: DateTime  priority: Float}
type TodoItemMinAggregate {  id: ID  title: String  description: String  created: DateTime  updated: DateTime  priority: Float}
type TodoItemSumAggregate {  id: Float  priority: Float}

Examples

Basic

GraphQL

{  todoItemAggregate {    count {      id    }    sum {      id    }    avg {      id    }    min {      id      title      created    }    max {      id      title      created    }  }}

Response

{  "data": {    "todoItemAggregate": [      {        "count": {          "id": 5        },        "sum": {          "id": 15        },        "avg": {          "id": 3        },        "min": {          "id": "1",          "title": "Add Todo Item Resolver",          "created": "2021-03-29T06:51:26.061Z"        },        "max": {          "id": "5",          "title": "How to create item With Sub Tasks",          "created": "2021-03-29T06:51:26.061Z"        }      }    ]  }}

With GroupBy

To group your aggregate queries you can add a groupBy to specify one or more fields to group on.

GraphQL

{  todoItemAggregate {    groupBy {      completed    }    count {      id    }    sum {      id    }    avg {      id    }    min {      id      title      created    }    max {      id      title      created    }  }}

Response

{  "data": {    "todoItemAggregate": [      {        "groupBy": {          "completed": false        },        "count": {          "id": 4        },        "sum": {          "id": 14        },        "avg": {          "id": 3.5        },        "min": {          "id": "2",          "title": "Add Todo Item Resolver",          "created": "2021-03-29T06:51:26.061Z"        },        "max": {          "id": "5",          "title": "How to create item With Sub Tasks",          "created": "2021-03-29T06:51:26.061Z"        }      },      {        "groupBy": {          "completed": true        },        "count": {          "id": 1        },        "sum": {          "id": 1        },        "avg": {          "id": 1        },        "min": {          "id": "1",          "title": "Create Nest App",          "created": "2021-03-29T06:51:26.061Z"        },        "max": {          "id": "1",          "title": "Create Nest App",          "created": "2021-03-29T06:51:26.061Z"        }      }    ]  }}

With Filter

You can also provide a filter to only aggregate on a subset of data.

GraphQL

{  todoItemAggregate(filter: { completed: { is: false } }) {    count {      id    }    min {      id      title      created    }    max {      id      title      created    }  }}

Response

{  "data": {    "todoItemAggregate": [      {        "count": {          "id": 4        },        "min": {          "id": "2",          "title": "Add Todo Item Resolver",          "created": "2021-03-29T06:51:26.061Z"        },        "max": {          "id": "5",          "title": "How to create item With Sub Tasks",          "created": "2021-03-29T06:51:26.061Z"        }      }    ]  }}

When using the count aggregate only non-null fields will be counted.

For example assume description is null for all todo items you will get 0 back.

GraphQL

{  todoItemAggregate(filter: { completed: { is: false } }) {    count {      id      title      description    }  }}

Response

{ "data": {   "todoItemAggregate": [     {       "count": {         "id": 4,         "title": 4,         "description": 0       }     }   ] }}

Aggregating Relations

When using the enableAggregate option any defined many relations will also expose a aggregate query {relationName}Aggregate

Building on the previous example assume TodoItem has a subTasks connection.

The following schema fragment will be created

type TodoItem {  id: ID!  title: String!  description: String  completed: Boolean!  created: DateTime!  updated: DateTime!  age: Float!  priority: Float!  subTasks(    paging: CursorPaging = { first: 10 }
    filter: SubTaskFilter = {}
    sorting: [SubTaskSort!] = []  ): TodoItemSubTasksConnection!  subTasksAggregate(    filter: SubTaskAggregateFilter  ):[ TodoItemSubTasksAggregateResponse!]!}
type TodoItemSubTasksAggregateResponse {  groupBy: TodoItemSubTasksAggregateGroupBy  count: TodoItemSubTasksCountAggregate  sum: TodoItemSubTasksSumAggregate  avg: TodoItemSubTasksAvgAggregate  min: TodoItemSubTasksMinAggregate  max: TodoItemSubTasksMaxAggregate}
type TodoItemSubTasksAggregateGroupBy {  id: ID  title: String  description: String  completed: Boolean  created: DateTime  updated: DateTime  todoItemId: String  createdBy: String  updatedBy: String}
type TodoItemSubTasksAvgAggregate {  id: Float}
type TodoItemSubTasksCountAggregate {  id: Int  title: Int  description: Int  completed: Int  created: Int  updated: Int  todoItemId: Int}
type TodoItemSubTasksMaxAggregate {  id: ID  title: String  description: String  created: DateTime  updated: DateTime  todoItemId: String}
type TodoItemSubTasksMinAggregate {  id: ID  title: String  description: String  created: DateTime  updated: DateTime  todoItemId: String}
type TodoItemSubTasksSumAggregate {  id: Float}

Examples

Basic

In this example we'll aggregate on all related subTasks.

GraphQL

{  todoItem(id: 5) {    subTasksAggregate {      count {        id      }      sum {        id      }      avg {        id      }      min {        id        title      }      max {        id        title      }    }  }}

Response

{ "data": {   "todoItem": {     "subTasksAggregate": [       {         "count": {           "id": 3         },         "sum": {           "id": 42         },         "avg": {           "id": 14         },         "min": {           "id": "13",           "title": "How to create item With Sub Tasks - Sub Task 1"         },         "max": {           "id": "15",           "title": "How to create item With Sub Tasks - Sub Task 3"         }       }     ]   } }}

With GroupBy

In this example we'll aggregate on all related subTasks and group by completed.

GraphQL

{  todoItem(id: 5) {    subTasksAggregate {      groupBy {        completed      }      count {        id      }      sum {        id      }      avg {        id      }      min {        id        title      }      max {        id        title      }    }  }}

Response

{ "data": {   "todoItem": {     "subTasksAggregate": [       {         "groupBy": {           "completed": false         },         "count": {           "id": 2         },         "sum": {           "id": 29         },         "avg": {           "id": 14.5         },         "min": {           "id": "14",           "title": "How to create item With Sub Tasks - Sub Task 2"         },         "max": {           "id": "15",           "title": "How to create item With Sub Tasks - Sub Task 3"         }       },       {         "groupBy": {           "completed": true         },         "count": {           "id": 1         },         "sum": {           "id": 13         },         "avg": {           "id": 13         },         "min": {           "id": "13",           "title": "How to create item With Sub Tasks - Sub Task 1"         },         "max": {           "id": "13",           "title": "How to create item With Sub Tasks - Sub Task 1"         }       }     ]   } }}

With Filter

This example will aggregate all related subTasks that are not completed.

GraphQL

{  todoItem(id: 5) {    subTasksAggregate(filter: { completed: { is: false } }) {      count {        id      }      min {        id        title      }      max {        id        title      }    }  }}

Response

{ "data": {   "todoItem": {     "subTasksAggregate": [       {         "count": {           "id": 2         },         "min": {           "id": "14",           "title": "How to create item With Sub Tasks - Sub Task 2"         },         "max": {           "id": "15",           "title": "How to create item With Sub Tasks - Sub Task 3"         }       }     ]   } }}

Advanced

Enabling Aggregates Only For Root

When using the enableAggregate option it will enable aggregates on the root type as well as all relations. If you only want to expose aggregate functionality on the root type you can specify the aggregate option.

import { NestjsQueryGraphQLModule } from '@nestjs-query/query-graphql';import { NestjsQueryTypeOrmModule } from '@nestjs-query/query-typeorm';import { Module } from '@nestjs/common';import { TodoItemInputDTO } from './dto/todo-item-input.dto';import { TodoItemUpdateDTO } from './dto/todo-item-update.dto';import { TodoItemDTO } from './dto/todo-item.dto';import { TodoItemEntity } from './todo-item.entity';
@Module({  imports: [    NestjsQueryGraphQLModule.forFeature({      imports: [NestjsQueryTypeOrmModule.forFeature([TodoItemEntity])],      resolvers: [        {          DTOClass: TodoItemDTO,          EntityClass: TodoItemEntity,          CreateDTOClass: TodoItemInputDTO,          UpdateDTOClass: TodoItemUpdateDTO,          aggregate: { enabled: true }        },      ],    }),  ],})export class TodoItemModule {}

Disable Aggregate for Single Relation

You can also selectively disable aggregates on an individual relation by specifying the enableAggregate option when defining the relation.

import { FilterableField, FilterableConnection, IDField } from '@nestjs-query/query-graphql';import { ObjectType, ID, GraphQLISODateTime, Field } from '@nestjs/graphql';import { AuthGuard } from '../../auth.guard';import { SubTaskDTO } from '../../sub-task/dto/sub-task.dto';
@ObjectType('TodoItem')@FilterableConnection('subTasks', () => SubTaskDTO, { enableAggregate: false })export class TodoItemDTO {  @IDField(() => ID)  id!: number;
  @FilterableField()  title!: string;
  @FilterableField({ nullable: true })  description?: string;
  @FilterableField()  completed!: boolean;
  @FilterableField(() => GraphQLISODateTime)  created!: Date;
  @FilterableField(() => GraphQLISODateTime)  updated!: Date;
  @Field()  age!: number;
  @FilterableField()  priority!: number;}