Relationships

Many-to-one reference

A many-to-one reference is a relationship between two database tables where multiple rows in one table (the "child" table) can reference a single row in another table (the "parent" table).

Consider the following UsersTable and its corresponding entity:

object UsersTable : IntIdTable() {
    val name = varchar("name", MAX_USER_NAME_LENGTH)
}

class UserEntity(id: EntityID<Int>) : IntEntity(id) {
    companion object : IntEntityClass<UserEntity>(UsersTable)

    val ratings by UserRatingEntity referrersOn UserRatingsTable.user orderBy UserRatingsTable.value

Assume you want to add another table, UserRatingsTable, to store user ratings for a particular film. Each rating is associated with a user, but a user can be associated with many ratings. This is a many-to-one relationship.

You can implement this relationship by using a reference column in the child table (UserRatingsTable) that links to the parent table (UsersTable). To create a reference column, use the reference() function:

object UserRatingsTable : IntIdTable() {
    val value = long("value")
    val film = reference("film", StarWarsFilmsTable)
    val user = reference("user", UsersTable)
}

class UserRatingEntity(id: EntityID<Int>) : IntEntity(id) {
    companion object : IntEntityClass<UserRatingEntity>(UserRatingsTable)

    var value by UserRatingsTable.value
    var film by StarWarsFilmEntity referencedOn UserRatingsTable.film // use referencedOn for normal references
    var user by UserEntity referencedOn UserRatingsTable.user
}

val film = filmRating.film

class StarWarsFilmEntity(id: EntityID<Int>) : IntEntity(id) {
    companion object : IntEntityClass<StarWarsFilmEntity>(StarWarsFilmsTable)

    var sequelId by StarWarsFilmsTable.sequelId
    var name by StarWarsFilmsTable.name
    var director by StarWarsFilmsTable.director
    val ratings by UserRatingEntity referrersOn UserRatingsTable.film // make sure to use val and referrersOn
}

val filmRatings = starWarsFilm.ratings

class UserWithSingleRatingEntity(id: EntityID<Int>) : IntEntity(id) {
    companion object : IntEntityClass<UserWithSingleRatingEntity>(UsersTable)

    var name by UsersTable.name
    val rating by UserRatingEntity backReferencedOn UserRatingsTable.user // make sure to use val and backReferencedOn
}

user1.rating // returns a UserRating object

Optional reference

In Exposed, you can also add an optional reference.

For example, if you want to include anonymous user ratings to your table, you can do so by setting the reference field as optional using optReference() in your table and optionalReferencedOn in your entity definition:

object UserRatingsWithOptionalUserTable : IntIdTable() {
    val value = long("value")
    val film = reference("film", StarWarsFilmsTable)
    val user = optReference("user", UsersTable)
}

class UserRatingWithOptionalUserEntity(id: EntityID<Int>) : IntEntity(id) {
    companion object : IntEntityClass<UserRatingWithOptionalUserEntity>(UserRatingsWithOptionalUserTable)

    var value by UserRatingsWithOptionalUserTable.value
    var film by StarWarsFilmEntity referencedOn UserRatingsWithOptionalUserTable.film // use referencedOn for normal references
    var user by UserEntity optionalReferencedOn UserRatingsWithOptionalUserTable.user
}

Ordered reference

You can define the order in which referenced entities appear using orderBy:

class UserEntity(id: EntityID<Int>) : IntEntity(id) {
    companion object : IntEntityClass<UserEntity>(UsersTable)

    var name by UsersTable.name
    val ratings by UserRatingEntity referrersOn UserRatingsTable.user orderBy UserRatingsTable.value
}

In a more complex scenario, you can specify multiple columns along with the corresponding sort order for each:

class UserOrderedEntity(id: EntityID<Int>) : IntEntity(id) {
    companion object : IntEntityClass<UserOrderedEntity>(UsersTable)

    var name by UsersTable.name
    val ratings by UserRatingEntity referrersOn UserRatingsTable.user orderBy listOf(
        UserRatingsTable.value to SortOrder.DESC,
        UserRatingsTable.id to SortOrder.ASC
    )
}

Without using the infix notation, the orderBy method is chained after referrersOn:

class UserOrderedEntity(id: EntityID<Int>) : IntEntity(id) {
    companion object : IntEntityClass<UserOrderedEntity>(UsersTable)

