Pagination offset-based and cursor-based for NestJS
MIT License
PostgreSQL
, MySQL
, MariaDB
.0.0.2
:
OR
fts
(Full Text Search)Install dependencies
# npm
npm i @nestjs/common typeorm
# OR
# yarn
yarn add @nestjs/common typeorm
Install package
# npm
npm i @blogcode/nest-typeorm-pagination
# OR
# yarn
yarn add @blogcode/nest-typeorm-pagination
page=1&limit=10&filters[name]=abc&filters[age][value]=18&filters[age][operator]=gte&order[name]=1&order[age]=0
page=1
: Get page 1, offset 0limit=10
: Per page 10 records.filters[name]=abc
: Field name
or alias name
equal to abc
.filters[age][value]=18
: Field age
or alias age
will be compare.filters[age][operator]=gte
: Field age
or alias age
compare greater than or equal to 18
.order[name]=1
: Order by name
or alias name
is DESC
.order[age]=0
: Order by age
or alias age
is ASC
.cursor=10&limit=10&direction=next&filters[name]=abc&filters[age][value]=18&filters[age][operator]=gte&order[name]=1&order[age]=0
cursor=10
: This indicates the current position in the dataset. The cursor is typically a unique identifier or an offset that helps the server know where to continue fetching records from.limit=10
: This specifies the number of records to return in the response. In this case, the client is requesting 10 records.direction=next
: This indicates the direction in which to paginate. The value next
suggests that the client wants to fetch the next set of records following the current cursor position.filters[name]=abc
: This applies a filter on the name
field, where the value must be abc
.filters[age][value]=18
and filters[age][operator]=gte
: These two parameters work together to filter the age
field. The value
is set to 18
, and the operator
is gte
, which stands for "greater than or equal to." Thus, this filter will include records where the age is 18 or older.order[name]=1
and order[age]=0
: These parameters specify the sorting order of the results. The order[name]=1
indicates that the results should be sorted by the name
field in ascending order, while order[age]=0
indicates that the results should be sorted by the age
field in descending order.Operator Group | Description |
---|---|
eq , =
|
Equal to |
neq , !=
|
Not equal to |
gt , >
|
Greater than |
lt , <
|
Less than |
gte , >=
|
Greater than or equal to |
lte , <=
|
Less than or equal to |
in |
In (within a specified set) |
nin |
Not in (not within a specified set) |
like |
Like (matches a pattern) |
ilike |
Case-insensitive like (matches a pattern) |
nlike |
Not like (does not match a pattern) |
nilike |
Case-insensitive not like (does not match a pattern) |
is |
Is (matches a specific value, often used for null checks) |
isnot |
Is not (does not match a specific value, often used for null checks) |
bw |
Between (within a specified range) |
nbw |
Not between (not within a specified range) |
export const DEFAULT_LIMIT = 10;
export const MAX_LIMIT = 1000;
export function isBaseType(value: any): value is string | number | boolean {
return ['string', 'number', 'boolean'].includes(typeof value);
}
value
: The value to check.true
if the value is a string, number, or boolean; otherwise false
.export function getFilters(query: Record<string, any> | undefined) {
const filters: PaginationFilters = {};
if (typeof query?.filters === 'object') {
Object.keys(query.filters).forEach((key) => {
const value = query.filters[key];
if (typeof value === 'object') {
filters[key] = {
value: value.value,
operator: value.operator,
};
} else if (isBaseType(value)) {
filters[key] = {
value,
operator: 'eq',
};
}
});
}
return filters;
}
query
: An object containing the query parameters.export function getOrder(query: Record<string, any> | undefined) {
const order: PaginationOrder = {};
if (typeof query?.order === 'object') {
Object.keys(query.order).forEach((key) => {
order[key] = parseInt(query.order[key]) === 1 ? 1 : 0;
});
}
return order;
}
query
: An object containing the query parameters.export function getCursorDirection(query: Record<string, any> | undefined) {
const direction = query?.direction as string;
return direction?.toLowerCase() === 'prev' ? 'prev' : 'next';
}
query
: An object containing the query parameters.'prev'
or 'next'
).PaginationOffsetDto
containing the following properties:
page
: The current page number, defaulting to 1 if not provided in the query.limit
: The number of records per page, defaulting to DEFAULT_LIMIT
if not provided in the query.filters
: An object representing the filters for pagination, extracted from the query.order
: An object representing the order for sorting, extracted from the query.// src/modules/item.repo.ts
import { Injectable } from '@nestjs/common';
import { Repository } from 'typeorm';
import { PaginationService, PaginationOffsetDto } from '@blogcode/nest-typeorm-pagination';
@Injectable()
export class ItemService {
constructor(
private readonly itemRepo: Repository<ItemEntity>,
private readonly paginationService: PaginationService,
) {
this.paginationService.setPrimaryKey('id'); // value `id` is default inside package.
// Set others value if need
}
getItemsOffset(query: PaginationOffsetDto) {
return this.paginationService.offset(this.itemRepo, query);
}
getItemsCursor(query: PaginationCursorDto) {
return this.paginationService.cursor(this.itemRepo, query);
}
getTotalItemCursor(query: PaginationCursorDto) {
return this.paginationService.getCursorTotal(this.itemRepo, query);
}
}
// src/modules/item.controlller.ts
import { Controller, Get } from '@nestjs/common';
import { Offset, PaginationOffsetDto } from '@blogcode/nest-typeorm-pagination';
import { ItemRepo } from './item.repo';
@Controller('items')
export class ItemsController {
constructor(private readonly itemRepo: ItemRepo) {}
@Get('get-paging-items-by-offset')
getItems(@Offset() query: PaginationOffsetDto) {
// Your logic to handle the pagination
return this.itemsService.getItemsOffset(query);
}
}
PaginationOffsetDto
containing the following properties:
cursor
: The current cursor position, extracted from the query.direction
: The direction of pagination ('prev'
or 'next'
), determined from the query.limit
: The number of records to return, defaulting to DEFAULT_LIMIT
if not provided in the query.filters
: An object representing the filters for pagination, extracted from the query.order
: An object representing the order for sorting, extracted from the query.// src/modules/item.controlller.ts
import { Controller, Get } from '@nestjs/common';
import { Offset, PaginationCursorDto } from '@blogcode/nest-typeorm-pagination';
import { ItemRepo } from './item.repo';
@Controller('items')
export class ItemsController {
constructor(private readonly itemRepo: ItemRepo) {}
@Get('get-paging-items-by-cursor')
getItems(@Cursor() query: PaginationCursorDto) {
// Your logic to handle the pagination
return this.itemsService.getItemsCursor(query);
}
}
PaginationService provides methods for performing data pagination in two ways: offset-based and cursor-based. It supports applying filters, sorting, and setting limits on the number of returned results.
setFilterMapping(mapping: Record<string, string>): this
mapping
: Object mapping between DTO field names and database field names.this
- allows method chaining.setDefaultLimit(limit: number): this
limit
: Default number of results (must be greater than 0).this
- allows method chaining.setMaxLimit(limit: number): this
limit
: Maximum number of results (must be greater than 0).this
- allows method chaining.setEntityName(name: string): this
name
: Name of the entity.this
- allows method chaining.setResultName(name: string): this
name
: Name of the result field.this
- allows method chaining.setPrimaryKey(key: string): this
key
: Name of the primary key.this
- allows method chaining.async offset<T extends ObjectLiteral, K extends string = string>(
repository: Repository<T>,
dto: PaginationOffsetDto,
): Promise<PaginationOffsetResult<K, T>>
repository
: Repository of the entity.dto
: DTO object containing pagination, filters, and sorting information.Promise<PaginationOffsetResult<K, T>>
- Result of offset-based pagination.async cursor<T extends ObjectLiteral, K extends string = string>(
repository: Repository<T>,
dto: PaginationCursorDto,
): Promise<PaginationCursorResult<K, T>>
repository
: Repository of the entity.dto
: DTO object containing pagination, filters, sorting information, and cursor.Promise<PaginationCursorResult<K, T>>
- Result of cursor-based pagination, including next and previous cursors.async getCursorTotal<T extends ObjectLiteral>(
repository: Repository<T>,
dto: PaginationCursorDto,
): Promise<number>
repository
: Repository of the entity.dto
: DTO object containing filter information.Promise<number>
- Total number of items based on the filters.Set mapping fields
@Injectable()
export class UserService {
constructor(private readonly paginationService: PaginationService) {
this.paginationService.setFilterMapping({
name: 'username, email, firstname, lastname'
});
}
}
Query string:
filters[name]=text
Result:
WHERE (username = "text" OR email = "text" OR firstname = "text" OR lastname = "text")
Query string:
filters[name][value]=text&filter[name][operator]=like
Result:
WHERE (username LIKE "%text%" OR email LIKE "%text%" OR firstname LIKE "%text%" OR lastname = "%text%")
Set mapping fields
@Injectable()
export class UserService {
constructor(private readonly paginationService: PaginationService) {
this.paginationService.setFilterMapping({
name: "to_tsquery('english', ':name')"
});
}
}
Property name
and :name
must be same word.
Query string
filters[name][value]=search%20keyword&filters[name][operator]=fts
Result:
WHERE name @@ to_tsquery('english', 'search 20keyword')
Set mapping fields
@Injectable()
export class UserService {
constructor(private readonly paginationService: PaginationService) {
this.paginationService.setFilterMapping({
name: 'username, email, firstname, lastname'
});
}
}
Query string
filters[name][value]=search%20keyword&filters[name][operator]=fts
Result:
WHERE MATCH (username, email, firstname, lastname) AGAINST ('search keyword' IN BOOLEAN MODE)