You are viewing the legacy version of AdonisJS. Visit https://adonisjs.com for newer docs. This version will receive security patches until the end of 2021.

Relationships

Relational Databases are very powerful in managing the relationship between multiple database tables. Lucid extends this power by offering Javascript-only database associations, which means that you can define a relationship between two tables without touching the SQL schema.

Basic Example

Relational databases are intended to define relations between two or more database tables. There are several benefits of defining relationships as they make common database operations a lot easier.

Let’s take the most common scenario of a User and a Profile model. Where every user in your database can have a social profile. We call this a one to one relationship.

To describe this relationship, you will have to add following line of code to your User model.

app/Model/User.js
'use strict'

const Lucid = use('Lucid')

class User extends Lucid {

  profile () {
    return this.hasOne('App/Model/Profile') (1)
  }

}
1 The hasOne method defines a one to one relationship on a given model.

Now you can call the profile method to access the profile for a given user.

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

Types Of Relationships

Has One Relationship

The hasOne relationship defines a one to one relation between 2 models using a foreign key. Foreign Key is created using the singular name of a given model followed by _id. However, you are free to override it.

Model Foreign Key

User

user_id

Seller

seller_id

has one zfrkve

To setup the relationship shown in the above figure, you need to define it inside your User model.

hasOne(relatedModel, [primaryKey=id], [foreignKey=user_id])

app/Model/User.js
class User extends Lucid {

  profile () {
    return this.hasOne('App/Model/Profile')
  }

}

BelongsTo Relationship

The belongsTo relationship is the opposite of hasOne and always holds the foreign key. So the best way to remember it is with the foreign key. Any database table that has the foreign key, it’s Model will always have the belongsTo relation.

There are no hard rules on how to design your relationships, but it is always nice to design them in the most natural way. For example

Model Relation Related Model

User

hasOne

Profile

Profile

belongsTo

User

Student

hasOne

IdCard

IdCard

belongsTo

User

Hope this makes sense. Continuing with our User Profile relationship, the Profile model will contain the belongsTo relation as it holds the foreign key.

belongsTo(relatedModel, [primaryKey=id], [foreignKey=user_id])

app/Model/Profile.js
class Profile extends Lucid {

  user () {
    return this.belongsTo('App/Model/User')
  }

}

HasMany Relationship

You will find yourself using hasMany quite often, as this is the most common relationship required by any application. Let’s review some examples.

Model Relation Related Model

Book

hasMany

Chapter

Chapter

belongsTo

Book

Post

hasMany

Comment

Comment

belongsTo

Post

The hasMany relationship makes it possible to have multiple related records for a given row each holding the foreignKey.

has many p91i9i

Let’s define the above Models and their relationships in Lucid.

hasMany(relatedModel, [primaryKey=id], [foreignKey=book_id])

app/Model/Book.js
class Book extends Lucid {

  chapters () {
    return this.hasMany('App/Model/Chapter')
  }

}
app/Model/Chapter.js
class Chapter extends Lucid {

  book () {
    return this.belongsTo('App/Model/Book')
  }

}

BelongsToMany Relationship

There are situations where each side of the relationship can have many related rows inside the database. Let’s see some examples.

Model Relation Related Model

Student

belongsToMany

Courses

Course

belongsToMany

Students

Post

belongsToMany

Categories

Category

belongsToMany

Posts

Taking the example of Student and Course, where both models can have many related rows in the database. In other words, it is a many to many relationship.

belongsto many ymawpb

Looking at the above figure, you will notice there is a 3rd table called course_student. Since each model on both the ends has many relationships, they cannot hold the foreign key.

The third table is known as a pivot table. It holds the Foreign Key for both Models and defines a unique relationship between them. Let’s define this relationship in Lucid and review configurable options.

belongsToMany(relatedModel, [pivotTable], [localKey], [otherKey])

app/Model/Student.js
class Student extends Lucid {

  courses () {
    return this.belongsToMany('App/Model/Course')
  }

}
app/Model/Course.js
class Course extends Lucid {

  students () {
    return this.belongsToMany('App/Model/Student')
  }

}

The belongsToMany method accepts multiple arguments to configure the table/fields for the relationship.

Parameter Required Default Value

pivotTable

No

Pivot table is the singular form of each model name, order by name. For example Course and Student model will have course_student as the pivot table name.

localKey

No

Reference to the model foreign key inside the pivot table.

otherKey

No

Reference to the related model foreign key inside the pivot table.

withTimestamps

Also you choose to save timestamps on the pivot table.

class Student extends Lucid {
  courses () {
    this.belongsToMany('App/Model/Course').withTimestamps()
  }
}

