Skip to main content
version 1.0.0

ORM: Relationships

Introduction​

Database tables are often related to one another. For example, a blog post may have many comments or an order could be related to the user who placed it. Athenna makes managing and working with these relationships easy, and supports a variety of common relationships:

Defining relationships​

Athenna relationships are defined in the static schema method using the Relation class in your model classes. Let's learn how to define each type of relationship supported by Athenna.

Has One​

A one-to-one relationship is a very basic type of database relationship. For example, a User model might be associated with one Phone model. To define this relationship, we will place a phone property in the schema method on the User model. The phone property should call the hasOne method from the Relation class:

import { Phone } from '#app/Models/Phone'
import { Model, Column, Relation } from '@athenna/database'

export class User extends Model {
  static schema() {
    return {
      id: Column.autoIncrementedInt(),
      name: Column.string(),
      phoneId: Column.integer('phone_id'),
      phone: Relation.hasOne(Phone, 'user'),
      createdAt: Column.createdAt(),
      updatedAt: Column.updatedAt(),
      deletedAt: Column.deletedAt(),
    }
  }

  /*...*/
}

The first argument passed to the hasOne method is the related model class and the second is the inverse side of the relation in the Phone class. Once the relationship is defined, we may retrieve the related record using the with method of the model query builder, the load method of the model instance or the relationships model queries:

Using with method
const user = await User.query().with('phone').find()
const phone = user.phone
Using load method
const user = await User.find()

await user.load('phone')

const phone = user.phone
Using relationships model queries
const user = await User.find()

const phone = await user.phoneQuery().find()

Athenna determines the foreign key of the relationship based on the parent model name. In this case, the Phone model is automatically assumed to have a userId foreign key. If you wish to override this convention, you may construct your relation from scratch with the Relation class:

import { Phone } from '#app/Models/Phone'
import { Model, Column, Relation } from '@athenna/database'

export class User extends Model {
  static schema() {
    return {
      id: Column.autoIncrementedInt(),
      name: Column.string(),
      phoneId: Column.integer('phone_id'),
      phone: Relation.type('hasOne')
                      .model(Phone)
                      .inverseSide('user')
                      .foreignKey('user_id') // <-
                      .get(),
      createdAt: Column.createdAt(),
      updatedAt: Column.updatedAt(),
      deletedAt: Column.deletedAt(),
    }
  }

  /*...*/
}

Additionally, Athenna assumes that the foreign key should have a value matching the primary key column of the parent. In other words, Athenna will look for the value of the user's id column in the userId column of the Phone record. If you would like the relationship to use a primary key value other than id or your model's static getter primaryKey, you may pass a primaryKey method to the Relation class:

import { Phone } from '#app/Models/Phone'
import { Model, Column, Relation } from '@athenna/database'

export class User extends Model {
  static schema() {
    return {
      id: Column.autoIncrementedInt(),
      name: Column.string(),
      phoneId: Column.integer('phone_id'),
      phone: Relation.type('hasOne')
                      .model(Phone)
                      .inverseSide('user')
                      .primaryKey('id') // <-
                      .foreignKey('user_id')
                      .get(),
      createdAt: Column.createdAt(),
      updatedAt: Column.updatedAt(),
      deletedAt: Column.deletedAt(),
    }
  }

  /*...*/
}

Defining the inverse side of the has one​

So, we can access the Phone model from our User model. Next, let's define a relationship on the Phone model that will let us access the user that owns the phone. We can define the inverse of a hasOne relationship using the belongsTo method:

import { User } from '#app/Models/Phone'
import { Model, Column, Relation } from '@athenna/database'

export class Phone extends Model {
  static schema() {
    return {
      id: Column.autoIncrementedInt(),
      number: Column.string(),
      userId: Column.integer('user_id'),
      user: Relation.belongsTo(User, 'phone'),
      createdAt: Column.createdAt(),
      updatedAt: Column.updatedAt(),
      deletedAt: Column.deletedAt(),
    }
  }

