Many to many

The ManyToMany relationship class manages the many to many relationship between two models.

You will not find yourself directly working with this class. However, an instance of the class can be accessed using the Model.$getRelation method.

import { BaseModel, manyToMany, ManyToMany } from '@ioc:Adonis/Lucid/Orm'
import Project from 'App/Models/Project'

class User extends BaseModel {
  @manyToMany(() => Project)
  public projects: ManyToMany<typeof Project>
}

User.$getRelation('projects').relationName
User.$getRelation('projects').type
User.$getRelation('projects').relatedModel()

Methods/Properties

Following is the list of methods and properties available on the ManyToMany relationship.

type

The type of the relationship. The value is always set to manyToMany.

class User extends BaseModel {
  @manyToMany(() => Project)
  public projects: ManyToMany<typeof Project>
}

User.$getRelation('projects').type // 'manyToMany'

relationName

The relationship name. It is a property name defined on the parent model.

class User extends BaseModel {
  @manyToMany(() => Project)
  public projects: ManyToMany<typeof Project>
}

User.$getRelation('projects').relationName // 'projects'

serializeAs

The name to be used for serializing the relationship. You can define it using the decorator options.

class User extends BaseModel {
  @manyToMany(() => Project, {
    serializeAs: 'teamProjects'
  })
  public projects: ManyToMany<typeof Project>
}

booted

Find if the relationship has been booted. If not, call the boot method.

boot

Boot the relationship. Lucid models public APIs call this method internally, and you never have to boot the relationship manually.

model

Reference to the parent model (the one that defines the relationship).

class User extends BaseModel {
  @manyToMany(() => Project, {
    serializeAs: 'teamProjects'
  })
  public projects: ManyToMany<typeof Project>
}

User.$getRelation('projects').model // User

relatedModel

Reference to the relationship model. The property value is a function that returns the related model.

class User extends BaseModel {
  @manyToMany(() => Project, {
    serializeAs: 'teamProjects'
  })
  public projects: ManyToMany<typeof Project>
}

User.$getRelation('projects').relatedModel() // Project

localKey

The localKey for the relationship. You must read the NamingStrategy doc to learn more about how the key name is computed.

You can also define the localKey explicitly. Do make sure you mention the model property name and NOT the database column name.

class User extends BaseModel {
  @column()
  public id: number

  @manyToMany(() => Project, {
    localKey: 'id', // id column on "User" model
  })
  public projects: ManyToMany<typeof Project>
}

relatedKey

The relatedKey for the relationship. This is usually the primary key on the related model. For example, The id column on the Project model.

You can also define the relatedKey explicitly. Do make sure you mention the model property name and NOT the database column name.

class User extends BaseModel {
  @manyToMany(() => Project, {
    relatedKey: 'id', // id column on "Project" model
  })
  public projects: ManyToMany<typeof Project>
}

pivotForeignKey

The pivotForeignKey is the name of the column inside the pivot table for the parent model. For example: The user_id column inside the pivot table is the pivotForeignKey.

You can also define the pivotForeignKey explicitly. Also, since there is no pivot model, you define the database column name directly.

class User extends BaseModel {
  @manyToMany(() => Project, {
    pivotForeignKey: 'user_id',
  })
  public projects: ManyToMany<typeof Project>
}

pivotRelatedForeignKey

The pivotRelatedForeignKey is the name of the column inside the pivot table for the related model. For example: The project_id column inside the pivot table is the pivotRelatedForeignKey.

You can also define the pivotRelatedForeignKey explicitly. Also, since there is no pivot model, you define the database column name directly.

class User extends BaseModel {
  @manyToMany(() => Project, {
    pivotRelatedForeignKey: 'project_id',
  })
  public projects: ManyToMany<typeof Project>
}

pivotTable

The pivotTable property defines the pivot table to query for persisting/fetching related rows. Make sure to read the naming strategy guide to learn more about how the table name is computed.

You can also define the pivotTable name explicitly.

class User extends BaseModel {
  @manyToMany(() => Project, {
    pivotTable: 'user_projects'
  })
  public projects: ManyToMany<typeof Project>
}

pivotColumns

Define the columns you want Lucid to select when fetching many to many relationships. By default, it only selects the pivotRelatedForeignKey and the pivotForeignKey columns.

class User extends BaseModel {
  @manyToMany(() => Project, {
    pivotColumns: ['role', 'created_at', 'updated_at']
  })
  public projects: ManyToMany<typeof Project>
}

onQuery