    var name by UsersTable.name
    val ratings by UserRating.referrersOn(UserRatings.user).orderBy(
        UserRatings.value to SortOrder.DESC,
        UserRatings.id to SortOrder.ASC
    )
}

Many-to-many reference

A many-to-many reference is a relationship between two database tables where multiple records in one table are related to multiple records in another table. This type of relationship is modeled by using an intermediate table to link the two tables.

Consider the following table, ActorsTable, and it's corresponding entity:

object ActorsTable : IntIdTable() {
    val firstname = varchar("firstname", MAX_NAME_LENGTH)
    val lastname = varchar("lastname", MAX_NAME_LENGTH)
}

class ActorEntity(id: EntityID<Int>) : IntEntity(id) {
    companion object : IntEntityClass<ActorEntity>(ActorsTable)

    var firstname by ActorsTable.firstname
    var lastname by ActorsTable.lastname
}

Suppose you now want to extend this table to include a reference of to the StarWarsFilmEntity class. To achieve this, you can create an additional intermediate table to store the references:

object StarWarsFilmActorsTable : Table() {
    val starWarsFilm = reference("starWarsFilm", StarWarsFilmsTable)
    val actor = reference("actor", ActorsTable)
    override val primaryKey = PrimaryKey(starWarsFilm, actor, name = "PK_StarWarsFilmActors_swf_act") // PK_StarWarsFilmActors_swf_act is optional here
}

Add a reference to the ActorEntity in the StarWarsFilmEntity using the via function:

var actors by ActorEntity via StarWarsFilmActorsTable

The final StarWarsFilmEntity will look in the following way:

class StarWarsFilmEntity(id: EntityID<Int>) : IntEntity(id) {
    companion object : IntEntityClass<StarWarsFilmEntity>(StarWarsFilmsTable)

    var sequelId by StarWarsFilmsTable.sequelId
    var name by StarWarsFilmsTable.name
    var director by StarWarsFilmsTable.director
    val ratings by UserRatingEntity referrersOn UserRatingsTable.film // make sure to use val and referrersOn
    var actors by ActorEntity via StarWarsFilmActorsTable
}

Parent-Child reference

A parent-child reference is very similar to a many-to-many relationship, but an intermediate table contains both references to the same table.

A parent-child relationship can represent hierarchical data, such as a series of films and their directors. For example, you may want to track how directors oversee multiple Star Wars films, including sequels or spin-offs. For this, you would create a self-referencing intermediate table to define the relationships between a parent film (original) and its child films (sequels or spin-offs):

object StarWarsFilmRelationsTable : Table() {
    val parentFilm = reference("parent_film_id", StarWarsFilmsWithDirectorTable)
    val childFilm = reference("child_film_id", StarWarsFilmsWithDirectorTable)
    override val primaryKey = PrimaryKey(parentFilm, childFilm, name = "PK_FilmRelations")
}

In this example, parentFilm represents the original film, whereas childFilm represents a sequel, prequel, or spin-off. As you can see StarWarsFilmRelationsTable columns target only StarWarsFilmsWithDirectorTable.

You then need to update the entity class to include relationships for parent and child films using the via function:

class StarWarsFilmWithParentAndChildEntity(id: EntityID<Int>) : IntEntity(id) {
    companion object : IntEntityClass<StarWarsFilmWithParentAndChildEntity>(StarWarsFilmsWithDirectorTable)

    var name by StarWarsFilmsWithDirectorTable.name
    var director by DirectorEntity referencedOn StarWarsFilmsWithDirectorTable.director

    // Define hierarchical relationships
    var sequels by StarWarsFilmWithParentAndChildEntity.via(StarWarsFilmRelationsTable.parentFilm, StarWarsFilmRelationsTable.childFilm)
    var prequels by StarWarsFilmWithParentAndChildEntity.via(StarWarsFilmRelationsTable.childFilm, StarWarsFilmRelationsTable.parentFilm)
}

Here’s how you can create and query the parent-child hierarchy for StarWarsFilmsTable:

val director1 = DirectorEntity.new {
    name = "George Lucas"
    genre = Genre.SCI_FI
}

val film1 = StarWarsFilmWithParentAndChildEntity.new {
    name = "Star Wars: A New Hope"
    director = director1
}

val film2 = StarWarsFilmWithParentAndChildEntity.new {
    name = "Star Wars: The Empire Strikes Back"
    director = director1
}

val film3 = StarWarsFilmWithParentAndChildEntity.new {
    name = "Star Wars: Return of the Jedi"
    director = director1
}

// Assign parent-child relationships
film2.prequels = SizedCollection(listOf(film1)) // Empire Strikes Back is a sequel to A New Hope
film3.prequels = SizedCollection(listOf(film2)) // Return of the Jedi is a sequel to Empire Strikes Back
film1.sequels = SizedCollection(listOf(film2, film3)) // A New Hope has Empire Strikes Back as a sequel
film2.sequels = SizedCollection(listOf(film3)) // Empire Strikes Back has Return of the Jedi as a sequel

Composite primary key reference

In some database schemas, a composite primary key is used to uniquely identify rows by combining multiple columns. Here's how you can reference composite ID tables.

Assume that you have the following CompositeIdTable and its relevant entity:

object DirectorsCompositeIdTable : CompositeIdTable() {
    val name = varchar("name", NAME_LENGTH).entityId()
    val guildId = uuid("guild_id").autoGenerate().entityId()
    val genre = enumeration<Genre>("genre")

