Exposed Advanced

Right now, we can work with entities and data access objects (DAO). However, entities rarely exist in isolation. Relationships often arise between them, so we need to learn how to manage these associations using Exposed.

Many-to-one reference

Suppose we are building a social network where users create posts and leave comments. The entity-relationship diagram looks like this:

First of all, we need to define the user 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
}

We also need a table for posts. Many posts can belong to one user, representing a many-to-one relationship. We can implement this by using a reference column that links to the Users table using the reference function:

object Posts : IntIdTable() {
    val title = varchar("title", 50)
    val content = varchar("content",  250)
    val author = reference("author", Users)
}

class Post(id: EntityID<Int>) : IntEntity(id) {
    companion object : IntEntityClass<Post>(Posts)

    var title by Posts.title
    var content by Posts.content
    var author by User referencedOn Posts.author
}

This is how we associate a post with its author. You can retrieve the author of a post just like any other field:

val user = User.new { name = "John Doe" }
val post = Post.new {
    title = "I'm learning Exposed!"
    content = "Lorem ipsum dolor sit amet"
    author = user
}
val author = post.author // returns a User object

If you want to retrieve all posts created by a specific user, you could use Post.find. However, it is much easier to add a referrersOn field to the User class to handle this one-to-many reverse access:

class User(id: EntityID<Int>) : IntEntity(id) {
    companion object : IntEntityClass<User>(Users)

    var name by Users.name
    val posts by Post referrersOn Posts.author // make sure to use val and referrersOn
}

It is important to understand that the posts field in the User class is populated automatically. Therefore, the field must be immutable because it is effectively read-only from the user's perspective.

The author field of the Post class, conversely, is set manually and written to the database, so it must be mutable.

It works as follows:

val user = User.new { name = "John Doe" }
repeat(3) {
    Post.new {
        title = "Learning Exposed day $it"
        content = "Lorem ipsum dolor sit amet"
        author = user
    }
}
println(user.posts.map { it.title }) // [Learning Exposed day 0, Learning Exposed day 1, Learning Exposed day 2]

One-to-one reference

The previous section handles cases where one user has many posts. However, sometimes a relationship is strictly one-to-one, or you simply want to access a single referenced entity from the reverse side.

For example, if we store user profiles in a separate table where each user has exactly one profile, we can use backReferencedOn instead of referrersOn:

object UserProfiles : IntIdTable() {
    val bio = text("bio")
    val user = reference("user", Users)
}

class UserProfile(id: EntityID<Int>) : IntEntity(id) {
    companion object : IntEntityClass<UserProfile>(UserProfiles)

    var bio by UserProfiles.bio
    var user by User referencedOn UserProfiles.user
}

class User(id: EntityID<Int>) : IntEntity(id) {
    companion object : IntEntityClass<User>(Users)

    /* ... */
    val profile by UserProfile backReferencedOn UserProfiles.user
}

Now, accessing user.profile returns a single UserProfile object rather than a collection.

Optional reference

Imagine that some posts have an editor, but not all:

To add an editor to a post, we can create an optional reference. In the table definition, we can use optReference:

object Posts : IntIdTable() {
    /* ... */
    val editor = optReference("editor", Users)
}

class Post(id: EntityID<Int>) : IntEntity(id) {
    /* ... */
    var editor by User optionalReferencedOn Posts.editor
}

The editor is now a nullable field. To retrieve all posts reviewed by a specific editor, use optionalReferrersOn instead of referrersOn:

class User(id: EntityID<Int>) : IntEntity(id) {
    /* ... */
    val reviews by Post optionalReferrersOn Posts.editor
}

Many-to-many reference

Let's imagine that posts can have multiple authors. To implement this, we need a many-to-many relationship:

We must create an intermediate PostsAuthors table and update the entities to use the via function:

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
    val posts by Post via PostsAuthors
}

object Posts : IntIdTable() {
    val title = varchar("title", 50)
    val content = varchar("content", 250)
}

class Post(id: EntityID<Int>) : IntEntity(id) {
    companion object : IntEntityClass<Post>(Posts)

    var title by Posts.title
    var content by Posts.content
    var authors by User via PostsAuthors
}

object PostsAuthors : Table() {
    val post = reference("post", Posts)
    val author = reference("author", Users)
}

This works as follows:

val alice = User.new { name = "Alice" }
val bob = User.new { name = "Bob" }
val post = Post.new {
    title = "Title"
    content = "Content"
    authors = SizedCollection(alice, bob)
}
println(post.authors.map { it.name }) // [Alice, Bob]

Parent-Child reference

A parent-child reference is similar to a many-to-many relationship, but the intermediate table contains references pointing to the same table. Let's assume you want to create a hierarchy of posts where a post can be a comment on a previous one:

Our tables and the entity mapping will look like this:

object Comment : Table() {
    val subject = reference("subject", Posts)
    val comment = reference("comment", Posts)
}

class Post(id: EntityID<Int>) : IntEntity(id) {
    /* ... */
    var subjects by Post.via(Comment.comment, Comment.subject)
    var comments by Post.via(Comment.subject, Comment.comment)
}

Here, via takes the source column as the first argument and the target column as the second. This allows us to link posts hierarchically:

val parentPost = Post.new {
    title = "Original Post"
    content = "Main content"
}

repeat(3) {
    val commentPost = Post.new {
        title = "Comment $it"
        content = "Lorem ipsum dolor sit amet"
    }
    // Link the comment to the parent subject
    commentPost.subjects = SizedCollection(parentPost)
}
println(parentPost.comments.map { it.title }) // [Comment 0, Comment 1, Comment 2]

Reference options

If we want to configure cascading behaviors between entities, we can specify parameters when creating the reference column. For example, if we want all posts to be deleted automatically when their author is deleted, we pass onDelete = ReferenceOption.CASCADE.

object PostsAuthors : Table() {
    val post = reference("post", Posts)
    val author = reference("author", Users, onDelete = ReferenceOption.CASCADE)
}

If we don't want to delete the posts, but rather set the author field to null, we can pass ReferenceOption.SET_NULL.

We can also prohibit deleting a user as long as they have created at least one post using ReferenceOption.RESTRICT or ReferenceOption.NO_ACTION

Conclusion

In this topic, we learned how to create various relationships between entities in the database. We are able to create one-to-many, many-to-many and parent-child relationships. We also covered how to control data integrity using reference options like CASCADE and SET_NULL.