Joining tables

Join

For the join examples below, consider the following tables:

package org.example.tables

import org.jetbrains.exposed.dao.id.IntIdTable

object StarWarsFilmsIntIdTable : IntIdTable("star_wars_films_table") {
    val sequelId = integer("sequel_id").uniqueIndex()
    val name = varchar("name", MAX_VARCHAR_LENGTH)
    val director = varchar("director", MAX_VARCHAR_LENGTH)
}

package org.example.tables

import org.jetbrains.exposed.dao.id.IntIdTable

const val MAX_NAME_LENGTH = 50

object ActorsIntIdTable : IntIdTable("actors") {
    val sequelId = integer("sequel_id").uniqueIndex()
    val name = varchar("name", MAX_NAME_LENGTH)
}

package org.example.tables

import org.jetbrains.exposed.dao.id.IntIdTable

const val MAX_CHARACTER_NAME_LENGTH = 50

object RolesTable : IntIdTable() {
    val sequelId = integer("sequel_id")
    val actorId = reference("actor_id", ActorsIntIdTable)
    val characterName = varchar("name", MAX_CHARACTER_NAME_LENGTH)
}

In the following example,a join is used to count how many actors star in each movie:

        ActorsIntIdTable.join(
            StarWarsFilmsIntIdTable,
            JoinType.INNER,
            onColumn = ActorsIntIdTable.sequelId,
            otherColumn = StarWarsFilmsIntIdTable.sequelId
        )
            .select(ActorsIntIdTable.name.count(), StarWarsFilmsIntIdTable.name)
            .groupBy(StarWarsFilmsIntIdTable.name)

Instead of specifying onColumn and otherColumn, additionalConstraint can be used (and allows specifying other types of join conditions).

        ActorsIntIdTable.join(
            StarWarsFilmsIntIdTable,
            JoinType.INNER,
            additionalConstraint = { StarWarsFilmsIntIdTable.sequelId eq ActorsIntIdTable.sequelId }
        )
            .select(ActorsIntIdTable.name.count(), StarWarsFilmsIntIdTable.name)
            .groupBy(StarWarsFilmsIntIdTable.name)

When joining on a foreign key, the more concise innerJoin can be used:

        (ActorsIntIdTable innerJoin RolesTable)
            .select(RolesTable.characterName.count(), ActorsIntIdTable.name)
            .groupBy(ActorsIntIdTable.name)
            .toList()

This is equivalent to the following:

        ActorsIntIdTable.join(RolesTable, JoinType.INNER, onColumn = ActorsIntIdTable.id, otherColumn = RolesTable.actorId)
            .select(RolesTable.characterName.count(), ActorsIntIdTable.name)
            .groupBy(ActorsIntIdTable.name)
            .toList()

Union

You can combine the results of multiple queries using .union(...). Per the SQL specification, the queries must have the same number of columns, and not be marked for update. Subqueries may be combined when supported by the database.

        val lucasDirectedQuery =
            StarWarsFilmsIntIdTable.select(StarWarsFilmsIntIdTable.name).where { StarWarsFilmsIntIdTable.director eq "George Lucas" }

        val abramsDirectedQuery =
            StarWarsFilmsIntIdTable.select(StarWarsFilmsIntIdTable.name).where { StarWarsFilmsIntIdTable.director eq "J.J. Abrams" }

        val filmNames = lucasDirectedQuery.union(abramsDirectedQuery).map { it[StarWarsFilmsIntIdTable.name] }

Only unique rows are returned by default. Duplicates may be returned using .unionAll().

        val lucasDirectedQuery =
            StarWarsFilmsIntIdTable.select(StarWarsFilmsIntIdTable.name).where { StarWarsFilmsIntIdTable.director eq "George Lucas" }

        val originalTrilogyQuery =
            StarWarsFilmsIntIdTable.select(StarWarsFilmsIntIdTable.name).where { StarWarsFilmsIntIdTable.sequelId inList (3..5) }

        val allFilmNames = lucasDirectedQuery.unionAll(originalTrilogyQuery).map { it[StarWarsFilmsIntIdTable.name] }

Last modified: 05 December 2024