### Exposed 0.56.0 Help

# Relationships

## Many-to-one

Let's say you have this table:

object Users : IntIdTable() { val name = varchar("name", 50) } class User(id: EntityID<Int>) : IntEntity(id) { companion object : IntEntityClass<User>(Users) var name by Users.name }

And now you want to add a table referencing this table (and other tables!):

object UserRatings : IntIdTable() { val value = long("value") val film = reference("film", StarWarsFilms) val user = reference("user", Users) } class UserRating(id: EntityID<Int>) : IntEntity(id) { companion object : IntEntityClass<UserRating>(UserRatings) var value by UserRatings.value var film by StarWarsFilm referencedOn UserRatings.film // use referencedOn for normal references var user by User referencedOn UserRatings.user }

Now you can get the film for a `UserRating` object, `filmRating`, in the same way you would get any other field:

filmRating.film // returns a StarWarsFilm object

Now if you wanted to get all the ratings for a film, you could do that by using the `filmRating.find` function, but it is much easier to just add a `referrersOn` field to the `StarWarsFilm` class:

class StarWarsFilm(id: EntityID<Int>) : IntEntity(id) { companion object : IntEntityClass<StarWarsFilm>(StarWarsFilms) //... val ratings by UserRating referrersOn UserRatings.film // make sure to use val and referrersOn //... }

You can then access this field on a `StarWarsFilm` object, `movie`:

movie.ratings // returns all UserRating objects with this movie as film

Now imagine a scenario where a user only ever rates a single film. If you want to get the single rating for that user, you can add a `backReferencedOn` field to the `User` class to access the `UserRating` table data:

class User(id: EntityID<Int>) : IntEntity(id) { companion object : IntEntityClass<User>(Users) //... val rating by UserRating backReferencedOn UserRatings.user // make sure to use val and backReferencedOn }

You can then access this field on a `User` object, `user1`:

user1.rating // returns a UserRating object

## Optional reference

You can also add an optional reference:

object UserRatings : IntIdTable() { //... val secondUser = reference("second_user", Users).nullable() // this reference is nullable! //... } class UserRating(id: EntityID<Int>) : IntEntity(id) { companion object : IntEntityClass<UserRating>(UserRatings) //... var secondUser by User optionalReferencedOn UserRatings.secondUser // use optionalReferencedOn for nullable references //... }

Now `secondUser` will be a nullable field, and `optionalReferrersOn` should be used instead of `referrersOn` to get all the ratings for a `secondUser`.

class User(id: EntityID<Int>) : IntEntity(id) { companion object : IntEntityClass<User>(Users) //... val secondRatings by UserRating optionalReferrersOn UserRatings.secondUser // make sure to use val and optionalReferrersOn //... }

## Ordered reference

You can also define the order in which referenced entities appear:

class User(id: EntityID<Int>) : IntEntity(id) { companion object : IntEntityClass<User>(Users) //... val ratings by UserRating referrersOn UserRatings.user orderBy UserRatings.value //... }

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

class User(id: EntityID<Int>) : IntEntity(id) { companion object : IntEntityClass<User>(Users) //... val ratings by UserRating referrersOn UserRatings.user orderBy listOf(UserRatings.value to SortOrder.DESC, UserRatings.id to SortOrder.ASC) //... }

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

class User(id: EntityID<Int>) : IntEntity(id) { companion object : IntEntityClass<User>(Users) //... val ratings by UserRating.referrersOn(UserRatings.user) .orderBy(UserRatings.value to SortOrder.DESC, UserRatings.id to SortOrder.ASC) //... }

## Many-to-many reference

In some cases, a many-to-many reference may be required. Let's assume you want to add a reference to the following Actors table to the StarWarsFilm class:

object Actors : IntIdTable() { val firstname = varchar("firstname", 50) val lastname = varchar("lastname", 50) } class Actor(id: EntityID<Int>) : IntEntity(id) { companion object : IntEntityClass<Actor>(Actors) var firstname by Actors.firstname var lastname by Actors.lastname }

Create an additional intermediate table to store the references:

object StarWarsFilmActors : Table() { val starWarsFilm = reference("starWarsFilm", StarWarsFilms) val actor = reference("actor", Actors) override val primaryKey = PrimaryKey(starWarsFilm, actor, name = "PK_StarWarsFilmActors_swf_act") // PK_StarWarsFilmActors_swf_act is optional here }

Add a reference to `StarWarsFilm`:

class StarWarsFilm(id: EntityID<Int>) : IntEntity(id) { companion object : IntEntityClass<StarWarsFilm>(StarWarsFilms) //... var actors by Actor via StarWarsFilmActors //... }

Note: You can set up IDs manually inside a transaction like this:

transaction { // only works with UUIDTable and UUIDEntity StarWarsFilm.new (UUID.randomUUID()){ //... actors = SizedCollection(listOf(actor)) } }

## Parent-Child reference

Parent-child reference is very similar to many-to-many version, but an intermediate table contains both references to the same table. Let's assume you want to build a hierarchical entity which could have parents and children. Our tables and an entity mapping will look like

object NodeTable : IntIdTable() { val name = varchar("name", 50) } object NodeToNodes : Table() { val parent = reference("parent_node_id", NodeTable) val child = reference("child_user_id", NodeTable) } class Node(id: EntityID<Int>) : IntEntity(id) { companion object : IntEntityClass<Node>(NodeTable) var name by NodeTable.name var parents by Node.via(NodeToNodes.child, NodeToNodes.parent) var children by Node.via(NodeToNodes.parent, NodeToNodes.child) }

As you can see `NodeToNodes` columns target only `NodeTable` and another version of `via` function were used. Now you can create a hierarchy of nodes.

val root = Node.new { name = "root" } val child1 = Node.new { name = "child1" } child1.parents = SizedCollection(root) // assign parent val child2 = Node.new { name = "child2" } root.children = SizedCollection(listOf(child1, child2)) // assign children

## Composite primary key reference

Assuming that we have the following `CompositeIdTable`:

object Directors : CompositeIdTable("directors") { val name = varchar("name", 50).entityId() val guildId = uuid("guild_id").autoGenerate().entityId() val genre = enumeration<Genre>("genre") override val primaryKey = PrimaryKey(name, guildId) } class Director(id: EntityID<CompositeID>) : CompositeEntity(id) { companion object : CompositeEntityClass<Director>(Directors) var genre by Directors.genre }

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

object StarWarsFilms : IntIdTable() { val sequelId = integer("sequel_id").uniqueIndex() val name = varchar("name", 50) val directorName = varchar("director_name", 50) val directorGuildId = uuid("director_guild_id") init { foreignKey(directorName, directorGuildId, target = Directors.primaryKey) } } class StarWarsFilm(id: EntityID<Int>) : IntEntity(id) { companion object : IntEntityClass<StarWarsFilm>(StarWarsFilms) var sequelId by StarWarsFilms.sequelId var name by StarWarsFilms.name var director by Director referencedOn StarWarsFilms }

For more information on creating table foreign key constraints, see DSL Foreign Key.

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

Now if you wanted to get all the films made by a director, you could add a `referrersOn` field to the `Director` class:

class Director(id: EntityID<CompositeID>) : CompositeEntity(id) { companion object : CompositeEntityClass<Director>(Directors) var genre by Directors.genre val films by StarWarsFilm referrersOn StarWarsFilms }

You can then access this field on a `Director` 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. For scenarios wherefore you know you will require references ahead of time, Exposed can eager load them at the time of the parent query, this is prevents the classic "N+1" problem as references can be aggregated and loaded in a single query. To eager load a reference you can call the "load" function and pass the DAO's reference as a KProperty:

StarWarsFilm.findById(1).load(StarWarsFilm::actors)

This works for references of references also, for example if Actors had a rating reference you could:

StarWarsFilm.findById(1).load(StarWarsFilm::actors, Actor::rating)

Similarly, you can eagerly load references on Collections of DAO's such as Lists and SizedIterables, for collections you can use the with function in the same fashion as before, passing the DAO's references as KProperty's.

StarWarsFilm.all().with(StarWarsFilm::actors)

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. As of 0.35.1, enabling `keepLoadedReferencesOutOfTransaction` in `DatabaseConfig` will allow getting referenced values outside the transaction block.

### Eager loading for Text Fields

Some database drivers do not load text content immediately (for performance and memory reasons) which means that you can obtain the column value only within the open transaction.

If you desire to make content available outside the transaction, you can use the eagerLoading param when defining the DB Table.

object StarWarsFilms : Table() { //... val description = text("name", eagerLoading=true) }
Last modified: 30 October 2024