  /*...*/
}

When invoking the with method with the user in first param, Athenna will attempt to find a User model that has an id which matches the userId column on the Phone model.

Athenna determines the foreign key name by examining the name of the relationship method and suffixing the method name with Id. So, in this case, Athenna assumes that the Phone model has a userId column. However, if the foreign key on the Phone model is not userId, you may build your relation from scratch to set it:

import { User } from '#app/Models/Phone'
import { Model, Column, Relation } from '@athenna/database'

export class Phone extends Model {
  static schema() {
    return {
      id: Column.autoIncrementedInt(),
      number: Column.string(),
      userId: Column.integer('user_id'),
      user: Relation.type('belongsTo')
                      .model(User)
                      .inverseSide('phone')
                      .foreignKey('phone_id') // <-
                      .get(),
      createdAt: Column.createdAt(),
      updatedAt: Column.updatedAt(),
      deletedAt: Column.deletedAt(),
    }
  }

  /*...*/
}

If the parent model does not use id as its primary key, or you wish to find the associated model using a different column, you may pass a primaryKey method to the Relation class:

import { User } from '#app/Models/Phone'
import { Model, Column, Relation } from '@athenna/database'

export class Phone extends Model {
  static schema() {
    return {
      id: Column.autoIncrementedInt(),
      number: Column.string(),
      userId: Column.integer('user_id'),
      user: Relation.type('belongsTo')
                      .model(User)
                      .inverseSide('phone')
                      .primaryKey('id') // <-
                      .foreignKey('phone_id')
                      .get(),
      createdAt: Column.createdAt(),
      updatedAt: Column.updatedAt(),
      deletedAt: Column.deletedAt(),
    }
  }

  /*...*/
}

Has Many​

A has many relationship is used to define relationships where a single model is the parent to one or more child models. For example, a blog post may have an infinite number of comments. Like all other Athenna ORM relationships, has many relationships are defined by placing the relation property in the schema method:

import { Comment } from '#app/Models/Comment'
import { Model, Column, Relation } from '@athenna/database'

export class Post extends Model {
  static schema() {
    return {
      id: Column.autoIncrementedInt(),
      title: Column.string(),
      comments: Relation.hasMany(Comment, 'post'),
      createdAt: Column.createdAt(),
      updatedAt: Column.updatedAt(),
      deletedAt: Column.deletedAt(),
    }
  }

  /*...*/
}

Remember, Athenna will automatically determine the proper foreign key column for the Comment model. By convention, Athenna will take the "camelCase" name of the parent model and suffix it with Id. So, in this example, Athenna will assume the foreign key column on the Comment model is postId.

Once the relationship is defined, we may retrieve the related record using the with method of the model query builder, the load method of the model instance or the relationships model queries:

Using with method
const post = await Post.query().with('comments').find()
const comments = post.comments

comments.forEach(comment => {
    //
})
Using load method
const post = await Post.find()

await post.load('user')

const user = post.user
Using relationships model queries
const post = await Post.find()

const user = await post.userQuery().find()

Like the hasOne method, you may also override the foreign and local keys by building the relationship from scratch:

static schema() {
    return {
      id: Column.autoIncrementedInt(),
      title: Column.string(),
      comments: Relation.type('hasMany')
                      .model(Comment)
                      .inverseSide('post')
                      .primaryKey('id') // <-
                      .foreignKey('post_id') // <-
                      .get(),
      createdAt: Column.createdAt(),
      updatedAt: Column.updatedAt(),
      deletedAt: Column.deletedAt(),
    }
}

Defining the inverse side of the has many​

Now that we can access all of a post's comments, let's define a relationship to allow a comment to access its parent post. To define the inverse of a hasMany relationship, define a relationship property on the child model schema which calls the belongsTo method:

