Optional Filter

[Ufuk Halis]

Hello!

There is a question from my side that I would like to ask.

In our team, we use Jooq with Kotlin. And imagine that we have a data class called SomeFilter.

SomeFilter(val firstName: String?, val lastName: String?, val age: Int?)

As you can see, this filter includes optional fields. While creating the query, I perform the following steps.

dsl.selectFrom(TABLE)

.where(firstName?.let{ FIRST_NAME.eq(it) })

.and(lastName?.let{ LAST_NAME.eq(it) })

.and(age?.let{ AGE.eq(it) })

And this kind of query works perfectly fine, in case a field is null, then Jooq ignores that null part.

But, the question is, is this the way of handling such an optional filter while writing Jooq queries?

Or is there a better way of doing it?

Thanks,

Ufuk

[Lukas Eder]

Hi Ufuk,

The recommended way is to use the DSL.noCondition() as documented here:

https://www.jooq.org/doc/latest/manual/sql-building/dynamic-sql/no-condition

While it is possible to pass a null Condition to these methods, this currently isn't a documented feature, specifically because it wouldn't be easy to implement consistently across the API. For example, the semantics of passing null to WHERE seems reasonable (though it would have a different semantics in SQL!), but it's not easy to see what passing null to the GROUP BY clause should mean. In any case, we won't break this behaviour in the future. Our tests cover this sufficiently, so even if not documented, you can use the API this way.

Best Regards,

Lukas

--
You received this message because you are subscribed to the Google Groups "jOOQ User Group" group.
To unsubscribe from this group and stop receiving emails from it, send an email to jooq-user+...@googlegroups.com.
To view this discussion visit https://groups.google.com/d/msgid/jooq-user/57a15be8-d145-42be-9b42-39273a5c601cn%40googlegroups.com.

[Ufuk Halis]

Thanks, got it, this answers everything for us :)

Lukas Eder <lukas...@gmail.com>, 22 Ağu 2025 Cum, 09:44 tarihinde şunu yazdı:

You received this message because you are subscribed to a topic in the Google Groups "jOOQ User Group" group.
To unsubscribe from this topic, visit https://groups.google.com/d/topic/jooq-user/wpGsr9qEf_A/unsubscribe.
To unsubscribe from this group and all its topics, send an email to jooq-user+...@googlegroups.com.
To view this discussion visit https://groups.google.com/d/msgid/jooq-user/CAB4ELO5Pb1X7hZ3kxZ%3D7xRP5FT6U7rHnMpf6OOLv6B%2BOtH1Leg%40mail.gmail.com.