The onQuery method is an optional hook to modify the relationship queries. It receives an instance of the ManyToManyQueryBuilder .

You can define the hook at the time of declaring the relation.

class User extends BaseModel {
  @manyToMany(() => Project, {
    onQuery(query) {
      query.where('isActive', true)
    }
  })
  public projects: ManyToMany<typeof Project>
}

If you want to preload a nested relationship using the onQuery hook, then make sure to put it inside the !query.isRelatedSubQuery conditional because sub-queries are NOT executed directly, they are used inside other queries.

class User extends BaseModel {
  @manyToMany(() => Project, {
    onQuery(query) {
      if (!query.isRelatedSubQuery) {
        query.preload('tasks')
      }
    }
  })
  public projects: ManyToMany<typeof Project>
}

setRelated

Set a relationship on the parent model instance. The methods accept the parent model as the first argument and the related model instance as the second argument.

You must ensure that both the model instances are related to each other before calling this method.

const user = new User()
const project = new Project()

User.$getRelation('projects').setRelated(user, [project])

pushRelated

The pushRelated method pushes the relationship to the existing relationship value array.

const user = new User()

User.$getRelation('projects').pushRelated(user, new Project())
User.$getRelation('projects').pushRelated(user, new Project())
User.$getRelation('projects').pushRelated(user, new Project())

user.projects.length // 3

setRelatedForMany

Set the relationships on more than one parent model. The method accepts an array of the parent models as the first argument and an array of related models as the second argument.

Lucid internally calls this with the results of the preloader.

const users = [
  User {
    id: 1,
  },
  User {
    id: 2,
  },
  User {
    id: 3,
  }
]

const projects = [
  Project {
    id: 1,
    $extras: {
      pivot_user_id: 1,
    }
  },
  Project {
    id: 2,
    $extras: {
      pivot_user_id: 1,
    }
  },
  Project {
    id: 3,
    $extras: {
      pivot_user_id: 2,
    }
  },
  Project {
    id: 4,
    $extras: {
      pivot_user_id: 3,
    }
  }
]

User.$getRelation('projects').setRelatedForMany(users, projects)

client

Returns the reference to the ManyToManyQueryClient . The query client exposes the API to persist/fetch related rows from the database.

getPivotPair

Returns a tuple with the pivotForeignKey and its value from the parent model. The method accepts the parent model as the only argument.

const user = await User.find(1)

User.$getRelation('projects').getPivotPair(user)

// Return value: ['user_id', 1]

getPivotRelatedPair

Returns a tuple with the pivotRelatedForeignKey and its value from the related model. The method accepts the related model as the only argument.

const project = await Project.find(1)

User.$getRelation('projects').getPivotRelatedPair(project)

// Return value: ['project_id', 1]

eagerQuery

Returns an instance of the ManyToManyQueryBuilder . The query builder has the same API as the Model query builder

subQuery

Returns an instance of the ManytoManySubQueryBuilder . The sub queries are not meant to be executed and mainly used by the withCount and whereHas methods.

Query client

The query client exposes the API to persist/fetch related rows from the database. You can access the query client for a relationship using the related method.

const user = await User.find(1)

user.related('projects') // ManytoManyClientContract

create

Please create a new relationship model instance and persist it to the database right away. The method also inserts a new row into the pivot table.

const project = await user
  .related('projects')
  .create({
    title: 'Shipping v5',
  })

You can define the pivot attributes as the second argument.

await user
  .related('projects')
  .create({
    title: 'Shipping v5',
  }, {
    role: 'admin'
  })

The create method inherits the transaction client, or the connection name defined on the parent model instance. For example:

const trx = await Database.transaction()
const user = await User.query({ client: trx }).first()

/**
* Uses the `$trx` property from the `user` instance to
* persist relationship
*/
await user.related('projects').create()

await trx.commit()

createMany

Create multiple instances of a relationship model and persist them to the database. The method accepts an array of objects to persist.

• One insert query is issued for each model instance to ensure that we execute the lifecycle hooks for every individual instance.
• All the insert queries are internally wrapped inside a transaction. In case of an error, we will roll everything back.

await user.related('projects').createMany([
  {
    title: 'Shipping v5',
  },
  {
    title: 'Recording screencasts'
  }
])

The pivot attributes can be defined as the second argument. It should be an array with the same length as the data array.

await user.related('projects').createMany([
  {
    title: 'Shipping v5',
  },
  {
    title: 'Recording screencasts'
  }
], [
  {
    role: 'admin'
  },
  undefined // do not set any pivot attributes
])