import { Post } from '#app/Models/Post'
import { Model, Column, Relation } from '@athenna/database'

export class Comment extends Model {
  static schema() {
    return {
      id: Column.autoIncrementedInt(),
      content: Column.string(),
      postId: Column.string(),
      post: Relation.belongsTo(Post, 'comments'),
      createdAt: Column.createdAt(),
      updatedAt: Column.updatedAt(),
      deletedAt: Column.deletedAt(),
    }
  }

  /*...*/
}

Once the relationship has been defined, we can retrieve a comment's parent post using the with method:

import { Comment } from '#app/Models/Comment'

const comment = await Comment.query().with('post').find()

console.log(comment.post.title)

In the example above, Athenna will attempt to find a Post model that has an id which matches the postId column on the Comment model.

Athenna determines the default foreign key name by examining the name of the relationship property and suffixing the method name with the name of the parent model's primary key column in "camelCase" style. So, in this example, Athenna will assume the Post model's foreign key on the comments table is postId.

However, if the foreign key for your relationship does not follow these conventions, you may create your belongsTo relation from scratch:

static schema() {
    return {
      id: Column.autoIncrementedInt(),
      content: Column.string(),
      postId: Column.string(),
      post: Relation.type('belongsTo')
                    .model(Post)
                    .inverseSide('comments')
                    .foreignKey('post_id') // <-
                    .get(),
      createdAt: Column.createdAt(),
      updatedAt: Column.updatedAt(),
      deletedAt: Column.deletedAt(),
    }
}

If your parent model does not use id as its primary key, or you wish to find the associated model using a different column, you may use the primaryKey method in the relation construction:

static schema() {
    return {
      id: Column.autoIncrementedInt(),
      content: Column.string(),
      postId: Column.string(),
      post: Relation.type('belongsTo')
                    .model(Post)
                    .inverseSide('comments')
                    .primaryKey('id') // <-
                    .foreignKey('post_id')
                    .get(),
      createdAt: Column.createdAt(),
      updatedAt: Column.updatedAt(),
      deletedAt: Column.deletedAt(),
    }
}

Belongs To Many​

Belongs to many relations are slightly more complicated than hasOne and hasMany relationships. An example of a belongs-to-many (many-to-many) relationship is a user that has many roles and those roles are also shared by other users in the application. For example, a user may be assigned the role of "Author" and "Editor"; however, those roles may also be assigned to other users as well. So, a user has many roles and a role has many users.

Table structure​

To define this relationship, three database tables are needed: users, roles, and role_user. The role_user table is derived from the alphabetical order of the related model names and contains userId and roleId columns. This table is used as an intermediate table linking the users and roles.

Remember, since a role can belong to many users, we cannot simply place a userId column on the roles table. This would mean that a role could only belong to a single user. In order to provide support for roles being assigned to multiple users, the role_user table is needed. We can summarize the relationship's table structure like so:

users
    id - integer
    name - string

roles
    id - integer
    name - string

roles_users
    userId - integer
    roleId - integer

Model structure​

Many-to-many relationships are defined by writing a property using the belongsToMany method from Relation class. For example, let's define a roles property on our User model schema. The first argument passed to this method is the model of the related table and the second argument is the inverse side of the relation in the Role model:

import { Role } from '#app/Models/Role'
import { Model, Column, Relation } from '@athenna/database'

export class User extends Model {
  static schema() {
    return {
      id: Column.autoIncrementedInt(),
      name: Column.string(),
      roles: Relation.belongsToMany(Role, 'users'),
      createdAt: Column.createdAt(),
      updatedAt: Column.updatedAt(),
      deletedAt: Column.deletedAt(),
    }
  }

  /*...*/
}

Once the relationship is defined, we may retrieve the related record using the with method of the model query builder, the load method of the model instance or the relationships model queries:

Using with method
const user = await User.query().with('roles').find()
const roles = user.roles

