Return empty expression in Exposed DAO?

Question

I'm trying to conditionally add a part to my SQL query using Exposed's DAO API. My goal is to have:

SELECT * FROM table
  WHERE column1 = 1
  AND column2 = $value
  AND column3 = 3

where the existence of the AND column2 = $value part depends on a filter.

I've tried with:

TableDAO.find {
    Table.column1 eq 1 and (
        when (filter.value) {
            null -> null // Type mismatch. Required: Expression<Boolean>. Found: Op<Boolean>?
            else -> Table.column2 eq filter.value
        }) and (
            Table.column3 = 3
        )
}.map { it.toModel() }

but I can't find a way to return an empty expression or somehow exclude that part from the query. The only solution I can make work is something like

null -> Table.column2 neq -1

but I feel like there should be a better way.

Answer 1

You'll have to assign your expressions into a local variable:

var expr = Table.column1 eq 1 
if(filter.value) {
    expr = expr and (Table.column2 eq filter.value)
}
expr = expr and (
    Table.column3 = 3
)

I don't have my IDE in front of me, but this is the general idea. You can try to figure out something clever, but it would make your code unnecessarily complex.