In the new fts5 virtual tables in SQLite there is a ranking function bm25. What is the best way to call it from jOOQ? Is there any way to introduce this function as Java/Kotlin function or should it be just a string literal?

What I do currently is

.orderBy(field("bm25(books_fts)"))

but indeed this approach is error-prone. I can make a typo in any part and will have an error only in runtime.

Is there any better way?

CodePudding user response：

Instead of using the plain SQL template directly in your query, better create a custom library for these purposes, e.g.

fun bm25(t: Table<*>): Field<Double> = field("bm25({0})", SQLDataType.DOUBLE, t)

Now you can pretend this is a natively supported function in jOOQ and use it in a type safe way everywhere.