defaultSortBy of a related entity breaks pagination?

[ilijavuk]

Hello!

I'm facing an interesting problem. I've got a Project entity which has related Project phases and Additional work. When trying to apply default sort by their id's the pagination starts behaving in a weird way.

When 6 projects are present in the database, sending a request with Page=1, Limit=5 returns 3 records. meta.totalItems is 6.
What I'm trying to accomplish with the defaultSortBy is for my projects to be sorted first by their id's, then I want the project's related projectPhase's to be sorted by their id, and likewise with the additionalWork.

Am I trying to accomplish this in a way that's not intended?

This is what my pagination config looks like:

export const FindAllPaginationConfig: PaginateConfig<Project> = {
  sortableColumns: [
    'id',
    'name',
    'description',
    'dueDate',
    'estimatedHoursAmount',
    'createdAt',
  ],
  searchableColumns: [
    'name',
    'description',
    'dueDate',
    'estimatedHoursAmount',
  ],
  filterableColumns: {
    name: [FilterOperator.EQ, FilterSuffix.NOT],
  },
  maxLimit: PAGINATION_MAX_LIMIT,
  defaultLimit: PAGINATION_MAX_LIMIT,
  defaultSortBy: [
    ['id', 'ASC'],
    ['projectPhases.id', 'ASC'],
    ['additionalWork.id', 'ASC'],
  ],
  relations: ['projectPhases', 'additionalWork', 'externalAssociate'],
};

Any help is greatly appreciated!

[ppetzold]

By default this lib is using take/skip method of typeorm's query builder. There are some unresolved issues with take/skip, joins and orderBy within typeorm which likely is causing your problem.

Depending on the kind of relationships of your query, you might be able to fix it by using PaginationType.LIMIT_AND_OFFSET instead of take/skip. Be aware tho that this might cause other side effects 😅

[ilijavuk]

Thanks for the response. What kind of issues might arise due to using PaginationType.LIMIT_AND_OFFSET?