save

The save method persists an existing instance of the relationship. Like the create method, the save method also accepts an optional pivot attribute object as the second argument.

const project = new Project()
project.title = 'Shipping v5'

const user = await User.find(1)

await user
  .related('projects')
  .save(project)

project.$isPersisted // true
project.$extras.pivot_user_id // 1
project.$extras.pivot_project_id === project.id // true

Optionally, you can instruct the save method to check the pivot table before adding a new row.

await user
  .related('projects')
  .save(
    project,
    {},
    true // 👈 do not add new row when pivot table has this relationship already
  )

saveMany

The saveMany method persists an array of related model instances to the database.

• One insert query is issued for each model instance to ensure that we execute the lifecycle hooks for every individual instance.
• All the insert queries are internally wrapped inside a transaction. In case of an error, we will roll everything back.

const project = new Project()
project.title = 'Shipping v5'

const project1 = new Project()
project1.title = 'Recording screencasts'

await user
  .related('post')
  .saveMany([project, project1])

attach

The attach method allows you to set up relationships inside the pivot table by just using the ids. For example:

const user = await User.find(1)
const project = await Project.find(1)

await user.related('projects').attach([project.id])

You can define pivot attributes by passing a key-value pair. The key is the related model id, and value is an object of pivot attributes.

await user.related('projects').attach({
  [project.id]: {
    role: 'admin'
  }
})

detach

The detach method removes the relationship from the pivot table. Either you can pass an array of related models ids or call the detach method without any arguments to remove all related rows.

// Remove projects with id 1, 2, 3
await user.related('projects').detach([1, 2, 3])

// Remove all projects
await user.related('projects').detach()

sync

The sync method allows you to sync an array of related model ids in the pivot table. The sync operation is performed by considering the input as the real source of truth.

await user
  .related('projects')
  .sync([1, 2, 4, 5])

In the above example, the sync method will only keep the projects with the mentioned ids and removes the other ones.

You can change this behavior by passing false as the second argument to the sync method, and then it will attach the mentioned ids without detaching the others.

In the following example, the sync method will attach (only if they aren't already) the projects with the id of "1" and "2" without detaching any existing ids.

await user
  .related('projects')
  .sync([1, 2], false)

You can also perform a sync with pivot attributes.

await user
  .related('projects')
  .sync({
    [1]: {
      role: 'admin'
    },
    [4]: {
      role: 'guest',
    },
    [3]: {
      role: null,
    }
  })

The sync method will compute the diff between the existing pivot rows and the input data and performs insert, update, and delete queries as per the diff.

query

Returns an instance of the ManyToManyQueryBuilder .

Query Builder

The ManyToMany Query Builder has the following additional methods on top of a standard model query builder.

You can access the relationship query builder as follows:

const user = await User.find(1)

user.related('projects').query() // ManytoManyQueryBuilder

pivotColumns

Select columns from the pivot table. This method will prefix the pivot table name to the column behind the scenes and aliases it to pivot_[column_name].

user
  .related('projects')
  .query()
  .pivotColumns(['role']) // select project_user.role as pivot_role

wherePivot

Write a where conditional for the pivot table. The method has the same API as the where method on a standard query builder. It will prefix the pivot table name to the column name.

user
  .related('projects')
  .query()
  .wherePivot('role', 'admin') // where project_user.role = ?

Following is the list of the wherePivot method variations and shares the same API.

whereInPivot

Same as the whereIn method on a standard query builder. However, it prefixes the pivot table name in front of the column name.

user
  .related('projects')
  .query()
  .whereInPivot('role', ['admin', 'collaborator']) 

Following is the list of the whereInPivot method variations and shares the same API.

whereNullPivot

Same as the whereNull method on a standard query builder. However, it prefixes the pivot table name in front of the column name.

user
  .related('projects')
  .query()
  .whereNullPivot('deleted_at')

Following is the list of the whereNullPivot method variations and shares the same API.

groupLimit

The groupLimit method uses SQL window functions to add a limit to each group during relationship preloading. Please read the preloading guide to learn why and when you need the groupLimit method.

await User.query().preload('projects', (query) => {
  query.groupLimit(10)
})

groupOrderBy

Add an order by clause to the group limit query. The method has the same API as the orderBy method on the standard query builder.

await User.query().preload('projects', (query) => {
  query
    .groupLimit(10)
    .groupOrderBy('projects.updated_at', 'desc')
})