jooq use custom function in order by statement

I'm currently trying to introduce this function https://stackoverflow.com/a/12257917/4375998 into my project, but struggle with the function signature generated by jooq.

The generated code has these three signatures

public static String udfNaturalsortformat(        Configuration configuration      , String instring      , Integer numberlength      , String sameorderchars  )    public static Field<String> udfNaturalsortformat(        String instring      , Integer numberlength      , String sameorderchars  )    public static Field<String> udfNaturalsortformat(        Field<String> instring      , Field<Integer> numberlength      , Field<String> sameorderchars  )  

But none of them seem to fit my use case of ordering a table by name.

This is currently done in the following fashion: In some place a collection of search parameters is generated and this is later included in the query:

public Collection<? extends SortField<?>> getSortFields(List<Pageable.SortField> sorts) {          if (sorts.isEmpty()) {              return Collections.singletonList(ProjectRepositoryImpl.LAST_ACTIVITY.field("last_activity").desc());          }          List<SortField<?>> sortFields = new ArrayList<>();          for (Pageable.SortField sort : sorts) {              if (sort.getSortDirection() == Pageable.SortDirection.DESC) {                  sortFields.add(PROJECT.NAME.desc());              } else {                  sortFields.add(PROJECT.NAME.asc());              }          }            return sortFields;  }  

And the final query then looks like this

        Result<Record> queryResults = jooq.select(PROJECT.fields())                  .from(PROJECT)                  .where(searchCondition)                  .orderBy(transformer.getSortFields(pageable.getSorts()))                  .limit(pageable.getPageSize())                  .offset(pageable.getOffset())                  .fetch();  

So what I currently attempt to do is replace the sort field with something like this

    sortFields.add(udfNaturalsortformat(PROJECT.NAME, 10, ".").desc());  

but the signature mismatches.

What is the proper way to include this method in my order by statement?

https://stackoverflow.com/questions/67377395/jooq-use-custom-function-in-order-by-statement May 04, 2021 at 08:38AM