Adding distinct on to PostgreSQL

[lephyrius]

How do I add DISTINCT ON (columns) to SELECT clause? I want to make this query: SELECT DISTINCT ON (testdummies.id) testdummies.id, testdummies.integer_array, testdummies.name FROM testdummies LEFT JOIN testdummies t0 ON (t0.id = ANY(testdummies.test_dummies) AND t0.name IN ('test2', 'test3') ) WHERE ((testdummies.private = false) OR (testdummies.private IS NULL)) AND t0.name IN ('test2', 'test3') LIMIT 10

I have looked at the prepareSQL function : https://github.com/JetBrains/Exposed/blob/master/src/main/kotlin/org/jetbrains/exposed/sql/Query.kt#L159-L208

It doesn't look like I can supply a list of distinct columns. Is there another method that can do this?

[Tapac]

How DISTINCT ON differs from simple DISTINCT?

[lephyrius]

@Tapac DISTINCT ON (testdummies.id) testdummies.id, testdummies.integer_array, testdummies.name Means that I only want distinct on the id column.

[Tapac]

AFAIU this a per column function, so you can try :

class DistinctOn<T>(val expr: Column<T>) : Function<T>(expr.columnType) {
    override fun toSQL(queryBuilder: QueryBuilder) = "DISTINCT ON (${expr.toSQL(queryBuilder)}) ${expr.toSQL(queryBuilder)}"
}

[mellson]

I would also love to get DISTINCT ON 👍

@Tapac thanks for the input! I'm not experienced enough with Exposed to make this work from your example however. I'm unsure about the return type you've used. If you have the time I would appreciate an example?

[paulkagiri]

@mellson here's an example:

Suppose you have these 2 tables:

object Users : IntIdTable() {
    val name = varchar("name", length = 60)
    val createdAt = datetime("createdAt")
}

object Sessions : IntIdTable() {
    val name = varchar("name", length = 60)
    val user = reference("userId", Users, onDelete = ReferenceOption.CASCADE)
    val createdAt = datetime("createdAt")
}

You can use what @Tapac has above with a little bit of modification:

fun Column<*>.distinctOn(): Function<Int> = DistinctOn(this)

class DistinctOn(val expr: Expression<*>) : Function<Int>(IntegerColumnType()) {
    override fun toSQL(queryBuilder: QueryBuilder) = "DISTINCT ON (${expr.toSQL(queryBuilder)}) ${expr.toSQL(queryBuilder)}"
}

and then now suppose you want to get all sessions but distinct by user you can have:

transaction{
    Sessions.innerJoin(Users).slice(Sessions.user.distinctOn(), Users.id).selectAll().map{...}
}

[mellson]

Thank you @PaulMuriithi - That's very kind of you 🙏

[bastman]

@mellson thanks a lot for your idea.

Unfortunately I could not make it work with exposed 17.x . Maybe you can help me with it :)

class DistinctOn(vararg val expr: Expression<*>) : ExposedFunction<Int>(IntegerColumnType()) {
    override fun toQueryBuilder(queryBuilder: QueryBuilder)  = queryBuilder {
        append("DISTINCT ON (")
        append(*expr)
        append(")")
    }
}

The query being generated results in a syntax error.

ERROR: syntax error at or near "," at character 35 STATEMENT:

SELECT DISTINCT ON (author."name"), tweet.id, tweet."comment" FROM tweet

this would be the right syntax:

SELECT DISTINCT ON (author."name") tweet.id, tweet."comment" FROM tweet

It looks like "slice" concats all expressions with and "," .

But we need to have SELECT DISTINCT ON (...) col1, col2 FROM table --> so no "," between ON(...) and the rest of that statement.

@Tapac , do you have an idea how solve this?

# this is another approach to implement "DistinctOn"
# but results in the same issue, when used within slice()

fun customDistinctOn(vararg expressions: Expression<*>) = CustomStringFunction(
        "DISTINCT ON",
        *expressions
)

[KennethWussmann]

@bastman You can solve it by appending a TRUE to your list of columns:

class DistinctOn<T>(private val expr: Column<T>) : Function<T>(expr.columnType) {
  override fun toQueryBuilder(queryBuilder: QueryBuilder) = queryBuilder {
    append("DISTINCT ON (", expr, ") TRUE")
  }
}

Sure that will result in your database selecting a TRUE per each row, but that's the only way I could think of.

[bastman]

@KennethWussmann thank you so much. Appending " TRUE" does the trick ;)

What do you think of this solution ? Works for me ...

