Exposed: Add set operations

[acrane13]

It would be useful to have the set operations added:

union, union all, minus, intersect

I realize this can be handled with multiple select statements and then process the resulting lists, but they are more efficient in the database

[xJoeWoo]

Agree, I surprisingly found there's no union operation in Exposed.

[gronnbeck]

Is this being worked on? No movement on this issue for a long time. At the moment I drop into SQL to write UNION queries.

[NikolayMetchev]

Here is my attempt at UnionAll. turned out to be quite hairy. It can be improved slightly if Query.arguments() specifies it return type explicitly and matches the interface signature:

class UnionAll(vararg val queries: Query) : Query(queries[0].set, null) {
    override fun arguments(): List<ArrayList<Pair<IColumnType, Any?>>> {
        val allArgs = queries.map { it.arguments() }
        var answer: List<ArrayList<Pair<IColumnType, Any?>>> = listOf()
        allArgs.forEach {
            answer = answer.zipLongest(it) { a, b ->
                a + b
            }
        }
        return answer
    }

    override fun prepareSQL(transaction: Transaction): String =
        queries.joinToString( " UNION ALL ") { it.prepareSQL(transaction) }
}

private operator fun <T> Collection<T>?.plus(other: Collection<T>?) : ArrayList<T> {
    val result = ArrayList<T>(this?.size + other?.size)
    if (this != null) {
        result.addAll(this)
    }
    if (other != null) {
        result.addAll(other)
    }
    return result
}

private operator fun Int?.plus(other: Int?) = when {
    this == null && other == null -> 10
    this == null -> other!!
    other == null -> this
    else -> this + other
}

private inline fun <T, R, V> Iterable<T>.zipLongest(other: Iterable<R>, transform: (a: T?, b: R?) -> V): List<V> {
    val first = iterator()
    val second = other.iterator()
    val list = ArrayList<V>(minOf(collectionSizeOrDefault(10), other.collectionSizeOrDefault(10)))
    while (first.hasNext() || second.hasNext()) {
        if (first.hasNext() && second.hasNext()) {
            list.add(transform(first.next(), second.next()))
        } else if (first.hasNext()) {
            list.add(transform(first.next(), null))
        } else {
            list.add(transform(null, second.next()))
        }
    }
    return list
}

private fun <T> Iterable<T>.collectionSizeOrDefault(default: Int): Int = if (this is Collection<*>) this.size else default

[rogue-one]

any update on this?.. mysql doesn't support full outer join and I will have to rely on union operation to implement the full outer join.. lack of this operation basically means no full outer join in mysql as well ..

[edeak]

@Tapac Do you guys have any updates on this?

[Tapac]

UNION and UNION ALL supported