Pagination

VitNode provide a pagination system using cursor pagination to help you retrieve data from the database.

⚠️

VitNode doesn't support the offset pagination.

Here is an example of how to use the pagination system based on blog_categories table.

Data Transfer Object (DTO)

We will create a DTO to create arguments and return values for the query.

Arguments

File: show/dto/show.args.ts

Arguments used as container for fields to create query in GraphQL schema.

import { ArgsType, Field, Int } from "@nestjs/graphql";
 
@ArgsType()
export class ShowBlogCategoriesArgs {
  @Field(() => Int, { nullable: true })
  cursor: number | null;
 
  @Field(() => Int, { nullable: true })
  first: number | null;
 
  @Field(() => Int, { nullable: true })
  last: number | null;
}

Object

File: show/dto/show.obj.ts

Object used as container for fields to create return values query in GraphQL schema.

import { Field, Int, ObjectType } from "@nestjs/graphql";
 
import { PageInfo } from "@/types/database/pagination.type";
import { TextLanguage } from "@/types/database/text-language.type";
 
@ObjectType()
export class ShowBlogCategories {
  @Field(() => Int)
  id: number;
 
  @Field(() => [TextLanguage])
  name: TextLanguage[];
 
  @Field(() => [TextLanguage], { nullable: true })
  description: TextLanguage[] | null;
}
 
@ObjectType()
export class ShowBlogCategoriesObj {
  @Field(() => [ShowBlogCategories])
  edges: ShowBlogCategories[];
 
  @Field(() => PageInfo)
  pageInfo: PageInfo;
}

ShowBlogCategories object is used to create a list of blog_categories objects.

Query GraphQL

Service

File: show/show.service.ts

Inside service file we will create a show method that will return a ShowBlogCategoriesObj object.

import { Injectable } from "@nestjs/common";
 
import { ShowBlogCategoriesArgs } from "./dto/show.args";
import { ShowBlogCategoriesObj } from "./dto/show.obj";
 
import { DatabaseService } from "@/plugins/database/database.service";
 
@Injectable()
export class ShowBlogCategoriesService {
  constructor(private databaseService: DatabaseService) {}
 
  async show({
    cursor,
    first,
    last
  }: ShowBlogCategoriesArgs): Promise<ShowBlogCategoriesObj> {}
}

Initial values for pagination

We will use inputPaginationCursor() to create initial values for pagination.

import { Injectable } from "@nestjs/common";
 
import { ShowBlogCategoriesArgs } from "./dto/show.args";
import { ShowBlogCategoriesObj } from "./dto/show.obj";
 
import { DatabaseService } from "@/plugins/database/database.service";
import { inputPaginationCursor } from "@/functions/database/pagination";
import { blog_categories } from "../../admin/database/schema/categories";
import { SortDirectionEnum } from "@/types/database/sortDirection.type";
 
@Injectable()
export class ShowBlogCategoriesService {
  constructor(private databaseService: DatabaseService) {}
 
  async show({
    cursor,
    first,
    last
  }: ShowBlogCategoriesArgs): Promise<ShowBlogCategoriesObj> {
    const pagination = await inputPaginationCursor({
      cursor,
      database: blog_categories,
      databaseService: this.databaseService,
      first,
      last,
      primaryCursor: { order: "ASC", key: "id", schema: blog_categories.id },
      defaultSortBy: {
        direction: SortDirectionEnum.asc,
        column: "position"
      }
    });
  }
}

Query from database

We will use findMany() method to get the data from the database with with argument to get the related data.

@Injectable()
export class ShowBlogCategoriesService {
  constructor(private databaseService: DatabaseService) {}
 
  async show({
    cursor,
    first,
    last
  }: ShowBlogCategoriesArgs): Promise<ShowBlogCategoriesObj> {
    const pagination = await inputPaginationCursor({
      cursor,
      database: blog_categories,
      databaseService: this.databaseService,
      first,
      last,
      primaryCursor: { order: "ASC", key: "id", schema: blog_categories.id },
      defaultSortBy: {
        direction: SortDirectionEnum.asc,
        column: "position"
      }
    });
 
    const edges = await this.databaseService.db.query.blog_categories.findMany({
      ...pagination,
      with: {
        title: true,
        description: true
      }
    });
  }
}