fun customDistinctOn(vararg expressions: Expression<*>): CustomFunction<Boolean?> = CustomBooleanFunction(
        functionName = "DISTINCT ON",
        postfix = " TRUE",
        params = *expressions
)

fun CustomBooleanFunction(
        functionName: String, postfix: String = "", vararg params: Expression<*>
): CustomFunction<Boolean?> =
        object : CustomFunction<Boolean?>(functionName, BooleanColumnType(), *params) {
            override fun toQueryBuilder(queryBuilder: QueryBuilder) {
                super.toQueryBuilder(queryBuilder)
                if (postfix.isNotEmpty()) {
                    queryBuilder.append(postfix)
                }
            }
        }

Usage example:

        val query: Query = TweetsTable
                .slice(
                        // SELECT

                        // DISTINCT ON(tweet.message, tweet.\"comment\") TRUE
                        customDistinctOn(TweetsTable.message, TweetsTable.comment),

                        // , tweet.id, tweet.created_at, tweet.message, tweet.\"comment\" FROM tweet
                        *(TweetsTable.columns).toTypedArray() 
                )
                .select {
                    // WHERE tweet.created_at >= '1970-01-01 01:00:00.000000'"
                    TweetsTable.createdAt.greaterEq(Instant.EPOCH)
                }

[NikolayMetchev]

We have been using the workaround outline here but we get a failure down the line when we use the Entity.wrapRow(row: ResultRow, alias: QueryAlias) method for an aliased query that uses DISTINCT ON. Exposed fails on the following line in the file Entity.kt: val column = originalColumns.single { exp.table.delegate == it.table && exp.name == it.name } because the TRUE column that was added to the query isn't recognized.

[NikolayMetchev]

Also upgrading to the latest 0.28.1 breaks this workaround. We get the following exception

Column not found in original table
java.lang.IllegalStateException: Column not found in original table
    at org.jetbrains.exposed.sql.QueryAlias.get(Alias.kt:73)
    at com.paxos.absledge.persistence.SettlementsTable.getFullyReservedGroups(SettlementsTable.kt:207)
    at com.paxos.absledge.manager.SettlementsManagerTests$1$31$2.invoke(SettlementsManagerTests.kt:502)
    at com.paxos.absledge.manager.SettlementsManagerTests$1$31$2.invoke(SettlementsManagerTests.kt:61)
    at com.paxos.db.DatabaseManager$transaction$1.invoke(db.kt:66)
    at com.paxos.db.DatabaseManager$transaction$1.invoke(db.kt:42)
    at org.jetbrains.exposed.sql.transactions.ThreadLocalTransactionManagerKt$inTopLevelTransaction$1.invoke(ThreadLocalTransactionManager.kt:170)
    at org.jetbrains.exposed.sql.transactions.ThreadLocalTransactionManagerKt$inTopLevelTransaction$2.invoke(ThreadLocalTransactionManager.kt:211)
    at org.jetbrains.exposed.sql.transactions.ThreadLocalTransactionManagerKt.keepAndRestoreTransactionRefAfterRun(ThreadLocalTransactionManager.kt:219)
    at org.jetbrains.exposed.sql.transactions.ThreadLocalTransactionManagerKt.inTopLevelTransaction(ThreadLocalTransactionManager.kt:210)
    at org.jetbrains.exposed.sql.transactions.ThreadLocalTransactionManagerKt$transaction$1.invoke(ThreadLocalTransactionManager.kt:148)
    at org.jetbrains.exposed.sql.transactions.ThreadLocalTransactionManagerKt.keepAndRestoreTransactionRefAfterRun(ThreadLocalTransactionManager.kt:219)
    at org.jetbrains.exposed.sql.transactions.ThreadLocalTransactionManagerKt.transaction(ThreadLocalTransactionManager.kt:120)
    at org.jetbrains.exposed.sql.transactions.ThreadLocalTransactionManagerKt.transaction(ThreadLocalTransactionManager.kt:118)

[frynet]

Here's the DISTINCT ON for multiple columns version:

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

class DistinctOn<T>(expr: Column<T>, columns: Array<out Column<*>>) : Function<T>(expr.columnType) {

    private val distinctNames = listOf(expr, *columns)
        .joinToString(
            separator = ", ",
            transform = {
                "${it.table.tableName}.${it.name}"
            }
        )

    private val colName = expr.table.tableName + "." + expr.name

    override fun toQueryBuilder(queryBuilder: QueryBuilder) {
        queryBuilder {
            append(" DISTINCT ON ($distinctNames) $colName ")
        }
    }
}