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. We will use the core_members table as an example.

Data Transfer Object (DTO)

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

Arguments

File: show/dto/show-core_members.args.ts

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

import { ArgsType, Field, Int } from "@nestjs/graphql";
 
@ArgsType()
export class ShowCoreMembersArgs {
  @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-core_members.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";
 
@ObjectType()
export class ShowCoreMembersObj {
  @Field(() => [ShowCoreMembers])
  edges: ShowCoreMembers[];
 
  @Field(() => PageInfo)
  pageInfo: PageInfo;
}

ShowCoreMembers object is used to create a list of core_members objects.

Query GraphQL

Service

File: show/show-core_members.service.ts

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

import { Injectable } from "@nestjs/common";
 
import { ShowCoreMembersObj } from "./dto/show-core_members.obj";
import { ShowCoreMembersArgs } from "./dto/show-core_members.args";
 
import { DatabaseService } from "@/database/database.service";
 
@Injectable()
export class ShowCoreMembersService {
  constructor(private databaseService: DatabaseService) {}
 
  async show({
    cursor,
    first,
    last
  }: ShowCoreMembersArgs): Promise<ShowCoreMembersObj> {}
}

Initial values for pagination

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

import {
  inputPaginationCursor,
  outputPagination
} from "@/functions/database/pagination";
import { core_users } from "@/src/admin/core/database/schema/users";
import { SortDirectionEnum } from "@/types/database/sortDirection.type";
 
const pagination = await inputPaginationCursor({
  cursor,
  database: core_users,
  databaseService: this.databaseService,
  first,
  last,
  primaryCursor: { order: "ASC", key: "id", schema: core_users.id },
  defaultSortBy: {
    direction: SortDirectionEnum.desc,
    column: "joined"
  },
  sortBy
});

Query from database

We will use findMany() method to get the data from the database. We will use with argument to get the avatar and group data from the core_users table.

const edges = await this.databaseService.db.query.core_users.findMany({
  ...pagination,
  with: {
    avatar: true,
    group: {
      with: {
        name: true
      }
    }
  }
});

Where argument

If you want to use where argument you can pass it to the findMany() method like this:

const where = or(
  eq(core_users.name_seo, name_seo),
  or(
    ilike(core_users.name, `%${search}%`),
    ilike(core_users.email, `%${search}%`),
    Number(search) ? eq(core_users.id, Number(search)) : undefined
  )
);
 
const edges = await this.databaseService.db.query.core_users.findMany({
  ...pagination,
  where: and(pagination.where, where),
  with: {
    avatar: true,
    group: {
      with: {
        name: 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 { outputPagination } from "@/functions/database/pagination";
 
const totalCount = await this.databaseService.db
  .select({ count: count() })
  .from(core_users)
  .where(where);
 
return outputPagination({ edges, totalCount, first, cursor, last });

Complite example

import { Injectable } from "@nestjs/common";
import { and, count, eq, ilike, or } from "drizzle-orm";
 
import { ShowCoreMembersObj } from "./dto/show.obj";
import { ShowCoreMembersArgs } from "./dto/show.args";
 
import { DatabaseService } from "@/database/database.service";
import {
  inputPaginationCursor,
  outputPagination
} from "@/functions/database/pagination";
import { core_users } from "@/src/admin/core/database/schema/users";
import { SortDirectionEnum } from "@/types/database/sortDirection.type";
 
@Injectable()
export class ShowCoreMembersService {
  constructor(private databaseService: DatabaseService) {}
 
  async show({
    cursor,
    first,
    last,
    name_seo,
    search
  }: ShowCoreMembersArgs): Promise<ShowCoreMembersObj> {
    const pagination = await inputPaginationCursor({
      cursor,
      database: core_users,
      databaseService: this.databaseService,
      first,
      last,
      primaryCursor: { order: "ASC", key: "id", schema: core_users.id },
      defaultSortBy: {
        direction: SortDirectionEnum.desc,
        column: "joined"
      }
    });
 
    const where = or(
      eq(core_users.name_seo, name_seo),
      or(
        ilike(core_users.name, `%${search}%`),
        ilike(core_users.email, `%${search}%`),
        Number(search) ? eq(core_users.id, Number(search)) : undefined
      )
    );
 
    const edges = await this.databaseService.db.query.core_users.findMany({
      ...pagination,
      where: and(pagination.where, where),
      with: {
        avatar: true,
        group: {
          with: {
            name: true
          }
        }
      }
    });
 
    const totalCount = await this.databaseService.db
      .select({ count: count() })
      .from(core_users)
      .where(where);
 
    return outputPagination({ edges, totalCount, first, cursor, last });
  }
}

ℹ️

Remember if you want use findMany() query with where argument you need to pass where argument to the findMany() and count() method.

Output

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

Overview Sorting