JOOQ combining Field and SortField in orderBy method?

Question

I'm trying to select a list of random records from a MySQL-table but with priority towards certain ENUM types. It works fine when I run the following plain SQL-query in a terminal:

select * from table_name where expires <= UNIX_TIMESTAMP()*1000 
order by enum_type desc, rand() limit 500;

But I get a compilation error from my IDE when writing the following code:

private List<FooRecord> getNextRecordsWeighted(Condition condition, int recordLimit) {
    final long timeNow = System.currentTimeMillis();
    return context.selectFrom(TABLE_NAME).where(TABLE_NAME.EXPIRES.lessOrEqual(timeNow)).
        orderBy(TABLE_NAME.ENUM_TYPE.desc(), DSL.rand()).limit(recordLimit).fetch();
}

Now, what my IDE says is that there obviously is no compatible method that I can call for doing this. How would I go about to solve this? Is there a workaround?

Answer 1

The problem is with the orderBy() methods' various overloads. You have:

• SelectOrderByStep(Field, Field)
• SelectOrderByStep(SortField, SortField)

Your TABLE_NAME.ENUM_TYPE.desc() is a SortField, whereas DSL.rand() is a Field. In order to make this work, you'll have to make DSL.rand() a SortField, by calling: DSL.rand().asc().

I recognise that this is somewhat of a flaw in the API, which can probably be fixed in a future version of jOOQ. I have created a GitHub issue for this fix: #3631