Is Postgres DISTINCT ON not supported by exposed?

# exposed

[Mario Andhika]

Is Postgres DISTINCT ON not supported by exposed?

[Oleg Babichev]

Hi, in postgres there is withDistinct() but looks like it adds only DISTINCT key word without possibility to specify column.

Created the issue on YT to implement it later: EXPOSED-560 Support DISTINCT ON from Postgres

[Chantal Loncle]

Hi @Mario Andhika A small custom function can be used for this. The code here still works, but I've added a simplified alternative below:

fun Column<*>.distinctOn(vararg extraColumns: Column<*>) =
    DistinctOn(this, extraColumns)

class DistinctOn<T>(
    val expr: Column<T>,
    val columns: Array<out Column<*>>
) : Function<T>(expr.columnType) {
    override fun toQueryBuilder(queryBuilder: QueryBuilder) {
        queryBuilder {
            val txm = TransactionManager.current()
            val distinctNames = listOf(expr, *columns).joinToString { txm.fullIdentity(it) }
            append("DISTINCT ON ($distinctNames) ${txm.fullIdentity(expr)}")
        }
    }
}

Sessions
    .innerJoin(Users)
    .select(Sessions.user.distinctOn(Sessions.name), Users.id)
// SELECT
// DISTINCT ON (sessions."userId", sessions."name") sessions."userId", users.id
// FROM sessions INNER JOIN ...

[Mario Andhika]

Thanks @Chantal Loncle. I’m getting this class does not have a constructor with that code sample though. I’m on exposed 0.53.0

[Chantal Loncle]

Please check imports and make sure that the Function being used is the Exposed one, not the Kotlin one. IDE likes to choose the Kotlin one when code is copy-pasted.

[Mario Andhika]

Thanks @Chantal Loncle! It works now