    override val primaryKey = PrimaryKey(name, guildId)
}

class DirectorCompositeIDEntity(id: EntityID<CompositeID>) : CompositeEntity(id) {
    companion object : CompositeEntityClass<DirectorCompositeIDEntity>(DirectorsCompositeIdTable)

    var genre by DirectorsCompositeIdTable.genre
}

We can refactor the StarWarsFilmsTable table to reference this table by adding columns to hold the appropriate primary key values and creating a table-level foreign key constraint:

object StarWarsFilmsWithCompositeRefTable : IntIdTable() {
    val sequelId = integer("sequel_id").uniqueIndex()
    val name = varchar("name", MAX_VARCHAR_LENGTH)
    val directorName = varchar("director_name", MAX_VARCHAR_LENGTH)
    val directorGuildId = uuid("director_guild_id")

    init {
        foreignKey(directorName, directorGuildId, target = DirectorsCompositeIdTable.primaryKey)
    }
}

Then, add the field to the entity using the referencedOn function:

class StarWarsFilmWithCompositeRefEntity(id: EntityID<Int>) : IntEntity(id) {
    companion object : IntEntityClass<StarWarsFilmWithCompositeRefEntity>(StarWarsFilmsWithCompositeRefTable)

    var sequelId by StarWarsFilmsWithCompositeRefTable.sequelId
    var name by StarWarsFilmsWithCompositeRefTable.name
    var director by DirectorCompositeIDEntity referencedOn StarWarsFilmsWithCompositeRefTable
}

tip

For more information on creating table foreign key constraints, see the Foreign Key constraint section.

Now you can get the director for a StarWarsFilm object, movie, in the same way you would get any other field:

movie.director // returns a Director object

If you wanted to get all the films made by a director, you could add a referrersOn field to the DirectorCompositeIDEntity class:

class DirectorCompositeIDEntity(id: EntityID<CompositeID>) : CompositeEntity(id) {
    companion object : CompositeEntityClass<DirectorCompositeIDEntity>(DirectorsCompositeIdTable)

    var genre by DirectorsCompositeIdTable.genre
    val films by StarWarsFilmWithCompositeRefEntity referrersOn StarWarsFilmsWithCompositeRefTable
}

You can then access this field on a DirectorCompositeIDEntity object, director:

director.films // returns all StarWarsFilm objects that reference this director

Using other previously mentioned infix functions, like optionalReferencedOn, backReferencedOn, and optionalReferrersOn, is also supported for referencing or referenced CompositeEntity objects, by using the respective overloads that accept an IdTable as an argument. These overloads will automatically resolve the foreign key constraint associated with the composite primary key.

Eager Loading

References in Exposed are lazily loaded, meaning queries to fetch the data for the reference are made at the moment the reference is first utilised. In cases where you know you will require references ahead of time, Exposed can eager load them at the time of the parent query. This is preventing the classic "N+1" problem as references can be aggregated and loaded in a single query.

To eager load a reference, use the .load() function and pass the DAO's reference as a KProperty:

UserEntity.findById(1)?.load(UserEntity::ratings)

This works for references of references. For example, if UserRatingTable had a film reference you could do the following:

UserEntity.findById(1)?.load(UserEntity::ratings, UserRatingEntity::film)

note

References that are eagerly loaded are stored inside the transaction cache. This means that they are not available in other transactions and thus, must be loaded and referenced inside the same transaction. Enabling keepLoadedReferencesOutOfTransaction in DatabaseConfig will allow getting referenced values outside the transaction block.

UserEntity.all().with(UserEntity::ratings)

object StarWarsFilmsTable : Table() {
    //...
    val description = text("name", eagerLoading=true)
}