CODEWITHSUNDEEP
javamysqljooq
Tamwyn
Tamwyn

Reputation: 320

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?

Upvotes: 1

Views: 823

Answers (1)

Eugene Botyanovsky
Eugene Botyanovsky

Reputation: 871

As you can see in signatures of generated methods:

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
)

It has only overrides for all java simple objects (first one) or Field<?> references (second one).

Since you use PROJECT.NAME as a first argument, you would probably use second generated method, but then you have to pass other arguments all of Field<?> type. So try DSL.val or DSL.inline, which is a way to pass constant value as a field:

udfNaturalsortformat(Staff.STAFF.ACQUIRED_COMPANY, DSL.val(10), DSL.val(".")).desc();

Upvotes: 2

Related Questions