Relationships define how entities are associated with one another in your database schema and provide mechanisms to query and manipulate these associations. There are four ways entities could reference one another:
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)
var name by UsersTable.name
}
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)
}
In the corresponding entity class, use the referencedOn() infix function to register the reference:
class UserRatingEntity(id: EntityID<Int>) : IntEntity(id) {
companion object : IntEntityClass<UserRatingEntity>(UserRatingsTable)
var value by UserRatingsTable.value
var film by StarWarsFilmEntity referencedOn UserRatingsTable.film
var user by UserEntity referencedOn UserRatingsTable.user
}
class UserRatingEntity(id: EntityID<Int>) : IntEntity(id) {
companion object : IntEntityClass<UserRatingEntity>(UserRatingsTable)
var value by UserRatingsTable.value
val film by StarWarsFilmEntity referencedOn UserRatingsTable.film
val user by UserEntity referencedOn UserRatingsTable.user
}
With JDBC, referencedOn() returns the referenced entity directly, so properties are declared as var.
With R2DBC, referencedOn() return an accessor instead of the referenced entity, so properties are declared as val.
Accessing data
To retrieve the referenced film from a UserRatingEntity, use the syntax appropriate for the connectivity type:
val film = filmRating.film
val film = filmRating.film()
With JDBC, the property getter returns the referenced film directly.
With R2DBC, the property returns an accessor. You can retrieve the referenced entity by invoking it.
Reverse access
While you can use the .find() function of the entity class to get all ratings for a film, it is preferred to use the referrersOn() infix function on the class representing the 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
}
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
}
You can then access this field on an entity object:
val filmRatings = starWarsFilm.ratings
val filmRatings = starWarsFilm.ratings.toList()
Back reference
If each user rates only one film, you can use the backReferencedOn() infix function on the entity class to access the UserRatingsTable data:
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
}
You can then access this field on a UserWithSingleRatingEntity object:
user1.rating
user1.rating()
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 the optReference() function in your table definition:
object UserRatingsWithOptionalUserTable : IntIdTable() {
val value = long("value")
val film = reference("film", StarWarsFilmsTable)
val user = optReference("user", UsersTable)
}
In your entity definition, use and optionalReferencedOn to register the optional reference:
class UserRatingWithOptionalUserEntity(id: EntityID<Int>) : IntEntity(id) {
companion object : IntEntityClass<UserRatingWithOptionalUserEntity>(UserRatingsWithOptionalUserTable)
var value by UserRatingsWithOptionalUserTable.value
var film by StarWarsFilmEntity referencedOn UserRatingsWithOptionalUserTable.film
var user by UserEntity optionalReferencedOn UserRatingsWithOptionalUserTable.user
}
class UserRatingWithOptionalUserEntity(id: EntityID<Int>) : IntEntity(id) {
companion object : IntEntityClass<UserRatingWithOptionalUserEntity>(UserRatingsWithOptionalUserTable)
var value by UserRatingsWithOptionalUserTable.value
val film by StarWarsFilmEntity referencedOn UserRatingsWithOptionalUserTable.film
val user by UserEntity optionalReferencedOn UserRatingsWithOptionalUserTable.user
}
Ordered reference
You can define the order in which referenced entities appear using the orderBy() function:
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() function 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 its 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 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 looks 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)
}
class StarWarsFilmWithParentAndChildEntity(id: EntityID<Int>) : IntEntity(id) {
companion object : IntEntityClass<StarWarsFilmWithParentAndChildEntity>(StarWarsFilmsWithDirectorTable)
var name by StarWarsFilmsWithDirectorTable.name
val 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
val director1 = DirectorEntity.newSuspend {
name = "George Lucas"
genre = Genre.SCI_FI
}
val film1 = StarWarsFilmWithParentAndChildEntity.newSuspend {
name = "Star Wars: A New Hope"
director.set(director1)
}
val film2 = StarWarsFilmWithParentAndChildEntity.newSuspend {
name = "Star Wars: The Empire Strikes Back"
director.set(director1)
}
val film3 = StarWarsFilmWithParentAndChildEntity.newSuspend {
name = "Star Wars: Return of the Jedi"
director.set(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
}
You 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
}
class StarWarsFilmWithCompositeRefEntity(id: EntityID<Int>) : IntEntity(id) {
companion object : IntEntityClass<StarWarsFilmWithCompositeRefEntity>(StarWarsFilmsWithCompositeRefTable)
var sequelId by StarWarsFilmsWithCompositeRefTable.sequelId
var name by StarWarsFilmsWithCompositeRefTable.name
val director by DirectorCompositeIDEntity referencedOn StarWarsFilmsWithCompositeRefTable
}
With JDBC, you can access a referenced entity through the property directly. With R2DBC, reference properties use an accessor, so you must invoke the accessor to retrieve the referenced entity:
movie.director
movie.director()
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
director.films()
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:
To eagerly load references on Collections of DAO's such as List and SizedIterable, use the .with() function and pass each reference as KProperty:
UserEntity.all().with(UserEntity::ratings)
.with() eagerly loads references for all Entity instances in the SizedIterable returned by .all() and returns this collection.
Loading text fields
Some database drivers do not load text content immediately due to performance and memory reasons. This means that you can obtain the column value only within the open transaction.
To make content available outside the transaction, use the eagerLoading parameter in your field definition: