Relationships

Relationships are the backbone of data-driven applications, linking one model type to another.

For example, a User could have many Post relations, and each Post could have many Comment relations.

Lucid’s expressive API makes the process of associating and fetching model relations simple and intuitive, without the need to touch a SQL statement or even edit a SQL schema.

Basic Example

Let’s examine a scenario containing two models: User and Profile.

In our example, every User instance can have a single Profile.

We call this a one to one relationship.

Defining Relationship

To define this relationship, add the following method to your User model:

app/Models/User.js
const Model = use('Model')

class User extends Model {
  profile () {
    return this.hasOne('App/Models/Profile')
  }
}

module.exports = User

In the example above, we added a profile method to the User model returning a hasOne relationship typed to the Profile model.

If the Profile model does not exist, generate it:

> adonis make:model Profile
app/Models/Profile.js
const Model = use('Model')

class Profile extends Model {
}

module.exports = Profile
There is no need to define a relationship on both the models. Setting it one-way on a single model is all that’s required.

Fetching User Profile

Now we’ve defined the relationship between User and Profile, we can execute the following code to fetch a user’s profile:

const User = use('App/Models/User')

const user = await User.find(1)
const userProfile = await user.profile().fetch()

Has One

A hasOne relation defines a one to one relationship using a foreign key to the related model.

API

hasOne(relatedModel, primaryKey, foreignKey)
relatedModel

An IoC container reference to the model the current model has one of.

primaryKey

Defaults to the current model primary key (i.e. id).

foreignKey

Defaults to tableName_primaryKey of the current model. The singular form of the table name is used (for example, the foreign key user_id references the id column on the table users).

Defining Relation

app/Models/User.js
const Model = use('Model')

class User extends Model {
  profile () {
    return this.hasOne('App/Models/Profile')
  }
}

module.exports = User

Has Many

A hasMany relation defines a one to many relationship using a foreign key to the other related models.

API

hasMany(relatedModel, primaryKey, foreignKey)
relatedModel

An IoC container reference to the model the current model has many of.

primaryKey

Defaults to the current model primary key (i.e. id).

foreignKey

Defaults to tableName_primaryKey of the current model. The singular form of the table name is used (for example, the foreign key user_id references the id column on the table users).

Defining Relation

app/Models/User.js
const Model = use('Model')

class User extends Model {
  posts () {
    return this.hasMany('App/Models/Post')
  }
}

module.exports = User

Belongs To

The belongsTo relationship is the inverse of the hasOne relationship and is applied on the other end of the relation.

Continuing with our User and Profile example, the Profile model belongs to the User model, and thus has the belongsTo relationship defined on it.

API

belongsTo(relatedModel, primaryKey, foreignKey)
relatedModel

An IoC container reference to the model the current model belongs to.

primaryKey

Defaults to the related model foreign key (in our Profile belongs to User example, this would be user_id).

foreignKey

Defaults to the related model primary key.

Defining Relation

app/Models/Profile.js
const Model = use('Model')

class Profile extends Model {
  user () {
    return this.belongsTo('App/Models/User')
  }
}

module.exports = Profile

Belongs To Many

The belongsToMany relationship allows you to define many to many relationships on both the models.

For example:

  1. A User can have many Car models.

  2. A Car can have many User models (i.e. owners) during its lifespan.

As both User and Car can have many relations of the other model, we say that each model belongs to many of the other model.

When defining a belongsToMany relationship, we don’t store a foreign key on either of our model tables as we did for hasOne and hasMany relationships.

Instead, we must rely on a third, intermediary table called a pivot table.

You can create pivot tables using migration files.

API

belongsToMany(
  relatedModel,
  foreignKey,
  relatedForeignKey,
  primaryKey,
  relatedPrimaryKey
)
relatedModel

An IoC container reference to the model the current model has many of.

foreignKey

Defaults to the current model foreign key (in our User belongs to many Car example, this would be user_id).

relatedForeignKey

Defaults to the related model foreign key (in our User belongs to many Car example, this would be car_id).

primaryKey

Defaults to the current model primary key (i.e. id).

relatedPrimaryKey

Defaults to the related model primary key (i.e. id).

Defining Relation

app/Models/Car.js
const Model = use('Model')

class User extends Model {
  cars () {
    return this.belongsToMany('App/Models/Car')
  }
}

module.exports = User

In the example above, the table named car_user is the pivot table storing the unique relationship between Car and User model primary keys.

pivotTable

By default, pivot table names are derived by sorting lowercased related model names in alphabetical order and joining them with a _ character (e.g. User + Car = car_user).

To set a custom pivot table name, call pivotTable in the relationship definition:

cars () {
  return this
    .belongsToMany('App/Models/Car')
    .pivotTable('user_cars')
}

withTimestamps

By default, pivot tables aren’t assumed to have timestamps.

To enable timestamps, call withTimestamps in the relationship definition:

cars () {
  return this
    .belongsToMany('App/Models/Car')
    .withTimestamps()
}

withPivot

By default, only foreign keys are returned from a pivot table.

To return other pivot table fields, call withPivot in the relationship definition:

cars () {
  return this
    .belongsToMany('App/Models/Car')
    .withPivot(['is_current_owner'])
}

pivotModel

For more control over queries made to a pivot table, you can bind a pivot model:

cars () {
  return this
    .belongsToMany('App/Models/Car')
    .pivotModel('App/Models/UserCar')
}
app/Models/UserCar.js
const Model = use('Model')

class UserCar extends Model {
  static boot () {
    super.boot()
    this.addHook('beforeCreate', (userCar) => {
      userCar.is_current_owner = true
    })
  }
}

module.exports = UserCar

In the example above, UserCar is a regular Lucid model.

With a pivot model assigned, you can use lifecycle hooks, getters/setters, etc.

After calling pivotModel you cannot call the pivotTable and withTimestamps methods. Instead, you are required to set those values on the pivot model itself.

Many Through

The manyThrough relationship is a convenient way to define an indirect relation.

For example:

  1. A User belongs to a Country.

  2. A User has many Post models.

Using manyThrough, you can fetch all Post models for a given Country.

API

manyThrough(
  relatedModel,
  relatedMethod,
  primaryKey,
  foreignKey
)
relatedModel

An IoC container reference to the model the current model needs access through to reach the indirectly related model.

relatedMethod

The relationship method called on relatedModel to fetch the indirectly related model results through.

primaryKey

Defaults to the current model primary key (i.e. id).

foreignKey

Defaults to the foreign key for the current model (in our Posts through Country example, this would be country_id).

Defining Relations

The relationships need defining on both primary and intermediary models.

Continuing with our Posts through Country example, let’s define the required hasMany relationship on the intermediary User model:

app/Models/User.js
const Model = use('Model')

class User extends Model {
  posts () {
    return this.hasMany('App/Models/Post')
  }
}

Finally, define the manyThrough relationship on the primary Country model:

app/Models/Country.js
const Model = use('Model')

class Country extends Model {
  posts () {
    return this.manyThrough('App/Models/User', 'posts')
  }
}

In the example above, the second parameter is a reference to the posts method on the User model.

The relatedMethod parameter must always be passed to the manyThrough method for a many through relationship to work.

Querying Data

Querying related data is greatly simplified by Lucid’s intuitive API, providing a consistent interface for all types of model relationships.

If a User has many Post models, we can fetch all posts for user id=1 like so:

const User = use('App/Models/User')

const user = await User.find(1)
const posts = await user.posts().fetch()

Add runtime constraints by calling Query Builder methods like a typical query:

const user = await User.find(1)

// published posts
const posts = await user
  .posts()
  .where('is_published', true)
  .fetch()

The above example fetches all published posts for user id=1.

Querying Pivot Table

You can add where clauses for belongsToMany pivot tables like so:

const user = await User.find(1)

const cars = await user
  .cars()
  .wherePivot('is_current_owner', true)
  .fetch()

The above example fetches all cars where their current owner is user id=1.

The methods whereInPivot and orWherePivot are also available.

Eager Loading

When you want to fetch relations for more than one base relation (e.g. posts for more than one user), eager loading is the preferred way to do so.

Eager loading is the concept of fetching relationships with the minimum database queries possible in an attempt to avoid the n+1 problem.

Without eager loading, using the techniques discussed previously in this section:

Not Recommended
const User = use('App/Models/User')

const users = await User.all()
const posts = []

for (let user of users) {
  const userPosts = await user.posts().fetch()
  posts.push(userPosts)
}

The above example makes n+1 queries to the database, where n is the number of users. Looping through a large number of users would result in a large sequence of queries made to the database, which is hardly ideal!

With eager loading, only 2 queries are required to fetch all users and their posts:

Recommended
const User = use('App/Models/User')

const users = await User
  .query()
  .with('posts')
  .fetch()

The with method eager loads the passed relation as part of the original payload, so running users.toJSON() will now return an output like so:

JSON Output
[
  {
    id: 1,
    username: 'virk',
    posts: [{
      id: 1,
      user_id: 1,
      title: '...'
    }]
  }
]

In the JSON output above, each User object now has a posts relationship property, making it easy to spot at a glance which Post belongs to which User.

Adding Runtime Constraints

Add runtime constraints to eager loaded relationships like so:

const users = await User
  .query()
  .with('posts', (builder) => {
    builder.where('is_published', true)
  })
  .fetch()

Loading Multiple Relations

Multiple relations can be loaded by chaining the with method:

const users = await User
  .query()
  .with('posts')
  .with('profile')
  .fetch()

Loading Nested Relations

Nested relations are loaded via dot notation.

The following query loads all User posts and their related comments:

const users = await User
  .query()
  .with('posts.comments')
  .fetch()

Nested relation constraint callbacks apply only to the last relation:

const users = await User
  .query()
  .with('posts.comments', (builder) => {
    builder.where('approved', true)
  })
  .fetch()

In the example above, the builder.where clause is only applied to the comments relationship (not the posts relationship).

To add a constraint to the first relation, use the following approach:

const users = await User
  .query()
  .with('posts', (builder) => {
    builder.where('is_published', true)
      .with('comments')
  })
  .fetch()

In the example above, a where constraint is added to the posts relation while eager loading posts.comments at the same time.

Retrieving loaded models data

To retrieve the loaded data you must call the getRelated method:

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

const posts = user.getRelated('posts')

Lazy Eager Loading

To load relationships after already fetching data, use the load method.

For example, to load related posts after already fetching a User:

const user = await User.find(1)
await user.load('posts')

You can lazily load multiple relationships using the loadMany method:

const user = await User.find(1)
await user.loadMany(['posts', 'profiles'])

To set query constraints via loadMany you must pass an object:

const user = await User.find(1)
await user.loadMany({
  posts: (builder) => builder.where('is_published', true),
  profiles: null
})

Retrieving loaded models data

To retrieve the loaded data you must call the getRelated method:

const user = await User.find(1)
await user.loadMany(['posts', 'profiles'])

const posts = user.getRelated('posts')
const profiles = user.getRelated('profiles')

Filtering Data

Lucid’s API makes it simple to filter data depending on a relationship’s existence.

Let’s use the classic example of finding all posts with comments.

Here’s our Post model and its comments relationship definition:

app/Models/Post.js
const Model = use('Model')

class Post extends Model {
  comments () {
    return this.hasMany('App/Models/Comments')
  }
}

has

To only retrieve posts with at least one Comment, chain the has method:

const posts = await Post
  .query()
  .has('comments')
  .fetch()

It’s that simple! 😲

Add an expression/value constraint to the has method like so:

const posts = await Post
  .query()
  .has('comments', '>', 2)
  .fetch()

The above example will only retrieve posts with more than 2 comments.

whereHas

The whereHas method is similar to has but enables more specific constraints.

For example, to fetch all posts with at least 2 published comments:

const posts = await Post
  .query()
  .whereHas('comments', (builder) => {
    builder.where('is_published', true)
  }, '>', 2)
  .fetch()

doesntHave

The opposite of the has clause:

const posts = await Post
  .query()
  .doesntHave('comments')
  .fetch()
This method does not accept an expression/value constraint.

whereDoesntHave

The opposite of the whereHas clause:

const posts = await Post
  .query()
  .whereDoesntHave('comments', (builder) => {
    builder.where('is_published', false)
  })
  .fetch()
This method does not accept an expression/value constraint.

You can add an or clause by calling the orHas, orWhereHas, orDoesntHave and orWhereDoesntHave methods.

Counts

Retrieve relationship counts by calling the withCount method:

const posts = await Post
  .query()
  .withCount('comments')
  .fetch()

posts.toJSON()
JSON Output
{
  title: 'Adonis 101',
  __meta__: {
    comments_count: 2
  }
}

Define an alias for a count like so:

const posts = await Post
  .query()
  .withCount('comments as total_comments')
  .fetch()
JSON Output
__meta__: {
  total_comments: 2
}

Count Constraints

For example, to only retrieve the count of comments which have been approved:

const posts = await Post
  .query()
  .withCount('comments', (builder) => {
    builder.where('is_approved', true)
  })
  .fetch()

Inserts, Updates & Deletes

Adding, updating and deleting related records is as simple as querying data.

save

The save method expects an instance of the related model.

save can be applied to the following relationship types:

  • hasOne

  • hasMany

  • belongsToMany

const User = use('App/Models/User')
const Post = use('App/Models/Post')

const user = await User.find(1)

const post = new Post()
post.title = 'Adonis 101'

await user.posts().save(post)

create

The create method is similar to save but expects a plain JavaScript object, returning the related model instance.

create can be applied to the following relationship types:

  • hasOne

  • hasMany

  • belongsToMany

const User = use('App/Models/User')

const user = await User.find(1)

const post = await user
  .posts()
  .create({ title: 'Adonis 101' })

createMany

Save many related rows to the database.

createMany can be applied to the following relationship types:

  • hasMany

  • belongsToMany

const User = use('App/Models/User')

const user = await User.find(1)

const post = await user
  .posts()
  .createMany([
    { title: 'Adonis 101' },
    { title: 'Lucid 101' }
  ])

saveMany

Similar to save, but instead saves multiple instances of the related model:

saveMany can be applied to the following relationship types:

  • hasMany

  • belongsToMany

const User = use('App/Models/User')
const Post = use('App/Models/Post')

const user = await User.find(1)

const adonisPost = new Post()
adonisPost.title = 'Adonis 101'

const lucidPost = new Post()
lucidPost.title = 'Lucid 101'

await user
  .posts()
  .saveMany([adonisPost, lucidPost])

associate

The associate method is exclusive to the belongsTo relationship, associating two model instances with each other.

Assuming a Profile belongs to a User, to associate a User with a Profile:

const Profile = use('App/Models/Profile')
const User = use('App/Models/User')

const user = await User.find(1)
const profile = await Profile.find(1)

await profile.user().associate(user)

dissociate

The dissociate method is the opposite of associate.

To drop an associated relationship:

const Profile = use('App/Models/Profile')
const profile = await Profile.find(1)

await profile.user().dissociate()

attach

The attach method is called on a belongsToMany relationship to attach a related model via pivot table:

const User = use('App/Models/User')
const Car = use('App/Models/Car')

const mercedes = await Car.findBy('reg_no', '39020103')
const user = await User.find(1)

await user.cars().attach([mercedes.id])

The attach method accepts an optional callback receiving the pivotModel instance, allowing you to set extra properties on a pivot table if required:

const mercedes = await Car.findBy('reg_no', '39020103')
const audi = await Car.findBy('reg_no', '99001020')

const user = await User.find(1)
const cars = [mercedes.id, audi.id]

await user.cars().attach(cars, (row) => {
  if (row.car_id === mercedes.id) {
    row.is_current_owner = true
  }
})
The create and save methods for belongsToMany relationships also accept a callback allowing you to set extra properties on a pivot table if required.

detach

The detach method is the opposite of the attach method, removing all existing pivot table relationships:

const user = await User.find(1)
await user.cars().detach()

To detach only selected relations, pass an array of ids:

const user = await User.find(1)
const mercedes = await Car.findBy('reg_no', '39020103')

await user.cars().detach([mercedes.id])

sync

The sync method provides a convenient shortcut for detach then attach:

const mercedes = await Car.findBy('reg_no', '39020103')
const user = await User.find(1)

// Behave the same way as:
// await user.cars().detach()
// await user.cars().attach([mercedes.id])

await user.cars().sync([mercedes.id])

update

The update method bulk updates queried rows.

You can use Query Builder methods to update specific fields only:

const user = await User.find(1)

await user
  .posts()
  .where('title', 'Adonis 101')
  .update({ is_published: true })

To update a pivot table, call pivotQuery before update:

const user = await User.find(1)

await user
  .cars()
  .pivotQuery()
  .where('name', 'mercedes')
  .update({ is_current_owner: true })

delete

The delete method removes related rows from the database:

const user = await User.find(1)

await user
  .cars()
  .where('name', 'mercedes')
  .delete()
In the case of belongsToMany, this method also drops the relationship from the pivot table.