roles.forEach(role => {
    //
})
Using load method
const user = await User.find()

await user.load('roles')

const roles = user.roles
Using relationships model queries
const user = await User.find()
const roles = await user.rolesQuery().findMany()

To determine the table name of the relationship's intermediate table, Athenna will join the two related model names in alphabetical order. However, you are free to override this convention. You may do so by passing a third argument to the belongsToMany method:

static schema() {
    return {
      id: Column.autoIncrementedInt(),
      name: Column.string(),
      roles: Relation.belongsToMany(Role, 'users', 'users_roles'),
      createdAt: Column.createdAt(),
      updatedAt: Column.updatedAt(),
      deletedAt: Column.deletedAt(),
    }
}

In addition to customizing the name of the intermediate table, you may also customize the column names of the keys on the table by build the many-to-many relation from scratch. The pivotLocalForeignKey method defines the foreign key name of the model on which you are defining the relationship, while the pivotRelationForeignKey method defines the foreign key name of the model that you are joining to:

static schema() {
    return {
      id: Column.autoIncrementedInt(),
      name: Column.string(),
      roles: Relation.type('belongsToMany')
                     .model(Role)
                     .inverseSide('users')
                     .pivotTable('users_roles')
                     .pivotLocalForeignKey('user_id')
                     .pivotRelationForeignKey('role_id')
                     .get(),
      createdAt: Column.createdAt(),
      updatedAt: Column.updatedAt(),
      deletedAt: Column.deletedAt(),
    }
}

Defining the inverse side of the belongs to many​

To define the "inverse" of a belongs-to-many relationship, you should define a property on the related model which also returns the result of the belongsToMany method. To complete our user / role example, let's define the users property on the Role model:

import { User } from '#app/Models/User'
import { Model, Column, Relation } from '@athenna/database'

export class Role extends Model {
  static schema() {
    return {
      id: Column.autoIncrementedInt(),
      name: Column.string(),
      users: Relation.belongsToMany(User, 'roles'),
      createdAt: Column.createdAt(),
      updatedAt: Column.updatedAt(),
      deletedAt: Column.deletedAt(),
    }
  }

  /*...*/
}

As you can see, the relationship is defined exactly the same as its User model counterpart with the exception of referencing the User model and a different inverse side. Since we're reusing the belongsToMany method, all the usual table and key customization options are available when defining the "inverse" of belongs-to-many relationships.

Retrieving pivot table columns​

As you have already learned, working with belongs-to-many relations requires the presence of an pivot table. Athenna provides some very helpful ways of interacting with this table. For example, let's assume our User model has many Role models that it is related to. After loading the relationship, using the with method we may access the intermediate table using the pivot attribute on the models:

const user = await User.query().with('roles').find()
const roles = user.roles

roles.forEach(role => console.log(role.pivot.id))

Notice that each Role model we retrieve is automatically assigned a pivot attribute. This attribute contains all the data representing the pivot table.

Querying relationships​

Eager loading​

As you can see, you can use the with method to eager load your relationships in your model. In addition, when you include a model you can make more queries using the relation model inside the father model.

For example, imagine a blog application in which a User model has many associated Post models. You may query the posts relationship and then use all the instance methods available from Post model like so:

const user = await User.query().with('posts').find()
const posts = user.posts

for (const post of posts) {
    post.name = 'The incredible Daniel Luna!'

    await post.save()
}

Sub-queries in relationships​

Also, the with method can receive a closure as second parameter where you can define a lot of queries for when including your relationships. In the example bellow we are going to load all the users and include the active posts with all it comments:

const user = await User.query()
    .with('posts', query => query.with('comments').where('active', 1))
    .findMany()

const activePosts = user.posts

activePosts.forEach(post => {
    const comments = post.comments

    //
})
tip

The query value in with closure will be an instance of ModelQueryBuilder but of the relation. So you can use all its methods too.

Loading nested relations​

