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)
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
}
Accessing data
You can retrieve the film for a UserRatingEntity object in the same way you would get any other field:
val film = filmRating.film
Reverse access
If you wanted to get all the ratings for a film, you could do that by using the .find() function of the entity class. However, it is much easier to add a referrersOn field to the class representing the film, StarWarsFilmEntity:
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
Back reference
If each user rates only one film, you can define a backReferencedOn field to 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 // 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 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 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
}
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:
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: