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
            }
        

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)

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: 05 December 2024