Many to Many
A many-to-many relationship connects rows on both sides through a third table, called the pivot table. A post has many tags, and a tag belongs to many posts. Each row in post_tags links one post to one tag.
import { belongsToMany, defineModel } from '@holo-js/db'
export default defineModel('posts', {
relations: {
tags: belongsToMany('Tag', {
pivotTable: 'post_tags',
foreignPivotKey: 'post_id',
relatedPivotKey: 'tag_id',
}),
},
})import Post from '../models/Post'
const post = await Post.with('tags').findOrFail(1)
post.tags.map(tag => tag.name)
await post.tags().sync([1, 2, 3])post.tags is typed as an array of Tag models.
The pivot table
A pivot table needs one column for each side. Create it in a migration:
import { defineMigration } from '@holo-js/db'
export default defineMigration({
async up({ schema }) {
await schema.createTable('post_tags', (table) => {
table.id()
table.integer('post_id')
table.integer('tag_id')
table.boolean('is_primary').default(false)
table.timestamps()
})
},
async down({ schema }) {
await schema.dropTable('post_tags')
},
})Defining the relation
belongsToMany takes the related model and the pivot details:
| Option | Meaning | Default |
|---|---|---|
pivotTable | Name of the pivot table | required |
foreignPivotKey | Pivot column pointing at this model | required |
relatedPivotKey | Pivot column pointing at the related model | required |
parentKey | Column on this model that foreignPivotKey matches | 'id' |
relatedKey | Column on the related model that relatedPivotKey matches | 'id' |
You can also pass them positionally: belongsToMany('Tag', 'post_tags', 'post_id', 'tag_id').
Define the inverse on the other model with the keys swapped:
import { belongsToMany, defineModel } from '@holo-js/db'
export default defineModel('tags', {
relations: {
posts: belongsToMany('Post', {
pivotTable: 'post_tags',
foreignPivotKey: 'tag_id',
relatedPivotKey: 'post_id',
}),
},
})Reading pivot columns
Every loaded related model gets a pivot property. It holds the two pivot keys, and for morph relations the morph type column, plus the columns you declare with withPivot() and withTimestamps(). Other pivot columns aren't loaded. The pivot is typed from the pivot table's schema, so is_primary autocompletes:
const post = await Post.with('tags').findOrFail(1)
for (const tag of post.tags) {
console.log(tag.name, tag.pivot.is_primary)
}Declare the columns on the relation to read them:
tags: belongsToMany(() => Tag, {
pivotTable: 'post_tags',
foreignPivotKey: 'post_id',
relatedPivotKey: 'tag_id',
}).withPivot('is_primary')Declaring writable pivot columns
Use withPivot() to list extra pivot columns you want to write through attach(), sync(), and updateExistingPivot(). Column names autocomplete against the pivot table:
tags: belongsToMany('Tag', {
pivotTable: 'post_tags',
foreignPivotKey: 'post_id',
relatedPivotKey: 'tag_id',
}).withPivot('is_primary')Undeclared pivot columns are rejected
Writing a pivot column that isn't listed in withPivot() throws a SecurityError. So does trying to set the key columns (post_id, tag_id) yourself. This stops request data from overwriting pivot rows you didn't intend to change.
Pivot timestamps
Add withTimestamps() to fill created_at and updated_at on the pivot automatically. created_at is set on insert, and updated_at on every insert and update:
tags: belongsToMany('Tag', {
pivotTable: 'post_tags',
foreignPivotKey: 'post_id',
relatedPivotKey: 'tag_id',
}).withPivot('is_primary').withTimestamps()Renaming the pivot property
as() changes the property name from pivot to something that reads better. The type follows the new name:
members: belongsToMany('User', {
pivotTable: 'team_members',
foreignPivotKey: 'team_id',
relatedPivotKey: 'user_id',
}).withPivot('role').as('membership')const team = await Team.with('members').findOrFail(1)
team.members[0]?.membership.roleUsing a model for the pivot
using() turns each pivot row into an instance of a model you define, so casts and model methods apply:
import { belongsToMany, defineModel } from '@holo-js/db'
import Membership from './Membership'
export default defineModel('teams', {
relations: {
members: belongsToMany('User', {
pivotTable: 'team_members',
foreignPivotKey: 'team_id',
relatedPivotKey: 'user_id',
}).using(() => Membership).as('membership'),
},
})Filtering and sorting by pivot columns
wherePivot() and orderByPivot() constrain the relation by pivot columns. Both autocomplete pivot column names:
primaryTags: belongsToMany('Tag', {
pivotTable: 'post_tags',
foreignPivotKey: 'post_id',
relatedPivotKey: 'tag_id',
}).wherePivot('is_primary', true).orderByPivot('created_at', 'desc')wherePivot() accepts an operator too: .wherePivot('priority', '>', 2).
Attaching and detaching
Call the relation as a method to change pivot rows. Pass the related models' IDs.
const post = await Post.findOrFail(1)
await post.tags().attach(5)
await post.tags().attach([5, 6, 7])
await post.tags().attach(5, { is_primary: true })
await post.tags().attach({
5: { is_primary: true },
6: { is_primary: false },
})Attaching an ID that is already linked updates its pivot columns instead of inserting a duplicate.
Pivot columns are typed from the pivot table's schema in attach(), sync(), syncWithoutDetaching(), toggle(), updateExistingPivot(), and factory hasAttached(). Only the columns declared with withPivot() or withTimestamps() are accepted. A pivot key column, a computed column, an undeclared column, or a value of the wrong type is a type error.
detach() removes pivot rows and returns how many were removed. Call it with no arguments to remove them all:
await post.tags().detach(5)
await post.tags().detach([5, 6])
await post.tags().detach()Syncing
sync() makes the pivot match the list you pass: missing IDs are attached, extra IDs are detached, and changed pivot columns are updated. It returns what changed. An ID that appears more than once is synced once, and its last pivot values win:
const changes = await post.tags().sync([1, 2, 3])
// { attached: [3], detached: [4], updated: [] }Pass an object to set pivot columns per ID:
await post.tags().sync({
1: { is_primary: true },
2: { is_primary: false },
})To add and update without removing anything, use syncWithoutDetaching() on the model:
await post.syncWithoutDetaching('tags', [7, 8])Toggling
toggle() detaches IDs that are attached and attaches the rest:
const { attached, detached } = await post.tags().toggle([1, 2])Updating a pivot row
updateExistingPivot() changes the pivot columns for one ID. It returns 1 if a row changed and 0 if it didn't exist or already had those values:
await post.tags().updateExistingPivot(1, { is_primary: false })The database compares date, datetime and timestamp pivot columns, so a Date or text for the instant the row already holds counts as unchanged, in sync() and syncWithoutDetaching() too. A difference finer than a JavaScript Date can hold, such as PostgreSQL microseconds, still counts as a change.
Creating and saving through the relation
create() inserts a new related row and attaches it. save() does the same for a model you built:
import Tag from '../models/Tag'
const tag = await post.tags().create({ name: 'Databases', slug: 'databases' })
await post.tags().save(Tag.make({ name: 'SQL', slug: 'sql' }))How pivot writes behave
- Every
attach,detach,sync, andtogglecall runs in a transaction. If any write fails, the whole call is rolled back. - Large ID lists are split into batches automatically, so you can sync thousands of IDs without hitting the database's parameter limit.
- Writes clear the loaded relation on the model. Call
post.load('tags')to read the new state.