Aggregations

When aggregations are enabled will expose a new query that enables using the following common aggregations , , , , and .

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 '@ptc-org/nestjs-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 '@ptc-org/nestjs-query-graphql'
import { NestjsQueryTypeOrmModule } from '@ptc-org/nestjs-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

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(by: GroupBy = DAY): DateTime
  updated(by: GroupBy = DAY): 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 '@ptc-org/nestjs-query-graphql'
import { NestjsQueryTypeOrmModule } from '@ptc-org/nestjs-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 '@ptc-org/nestjs-query-graphql'
import { ObjectType, ID, GraphQLISODateTime, Field } from '@nestjs/graphql'
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
}

Group aggregate on DAY/WEEK/MONTH​

This functionality only works for typeorm!

You can change how datetime fields are grouped by passing WEEK, MONTH or YEAR to the date fields by.

{
  todoItemAggregate {
    groupBy {
      created(by: MONTH)
    }
    count {
      id
    }
    sum {
      id
    }
  }
}

Edit this page