Sorting

VitNode provide a sorting system to order the query result.

Here is an example of how to use the sorting system. We will use the core_members table as an example.

⚠️

Sorting system is based on Pagination System. If you don't want to use the pagination system you can use the orderBy argument directly in the findMany() method.

Data Transfer Object (DTO)

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

Initialize

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 {}

Create a enum

To restrict sorting by selected columns you have to create a enum.

enum ShowCoreMembersSortingColumnEnum {
  name = 'name',
  joined = 'joined',
  first_name = 'first_name',
  last_name = 'last_name',
  posts = 'posts',
  followers = 'followers',
  reactions = 'reactions'
}

⚠️

Remember to export the enum. It will be used in the next steps.

Register the enum

NestJS require to register the enum in the GraphQL schema. You can read more in NestJS documentation (opens in a new tab).

import { registerEnumType } from '@nestjs/graphql';
 
registerEnumType(ShowCoreMembersSortingColumnEnum, {
  name: 'ShowCoreMembersSortingColumnEnum'
});

Create a input type

Now you have to create a input type to use the enum in the args type.

import { InputType } from '@nestjs/graphql';
 
import { SortDirectionEnum } from '@/types/database/sortDirection.type';
 
@InputType()
class SortByArgs {
  @Field(() => ShowCoreMembersSortingColumnEnum)
  column: ShowCoreMembersSortingColumnEnum;
 
  @Field(() => SortDirectionEnum)
  direction: SortDirectionEnum;
}

Add input type to args type

Now you have to add the input type to the args type as arry and make it optional (If you want).

import { ArgsType, Field } from '@nestjs/graphql';
 
@ArgsType()
export class ShowCoreMembersArgs {
  @Field(() => [SortByArgs], { nullable: true })
  sortBy: SortByArgs[] | null;
}

Complite example

import { ArgsType, Field, InputType, registerEnumType } from '@nestjs/graphql';
 
import { SortDirectionEnum } from '@/types/database/sortDirection.type';
 
enum ShowCoreMembersSortingColumnEnum {
  name = 'name',
  joined = 'joined',
  first_name = 'first_name',
  last_name = 'last_name',
  posts = 'posts',
  followers = 'followers',
  reactions = 'reactions'
}
 
registerEnumType(ShowCoreMembersSortingColumnEnum, {
  name: 'ShowCoreMembersSortingColumnEnum'
});
 
@InputType()
class SortByArgs {
  @Field(() => ShowCoreMembersSortingColumnEnum)
  column: ShowCoreMembersSortingColumnEnum;
 
  @Field(() => SortDirectionEnum)
  direction: SortDirectionEnum;
}
 
@ArgsType()
export class ShowCoreMembersArgs {
  @Field(() => [SortByArgs], { nullable: true })
  sortBy: SortByArgs[] | null;
}

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({ sortBy }: ShowCoreMembersArgs): Promise<ShowCoreMembersObj> {}
}

Query from database

To the inputPaginationCursor() function you have to pass the sortBy argument.

import { inputPaginationCursor } from '@/functions/database/pagination';
 
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
});

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,
    sortBy
  }: 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'
      },
      sortBy
    });
 
    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 });
  }
}

Pagination Authorization