is it possible to add a custom sorting function to the tool ?

[hippolyte42]

just wondering, thanks

[chris-snow]

You can sort your query results using typeorm itself.

[benjamin658]

@mr-cool is right, use query builder to sort the results as usual then pass it to the paginator.

[hippolyte42]

thanks for your answers @mr-cool @benjamin658
It seems like the paginator resort my data after my query due to sortBy field being undefined. To give you more info on the situation: I am currently trying to sort a table called request that has a userId field; i want to sort the request table by username but I struggle to do so.

[remithomas]

@HippolyteJacque have you fixed your issue ?
It looks like I got the same issue.

My query builder looks like that:

const queryBuilder = this.productsRepository
      .createQueryBuilder('product')
      .addSelect('substring("metadataProduct"."countryOfOriginCode" from 1 for 2) AS "metadataProduct_country_code"')
      .innerJoinAndMapOne('product.metadataProducts', metadataProductEntity, 'metadataProduct', 'product.id = metadataProduct.product_id')
      .leftJoin(
        ProductToOrganisationEntity,
        'product_to_organisation',
        'product_to_organisation.product_id = product.id AND product_to_organisation.organisation_id = :organisationId',
        { organisationId },
      )
      .leftJoin(
        ProductEntity,
        'metadataProduct2',
        'metadataProduct2.product_id = product.id AND metadataProduct.createdAt < metadataProduct2.createdAt',
      )
      .where('metadataProduct2.id IS NULL')
      .andWhere(filtersSubQuery.length ? filtersSubQuery : 'metadataProduct.id IS NOT NULL', filterValues)
      .andWhere('product_to_organisation.organisation_id = :organisationId', { organisationId });
    .orderBy('substring("metadataProduct"."countryOfOriginCode" from 1 for 2)', 'DESC');

and my paginator

const paginator = buildPaginator({
      entity: ProductEntity,
      alias: 'product',
      paginationKeys: ['customId'],
      query: {
        limit: queryOptions.first,
        order: 'ASC',
        afterCursor: queryOptions.afterCursor,
        beforeCursor: queryOptions.beforeCursor,
      },
    });

    return paginator.paginate(queryBuilder);

I tried to use the sort function (.orderBy('substring("Product"."countryOfOriginCode" from 1 for 2)', 'DESC'); and as well a basic sort (.orderBy('Product_country_code', 'DESC');

Nothing works :/

Maybe because I try to sort on inner tables ? @benjamin658

[benjamin658]

@HippolyteJacque @remithomas thanks for the reporting.

Could you do me a little favor?
Please try to use .addOrderBy() instead of orderBy() and see if it works.

Thanks.

[remithomas]

@benjamin658 addOrderBy didn't work for basic sort and sort function. Thanks for your help

[jimuelpalaca]

@benjamin658 I think the problem with the sorting is being affected by this.buildOrder(); that is being called in appendPagingQuery(builder). That's why the .addOrderBy() or .orderBy() of the query builder is being "ignored". Maybe the order property of Paginator should not have a default value (private order: Order = 'DESC';), and allow developers to choose whether they will use the package sorting by ordering it using paginationKeys or by custom sorting via query builder.

[hippolyte42]

Hello @remithomas I have actually switch to our own paginator tool, page based not cursor base tho, took 1 day to build it with typeorm, pretty simple, sorry I could not help more

@benjamin658 have not get the time to try with addOrderBy recently but to my memory it did not work

[benjamin658]

Hi @jimuelpalaca and @HippolyteJacque ,

Thanks for your feedback.
I think the addOrderBy() must be appended right after the orderBy(), so I am planning to add custom order options to the buildPaginator.

However, I'm not sure whether the custom order has any side effects, I have to add more test cases to verify it. Hope I could add this feature in few days.

[cjlynch12]

@benjamin658
Any update on this functionality? Running into a similar issue. Looking to be able to sort on joined tables, but could also use a custom sort or just have it respect addOrder/addOrderBy.

[benjamin658]

Hi @cjlynch12 ,

Sorry for the late reply.

I'm still working on it, but I found that the TypeORM seems to have a bug on the addOrderBy nested column with take, and it refers to these issues:

• QueryBuilder produces wrong SQL with order by + nested fields, breaks pagination-related queries with skip() and take() typeorm/typeorm#6294
• Using LIMIT combined with ORDER BY returns under certain conditions less results than expected typeorm/typeorm#4073

(actually, there are a lot of related opened issues)

If the TypeORM could not solve the issue, the custom order feature will only allow ordering on the same entity.

Any suggestions are welcome.