Sorting alphabetical insensitive [Not Bug]

[benyou1969]

I know it should work like that, but is there away to customise the default behaviour

instead of this

{
  "data": [{ "name": "Apple"}, {"name": "Orange"}, {"name": "banana"}]
}

I want this

{
  "data": [{ "name": "Apple"}, {"name": "banana"}, {"name": "Orange"}]
}

[ppetzold]

I would prefer the insensitive case as default as well. could you check the TypeORM docs and submit PR here?

[benyou1969]

Thank you for replying @ppetzold , And Thank you for the great great library you made.

I believe we should stick to the typeorm default sorting (insensitive case as default) without changing the default library.

I wanted to share my solutions

⚠️ Before you use this be aware that the queryBuilder.addOrderBy will overcome the other sorts (one sort only, my case)⚠️

Solution with typeorm

1) for a simple pagination with one sort only, and applies always to the pagination (hardcoded)

 queryBuilder.addOrderBy(`LOWER(q.name)`, 'ASC');

2) for complex one with one sort only (dynamic)

console.log(query.sortBy) // returns multi dimensional array [[ 'name', 'ASC' ], [ 'length', 'DESC' ], ...etc]

// Add these two helper functions.
function GetSecondValueFromMultiDimensionalArray(array, searchTerm) {   
  for(var i in array){
    if(array[i][0] == searchTerm){
      return array[i][1];      
    }  
  }  
}

function CheckIfValueExistsInMultiDimensionalArray(array, value): boolean {
  if (array?.length == 0 || !array) {
    return false;
  }
  return array.some(function(entry) {
      // If this entry in the array is an array, recurse
      if (Array.isArray(entry)) {
          return CheckIfValueExistsInMultiDimensionalArray(entry, value);
      }
      // It isn't, do an equality check
      return entry === value;
  });
}

// Pagination method

public paginate(query: PaginateQuery): Promise<Paginated<Entity>> {
    const queryBuilder = add_your_query_builder

 const found = CheckIfValueExistsInMultiDimensionalArray(query?.sortBy, 'name');
    if (found) {
      const value = GetSecondValueFromMultiDimensionalArray(query.sortBy, 'name');
       // replace q.name with the column you think should be order  alphabetical
      if (value == 'ASC') {
        queryBuilder.addOrderBy(`LOWER(category.name)`, 'ASC');
      }
      if (value == 'DESC') {
        queryBuilder.addOrderBy(`LOWER(category.name)`, 'DESC');
      }
    }
    return paginate(query, queryBuilder, {
      defaultLimit: 20,
      sortableColumns: [
        'name',
        'createdAt',
      ],
      defaultSortBy: [['name', 'ASC']],
    });
}

Not perfect answer but it helps in my situation

[Dafnik]

Here is my take on a more generic solution for all others stumbling into this problem.

import { ApiProperty } from '@nestjs/swagger'
import { Type } from 'class-transformer'
import { IsInt, IsOptional, Max, Min } from 'class-validator'
import type { PaginateQuery } from 'nestjs-paginate'

export class PaginationRequestDto implements Omit<PaginateQuery, 'path'> {
  @ApiProperty({
    nullable: true,
    minimum: 1,
    default: 1
  })
  @Type(() => Number)
  @IsInt()
  @Min(1)
  @IsOptional()
  readonly page?: number = 1

  @ApiProperty({
    nullable: true,
    minimum: 1,
    maximum: 100,
    default: 20
  })
  @Type(() => Number)
  @IsInt()
  @Min(1)
  @Max(100)
  @IsOptional()
  readonly limit?: number = 20
}

import { ApiProperty } from '@nestjs/swagger'

export class PaginationResponseMetaDto  {
  @ApiProperty()
    currentPage: number
  @ApiProperty()
    totalPages: number
}

export class PaginationResponseLinksDto {
  @ApiProperty({ required: false })
    first?: string
  @ApiProperty({ required: false })
    previous?: string
  @ApiProperty()
    current: string
  @ApiProperty({ required: false })
    next?: string
  @ApiProperty({ required: false })
    last?: string
}

export class PaginationResponseDto<T> {
  data: T[]
  @ApiProperty()
    meta: PaginationResponseMetaDto
  @ApiProperty()
    links: PaginationResponseLinksDto
}

import type { PaginateConfig } from 'nestjs-paginate'
import type { FindManyOptions, ObjectLiteral } from 'typeorm'

export interface PaginationQueryRequest<T extends ObjectLiteral> {
  pagination: PaginationRequestDto
  config: Omit<PaginateConfig<T>, 'relations' | 'select' | 'where'>
  entity?: FindManyOptions<T>
}

import { NotFoundException } from '@nestjs/common'
import type { PaginateConfig, PaginateQuery } from 'nestjs-paginate'
import { paginate as basePaginate } from 'nestjs-paginate'
import type { ObjectLiteral, Repository } from 'typeorm'
import { format } from 'util'

export async function paginateQuery<T extends ObjectLiteral> (
  repository: Repository<T>,
  query: PaginationQueryRequest<T>
): Promise<PaginationResponseDto<T>> {
  const queryBuilder = repository.createQueryBuilder('entity_q')

  for (const sortBy of query.pagination.sortBy ?? []) {
    queryBuilder.addSelect(`LOWER(entity_q.${sortBy[0]})`, `${sortBy[0]}_lowercase`)
    queryBuilder.addOrderBy(`${sortBy[0]}_lowercase`, sortBy[1] as 'ASC' | 'DESC')
  }

  for (const sortBy of query.pagination?.sortBy ?? query.config?.defaultSortBy ?? []) {
    const sortByPropertyName = sortBy[0] as Column<T>

    if (!query.config.sortableColumns.includes(sortByPropertyName)) {
      throw new BadRequestException('Trying to sort an not-sortable property')
    }
    queryBuilder.addSelect(`LOWER(entity_q.${sortByPropertyName})`, `${sortByPropertyName}_lowercase`)
    queryBuilder.addOrderBy(`${sortByPropertyName}_lowercase`, sortBy[1] as 'ASC' | 'DESC')
  }

  query.pagination.sortBy = undefined
  query.config.defaultSortBy = undefined

  const results = await basePaginate(query.pagination as PaginateQuery, queryBuilder, {
    relativePath: true,
    ...query.config ?? {},
    ...(query.entity as PaginateConfig<T>)
  })

  if (results.meta.totalPages > 0 && results.meta.currentPage > results.meta.totalPages) {
    throw new NotFoundException(
      format(
        'Trying to list an out-of-bounds page, where there is total of %d pages and you are trying to list page %d with taking %d items per page.',
        results.meta.totalPages,
        results.meta.currentPage,
        results.meta.itemsPerPage
      )
    )
  }

  return results
}