Where argument

If you want to use where argument you can pass it to the findMany() method using and() method from drizzle-orm.

import { Injectable } from "@nestjs/common";
import { and, lte } from "drizzle-orm";
 
import { ShowBlogCategoriesArgs } from "./dto/show.args";
import { ShowBlogCategoriesObj } from "./dto/show.obj";
 
import { DatabaseService } from "@/plugins/database/database.service";
import { inputPaginationCursor } from "@/functions/database/pagination";
import { blog_categories } from "../../admin/database/schema/categories";
import { SortDirectionEnum } from "@/types/database/sortDirection.type";
 
@Injectable()
export class ShowBlogCategoriesService {
  constructor(private databaseService: DatabaseService) {}
 
  async show({
    cursor,
    first,
    last
  }: ShowBlogCategoriesArgs): Promise<ShowBlogCategoriesObj> {
    const pagination = await inputPaginationCursor({
      cursor,
      database: blog_categories,
      databaseService: this.databaseService,
      first,
      last,
      primaryCursor: { order: "ASC", key: "id", schema: blog_categories.id },
      defaultSortBy: {
        direction: SortDirectionEnum.asc,
        column: "position"
      }
    });
 
    const where = lte(blog_categories.created, new Date());
 
    const edges = await this.databaseService.db.query.blog_categories.findMany({
      ...pagination,
      where: and(pagination.where, where),
      with: {
        title: true,
        description: true
      }
    });
  }
}

Return values

We will use outputPagination() to create return values for the query. Remember to create a query totalCount to get the total count of the query.

import { Injectable } from "@nestjs/common";
import { count } from "drizzle-orm";
 
import { ShowBlogCategoriesArgs } from "./dto/show.args";
import { ShowBlogCategoriesObj } from "./dto/show.obj";
 
import { DatabaseService } from "@/plugins/database/database.service";
import {
  inputPaginationCursor,
  outputPagination
} from "@/functions/database/pagination";
import { blog_categories } from "../../admin/database/schema/categories";
import { SortDirectionEnum } from "@/types/database/sortDirection.type";
 
@Injectable()
export class ShowBlogCategoriesService {
  constructor(private databaseService: DatabaseService) {}
 
  async show({
    cursor,
    first,
    last
  }: ShowBlogCategoriesArgs): Promise<ShowBlogCategoriesObj> {
    const pagination = await inputPaginationCursor({
      cursor,
      database: blog_categories,
      databaseService: this.databaseService,
      first,
      last,
      primaryCursor: { order: "ASC", key: "id", schema: blog_categories.id },
      defaultSortBy: {
        direction: SortDirectionEnum.asc,
        column: "position"
      }
    });
 
    const edges = await this.databaseService.db.query.blog_categories.findMany({
      ...pagination,
      with: {
        title: true,
        description: true
      }
    });
 
    const totalCount = await this.databaseService.db
      .select({ count: count() })
      .from(blog_categories);
 
    return outputPagination({ edges, totalCount, first, cursor, last });
  }
}

If you have where argument you need to pass it to the count() method.

const totalCount = await this.databaseService.db
  .select({ count: count() })
  .from(blog_categories)
  .where(where);

Modyfy return values

If you want to modify the return values you can use map() method to modify the return values.

return outputPagination({
  edges: edges.map(edge => ({
    ...edge,
    name: edge.title,
    description: edge.description
  })),
  totalCount,
  first,
  cursor,
  last
});

Output

{
  "edges": [],
  "pageInfo": {
    "hasNextPage": false,
    "startCursor": "",
    "endCursor": "",
    "totalCount": 0,
    "count": 0
  }
}

Overview Sorting