Nested relations are loaded via dot notation. We are going to simplify the above example loading the relationships using dot notation:

const user = await User.query()
    .with('posts.comments', query => query.where('active', 1))
    .findMany()

Now your user constant will have this data structure:

{
    id: 1,
    name: 'João Lenon',
    email: 'joao.lenon@zenvia.com',
    posts: [{
        id: 1,
        title: 'Campelo and Hygor will be missed',
        active: 1,
        comments: [{
            id: 1,
            content: 'Yes :(',
            active: 1
        }]
    }]
}

Lazy eager loading​

To load relationships after you have already fetched the user data, you can use the load method. For example, to load related posts after already fetching a User:

const user = await User.find()

await user.load('posts')

The with method and load method have the same signature, this means that you can also use the dot notation and sub-queries when using it:

const user = await User.find()
const postsWithComments = await user.load('posts.comments', query => query.where('active', 1))

for (const post of user.posts) {
    //
}
tip

The only difference between with and load methods is that the load method will always return the lazily fetched data. As you can see in the above example we are saving the posts with comments loaded in the postsWithComments constant.

Querying relationships existence​

When retrieving model records, you may wish to limit your results based on the existence of a relationship. For example, imagine you want to retrieve all blog posts that have at least one comment. To do so, you may pass the name of the relationship to the has method:

// Retrieve all posts that have at least one comment
const posts = await Post.query().has('comments').findMany()

You may also specify an operator and count value to further customize the query:

// Retrieve all posts that have three or more comments
const posts = await Post.query().has('comments', '>=', 3).findMany()

Nested has statements may be constructed using "dot" notation. For example, you may retrieve all posts that have at least one comment that has at least one image:

// Retrieve posts that have at least one comment with images
const posts = await Post.query('comments.images').findMany()

If you need even more power, you may use the whereHas method to define additional query constraints on your has queries, such as inspecting the content of a comment:

// Retrieve posts with at least one comment containing words like code%
const posts = await Post.query()
    .whereHas('comments', query => query.whereLike('content', 'code%'))
    .findMany()

// Retrieve posts with at least ten comments containing words like code%
const posts = await Post.query()
    .whereHas('comments', query => query.whereLike('content', 'code%'), '>=', 10)
    .findMany()

Defining relationships queries in models​

As you can see you can use the with to eager load and load to lazy load your relationships. But you can also define a query for your relationship for your model, this will help a lot to automatically set values when making operations in your relationships.

Before going to the examples, lets check how we can define these methods in the father model. Let's use the classic example of User and Post:

import { Post } from '#app/Models/Post'
import { Model, Column, Relation } from '@athenna/database'

export class User extends Model {
  static schema() {
    return {
      id: Column.autoIncrementedInt(),
      name: Column.string(),
      posts: Relation.hasMany(Post, 'user'),
      createdAt: Column.createdAt(),
      updatedAt: Column.updatedAt(),
      deletedAt: Column.deletedAt(),
    }
  }

  postsQuery() {
      const withCriterias = true

      return this.hasMany(Post, withCriterias)
  }

  /*...*/
}
tip

The hasOne, hasMany, belongsTo and belongsToMany methods will always create a specific query builder for each type of relation that will extend the ModelQueryBuilder. This means that basically you can use all the ModelQueryBuilder methods in the relationships query builder.

The create method​

Athenna provides convenient methods for adding new models to relationships. For example, perhaps you need to add a new comment to a post. Instead of manually setting the postId attribute on the Comment model you may insert the comment using the relationship's create method:

const comment = new Comment()

comment.content = 'A new comment.'

const post = await Post.find()

await post.commentsQuery().create(comment)

The create method will automatically add the appropriate postId value to the new Comment model.

If you need to save multiple related models, you may use the createMany method:

const comment = new Comment()

comment.content = 'A new comment.'

const post = await Post.find()

