I would like to use this query in exposed but can'...

# exposed

[Christiano]

I would like to use this query in exposed but can't seem to find any equivalent of ifnull.

SELECT * FROM Projects
    WHERE ( 
    id = IFNULL((SELECT max(id) FROM Projects WHERE id < 4),(SELECT id FROM Projects ORDER BY id DESC LIMIT 1))
)

Is there something else I can do, to still obtain the same result? 🤔

[Jaap Beetstra]

You could implement IFNULL yourself; probably a bit like this:

class IfNull<T>(private val nullable: Expression<out T?>, private val alternative: Expression<out T>) :
    Expression<T>() {
    override fun toQueryBuilder(queryBuilder: QueryBuilder) = queryBuilder {
        append("IFNULL(", nullable, ",", alternative, ")")
    }
}

Depending on your exact need, you may also need to wrap a subquery to an expression.

[Christiano]

Ah nice to know!

I tried turning it into subqueries since that is my use-case, but couldn't quite get that to work.
I ended up just creating a raw SQL statement and just using it like that.

val query = """
SELECT * FROM Projects
    WHERE ( 
         id = IFNULL((SELECT max(id) FROM Projects WHERE id < $id),(SELECT id FROM Projects ORDER BY id DESC LIMIT 1))
    )
""".trimIndent()

val newId = transaction {
    var newTempId: Int? = null
    exec(query, explicitStatementType = StatementType.SELECT) {
        while (it.next()) {
            newTempId = it.getInt("id")
        }
    }
    newTempId
}

[Chantal Loncle]

Hi @Christiano Just dropping in to confirm that Exposed does not support IFNULL. The closest out-of-the-box option might be coalesce(), but if that's not appropriate for your use case, then a custom expression is the best option.

If you really only need to compare subqueries, here's an alternative that should produce expected results:

class IfNull<T>(
    private val nullable: AbstractQuery<*>,
    private val alternative: AbstractQuery<*>
) : Expression<T>() {
    override fun toQueryBuilder(queryBuilder: QueryBuilder): Unit = queryBuilder {
        append("IFNULL((")
        nullable.prepareSQL(this)
        append("), (")
        alternative.prepareSQL(this)
        append("))")
    }
}

fun <T> ifNull(nullable: AbstractQuery<*>, query: AbstractQuery<*>): IfNull<T> = IfNull(nullable, query)

// example use 
projects.selectAll().where { projects.id eq ifNull(query1, query2) }

[Christiano]

Hi @Chantal Loncle thank you for this message! At first I went with IFNULL because that was the first thing I found, but by deploying my backend to Render (which has a PostgreSQL database) I had to use coalesce as the syntax does not include ifnull.

I might try what you suggested, but for now it's working fine with a raw query 😄