HasManyThrough Relationship

Another important relation type supported by Lucid is hasManyThrough. Where a given model is dependent on another model via 3rd model

has many through vux5jm

Taking the example of fetching posts for a given country is not possible since there is no direct relationship between countries and posts. But with the help of User model, we can set up an indirect relationship between countries and posts and that is called hasManyThrough relationship.

app/Model/Country.js
class Country extends Lucid {

  posts () {
    return this.hasManyThrough('App/Model/Post', 'App/Model/User')
  }

}

Now to fetch posts for a given country, you need to call the posts method on the Country model.

const country = yield Country.findBy('name', 'India')
const posts = yield country.posts().fetch()
response.json(posts)

The hasManyThrough method accepts given options.

Parameter Required Default Value

relatedModel

Yes

null

throughModel

Yes

null

primaryKey

No

Model primary key

foreignKey

No

Model foreign key

throughPrimaryKey

No

Related model primary key

throughForeignKey

No

Related model foreign key

Querying Relationships

Querying database for relationships is so straightforward and intuitive with Lucid. You just have to call defined relationship methods without worrying about the join queries.

Also querying relations is divided into three broad categories of Lazy Loading, Eager Loading and Lazy Eager Loading.

Lazy Loading

Lazy loading is a process of loading relationships after fetching the primary/parent record from the database

class User extends Lucid {

  profile () {
    return this.hasOne('App/Model/Profile')
  }

}
const user = yield User.find(1) (1)
const profile = yield user.profile().fetch() (2)
1 First we find a user with the primary key.
2 Then we call the previously defined profile method to fetch the related profile for the given user.

Defining Query Constraints

You can also attach query builder methods to your relationship definitions, and Lucid will make sure to execute them.

class User extends Lucid {

  profile () {
    return this
      .hasOne('App/Model/Profile')
      .where('is_active', true) (1)
  }

}
1 Now when you will fetch the related profile for a given user, it will only include the record where is_active=true.

Runtime Query Constraints

You can also define runtime query constraints, just by chaining the query builder methods.

const user = yield User.find(1)
const profile = user
  .profile()
  .where('is_active', true)
  .fetch()

Eager Loading

Lazy loading may create N+1 problem in certain scenarios. For example Loading profile for ten users, one by one will make a total of 11 queries. To eliminate this behavior you can preload/eager load profiles which will result in a total of 2 database queries.

const users = yield User
  .query()
  .with('profile') (1)
  .fetch()

console.log(users.toJSON())
Output
[
  {
    id: 1,
    username: 'joe',
    email: '...',
    profile: {
      id: 4,
      avatar: '...'
    }
  }
]
1 The with method can be used to eager load relationships with the parent record. Also, you can load multiple/nested relationships using the with method.

Eager Loading Multiple Relations

const users = yield User
  .query()
  .with('profile', 'friends')
  .fetch()

Eager Loading Nested Relations

const user = yield User
  .query()
  .with('friends.profile')
  .fetch()

Runtime Query Constraints

Also, you can build upon the query builder to filter the results from the related models.

const user = yield User
  .with('profile', 'friends')
  .scope('profile', (builder) => {
    builder.where('is_active', true)
  }) (1)
  .scope('friends', (builder) => {
    builder.orderBy('rank', 'desc')
  })
  .fetch()
1 The scope method gives you the access to the query builder of the related model which means you can add where clause to filter the results.

Lazy Eager Loading

Lazy eager loading is a combination of Lazy loading and Eager Loading instead of preloading all the relationships, you fetch the parent model instance and then eager load all related models.

const user = yield User.find(1)
yield user.related('profile', 'friends').load()

console.log(user.toJSON())
Output
[
  {
    id: 1,
    username: 'joe',
    email: '...',
    profile: {
      id: 4,
      avatar: '...'
    }
  }
]

Filtering Records

Supported by adonis-lucid 3.0.13 or greater

A quite common use case is to filter top-level results based upon some conditions on a relationship. For example:

  1. Display all users who have contributed at least one post.

  2. Fetch all cars with 2 or more owners in a lifetime.

Ideally, it requires some complex joins, but Lucid makes it so easier for you.

has(relation, [expression], [value])

class User extends Lucid {

  posts () {
    return this.hasMany('App/Model/Post')
  }

}

// filtering
const users = yield User.query().has('posts').fetch()

// two or more
const users = yield User.query().has('posts', '>=', 2).fetch()

whereHas(relation, callback, [expression], [value])

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

Also, you can make use of doesntHave and whereDoesntHave methods, which are opposite of the above ones.

doesntHave(relation)

