This post explains how to use table aliases using Kotlin Exposed. Aliases are necessary when you want to join the same table multiple times. If you haven’t used Kotlin Exposed before, you can go here for an introduction: Kotlin Exposed - A lightweight SQL library.

In this example we have an application containing two tables: Message and User. The Message table has two references to the User table, one to model the 'fromUser' relationship and one for the 'toUser' relationship. The table definitions look as follows:

object UserTable: Table("user") {
    val id = long("id").autoIncrement()
    val name = varchar("owner_type", 64)

    override val primaryKey = PrimaryKey(id)
}

object MessageTable: Table("message") {
    val id = long("id").autoIncrement()
    val fromUser = long("from_user_id").references(UserTable.id, onDelete = ReferenceOption.CASCADE)
    val toUser = long("to_user_id").references(UserTable.id, onDelete = ReferenceOption.CASCADE)
    val timestamp = datetime("timestamp")
    val content = text("content")

    override val primaryKey = PrimaryKey(id)
}

You start with creating two aliases for the User table using the alias function on a Table object UserTable.alias("aliasName"). The parameter is the name that will be used in the generated SQL statement.

We can then join the MessageTable to these two alias tables as follows:

transaction {
    val fromUser = UserTable.alias("fromUser")
    val toUser = UserTable.alias("toUser")

    MessageTable
        .join(fromUser, JoinType.INNER, MessageTable.fromUser, fromUser[UserTable.id])
        .join(toUser, JoinType.INNER, MessageTable.toUser, toUser[UserTable.id])
        .selectAll()
        .forEach { row ->
            val from = row[fromUser[UserTable.name]]
            val to = row[toUser[UserTable.name]]
            val msg = row[MessageTable.content]
            println("A message from $from, to $to: $msg")
        }
}

// output:
A message from Hector, to Charlotte: Hello Charlotte, wanna meet for a date with me?...
A message from John, to Hector: Hector, stay away from my girlfriend?...

Note that normally you can read (or join) a column by simply referencing it as row[UserTable.name], but since we used an alias you have to get the column using the aliased table definition row[fromUser[UserTable.name]]

You can test it out yourself in this fully working example using H2 here: github.com/toefel18/kotlin-exposed-blog-alias

shadow-left