Allow to specify nullable column ordering behaviour

[Edvinas01]

When sorting a table in descending order while using PostgreSQL, NULL values appear on top. However, I would like NULL values to appear last. To solve this issue PostgreSQL has NULLS LAST and NULLS FIRST options:

SELECT *
FROM table_name
ORDER BY column_name DESC NULLS LAST;

SELECT *
FROM table_name
ORDER BY column_name ASC NULLS FIRST;

However when using Exposed, there doesn't seem a way to specify this as I'm only able to supply a vararg of Pair<Column<*>, Boolean> when ordering my queries:

val columns: List<Pair<Column<*>, Boolean>> = ...
val query: Query = ...

query
    // Can't specify weather nulls should appear last or first.
    .orderBy(*columns)
    .limit(...)
    .map(...)

[husnjak]

When will this enhancement be released?

[KennethWussmann]

Okay, I found a way to archive NULLS LAST via an exposed expression.

class ColumnNullsLast(private val col: Column<*>) : Expression<String>() {
    override fun toQueryBuilder(queryBuilder: QueryBuilder) = queryBuilder {
        append(col, " IS NULL, ", col)
    }
}

fun Column<*>.nullsLast() = ColumnNullsLast(this)

Usage:

query.orderBy(Users.age.nullsLast() to SortOrder.DESC)

[ybznek]

instead of ColumnNullsLast org.jetbrains.exposed.sql.IsNullOp could be used

[MEJIOMAH]

NULLS FIRST/LAST use index in postgres. This solution for null first

enum class NullOrder {
    FIRST,
    LAST
}

fun Query.orderBy(vararg expressions: Pair<NullsExpression, SortOrder>): Query {
    return orderBy(
        *expressions.map { (nullExpression, sortOrder) ->
            Pair(OrderWithNull(nullExpression.expression, sortOrder, nullExpression.nullOrder), sortOrder)
        }.toTypedArray()
    )
}

fun Expression<*>.nullsFirst(): NullsExpression = NullsExpression(this, NullOrder.FIRST)
fun Expression<*>.nullsLast(): NullsExpression = NullsExpression(this, NullOrder.LAST)

class NullsExpression(val expression: Expression<*>, val nullOrder: NullOrder)

private class OrderWithNull(
    private val expression: Expression<*>,
    private val sortOrder: SortOrder,
    private val nullOrder: NullOrder
) : Expression<String>() {
    override fun toQueryBuilder(queryBuilder: QueryBuilder): Unit = queryBuilder {
        append(expression)
        append(" ")
        append(sortOrder.name)
        when (nullOrder) {
            NullOrder.FIRST -> append(" NULLS FIRST ")
            NullOrder.LAST -> append(" NULLS LAST ")
        }

        append(", TRUE ")
    }
}```

[LukaszSkowronek]

Hi, is there any progress on that?

Regarding last comment it produces 'order by' query part as e.g
ORDER BY PRIORITY DESC NULLS LAST, TRUE DESC, VALUABLE DESC NULLS LAST, TRUE DESC
Which results in wrong query.

[erwinw]

This got implemented in #1344 and several follow-ups from @Tapac; no release has been cut yet, but I'm guessing that won't take too long. Thanks Andrey!