// Notice that you can use the Comment model and a plain object
await post.commentsQuery().createMany([comment, { content: 'Other comment.'} ])

The create and createMany methods will persist the given model instances, but will not add the newly persisted models to any in-memory relationships that are already loaded onto the parent model. If you plan on accessing the relationship after using the create or createMany methods, you may wish to use the load method to reload the relationships:

await post.commentsQuery().create(comment)

await post.load('comments')

// All comments, including the newly saved comment
post.comments
tip

The save method saves the model and relationships recursively this means that you can change your relationships data and save it from the parent model:

const post = await Post.query().with('user').find()

post.user.name = 'Valmir Barbosa'
post.user.email = 'valmirphp@gmail.com'

await post.save()

The update method​

You can also use the update method to update your model using the query builder method:

const post = await Post.query().with('user').find()

await post.userQuery().update({ name: 'Valmir Barbosa', email: 'valmirphp@gmail.com' })

// As we have already loaded the 'user' relation in
// the first line, we can use the refresh method to
// reload the post model and the already loaded relations.
await post.refresh()
tip

You may also use the createOrUpdate method to create and update models on relationships.

Manipulating relationships with instance methods​

Instead of using relationships queries methods you can modify the relation directly in the parent model instance. In the example bellow the User model belongs to an Account, let's see how we can update the username using an instance of the Account model:

const account = await Account.query().with('user').find()

account.user.name = 'Daniel Luna'

await account.user.save()
tip

As you can see in the example above we are calling the save method in the user relationship of account. Always remember that all the instance methods of the models like save, load, refresh, etc... will be available in relationships instances too.

This is also applicable to a User model that has many Post:

const user = await User.find()

await user.load('posts')

user.posts[0].title = 'New title for the first post'

await user.posts[0].save()

Belongs to relationship​

If you would like to assign a child model to a new parent model, you may use the associate method. In this example, the User model defines a belongsTo relationship to the Account model. This associate method will set the foreign key on the child model:

const user = await User.find()
const account = await Account.find()

user.accountQuery().associate(account)

await user.save()

To remove a parent model from a child model, you may use the dissociate method. This method will set the relationship's foreign key to null and will not delete any model:

user.accountQuery().dissociate()

await user.save()

To remove the relationship and the parent model you can use the delete method:

await user.accountQuery().delete()

Many to many relationships​

Athenna also provides methods to make working with many-to-many relationships more convenient. For example, let's imagine a user can have many roles and a role can have many users. You may use the attach method to attach a role to a user by inserting a record in the relationship's pivot table:

const role = await Role.find()
const user = await User.find()

await user.rolesQuery().attach(role.id)

When attaching a relationship to a model, you may also pass an array of additional data to be inserted into the pivot table:

await user.rolesQuery().attach(role.id, { expiresAt: new Date() })

Sometimes it may be necessary to remove a role from a user. To remove a many-to-many relationship record, use the detach method. The detach method will delete the appropriate record out of the pivot table; however, both models will remain in the database:

// Detach a single role from the user
await user.rolesQuery().detach(role.id)

// Detach all roles from the user
await user.rolesQuery().detach()

To remove the relationship record from the pivot table and the roles you can use the delete method:

await user.rolesQuery().delete()

Using instance properties in many-to-many​

Instead of using relationships queries methods you can modify the relation directly in the parent model instance and call save method:

const user = await User.query().with('roles').find()
const role = new Role()

role.name = 'Admin'

user.roles.push(role)

await user.save()

You can also modify the values inside the array to update the roles:

const user = await User.query().with('roles').find()

user.roles = user.roles.map(role => {
    if (role.name === 'Admin') {
        role.name = 'Other name'
    }

    return role
})

await user.save()

To remove a role from the user you can simply remove it from the roles array:

const user = await User.query().with('roles').find()

// The filter function will leave only the roles
// that the name is not Admin
user.roles = user.roles.filter(role => role.name !== 'Admin')

await user.save()