const users = yield User.query().doesntHave('friends').fetch()

whereDoesntHave(relation, callback)

const users = yield User.query().whereDoesntHave('friends', (builder) => {
  builder.where('is_verified', false)
}).fetch()

Counting Related Models

Getting counts of relationships is commonly used by web applications. For example: Fetching comments count for every post.

withCount(relation)

class Post extends Lucid {

  comments () {
    this.hasMany('App/Model/Comment')
  }

}

// fetching counts
const posts = yield Posts.query().withCount('comments').fetch()

console.log(posts.first().comments_count)

Insert, Updates & Deletes

Relationships can also be created, updated and deleted with the same ease as fetching them. Whereas each relationship type has slightly different methods to persist related data.

save(modelInstance, [pivotValues])

The save method can be used to create/update related model instance. It works with following relations.

NOTE

pivotValues are only supported by belongsToMany relationship. Checkout attach method documentation on how pivotValues are defined.

  1. hasOne

  2. hasMany

  3. belongsToMany

const user = yield User.find(1)

const profile = new Profile()
profile.name = '@cybernox'
profile.avatar =  '...'

yield user.profile().save(profile)

create(values, [pivotValues])

The create method is almost similar to the save method whereas you pass an arbitrary object instead of passing a model instance.

const user = yield User.find(1)

yield user
  .profile()
  .create({name: '@cybernox', avatar: '...'})

saveMany(arrayOfInstances)

Save multiple related records for a given model instance. saveMany works with following relation types.

  1. hasMany

  2. belongsToMany

const user = yield User.find(1)

const profile = new Profile({name: '@cybernox'})
const anotherProfile = new Profile({name: '@jgwhite'})

yield user.profile.saveMany([profile, anotherProfile])

createMany(arrayOfValues)

The createMany method will also create multiple records whereas you pass an array of objects instead of model instances.

const user = yield User.find(1)
const profiles = yield user
  .profile()
  .createMany([{name: '@cybernox'}, {name: 'jgwhite'}])

attach(rows, [pivotValues])

The attach method only works with belongsToMany relationship. You attach existing records to form a relationship.

const student = yield Student.find(1)
const coursesIds = yield Courses.ids()

yield Student.courses().attach(coursesIds)

Optionally, you can pass an object to populate fields inside the pivotTable.

yield Student.courses().attach(coursesIds, {enrollment_confirmed: false})

Or you can also define different pivotValues for each related row.

const mathsId = yield Courses
  .query()
  .where('name', 'Maths')
  .pluckId()

const englishId = yield Courses
  .query()
  .where('name', 'English')
  .pluckId()

const enrollment = {}
enrollment[mathsId] = {enrollment_confirmed: true}
enrollment[englishId] = {enrollment_confirmed: false}

yield Student.courses().attach(enrollment)

detach(rows)

The detach method is the opposite of the attach and will remove the relationships from pivotTable.

The detach method does not remove the rows from the related model. It just removes the relationship from the pivot table. 
const student = yield Student.find(1)
const coursesIds = yield Courses.ids()

yield Student.courses().detach(coursesIds)

sync(rows, [pivotValues])

The sync will remove all existing relations and will only add given relations. Think of it as calling detach and attach together.

You can also pass pivotValues to the sync method similar to the attach method. 
const student = yield Student.find(1)
const coursesIds = yield Courses.ids()

yield Student.courses().sync(coursesIds)

updatePivot(values, [relatedModelId])

In order to update the values inside the pivot table you can make use of updatePivot method.

const student = yield Student.find(1)
Student.courses().updatePivot({marks: 90})

Or

const student = yield Student.find(1)
const maths = yield Course.where('name', 'Maths').first()
Student.courses().updatePivot({marks: 90}, maths.id)

withPivot(keys)

When fetching records for belongsToMany lucid will not select any rows from the pivot table. In order to fetch additional fields you can make use of withPivot method.

Pivot table fields will be prefixed with _pivot_. In below example the marks will be returned as _pivot_marks 
'use strict'

class Student extends Lucid {

  courses () {
    return this.belongsToMany('App/Model/Course').withPivot('marks')
  }

}

Also you can define fields when running the select query.

const student = yield Student.find(1)
const courses = yield student.courses().withPivot('marks').fetch()

associate(modelInstance)

The associate method is used with belongsTo relationship to associate an existing database row.

const user = yield User.find(1)
const profile = new Profile()
profile.name = '@cybernox'

profile.user().associate(user)
yield profile.save()

dissociate

The dissociate method is the opposite of associate and will remove the existing relationship

const profile = yield Profile.find(1)

profile.user().dissociate()
yield profile.save()