Many To Many

Many-to-many relations are slightly more complicated than other relationships. An example of such a relationship is a user with many roles, where the roles are also shared by other users. For example, many users may have the role of "Admin". To define this relationship, three models are needed: User, Role, and RoleUser.

The RoleUser contains the fields to hold id of User and Role model. We'll define user_id and role_id fields here. The name of RoleUser model could be anything, but for this example, we'll keep it this way to make it easy to understand.

Many-to-many relationships are defined by defining this.belongsToMany().

class User extends Model {
  static entity = 'users'
  static fields () {
    return {
      id: this.attr(null),
      roles: this.belongsToMany(Role, RoleUser, 'user_id', 'role_id')
    }
  }
}
class Role extends Model {
  static entity = 'roles'
  static fields () {
    return {
      id: this.attr(null)
    }
  }
}
class RoleUser extends Model {
  static entity = 'roleUser'
  static primaryKey = ['role_id', 'user_id']
  static fields () {
    return {
      role_id: this.attr(null),
      user_id: this.attr(null)
    }
  }
}

The argument order of the belongsToMany attribute is:

1. The Related model which is in this case Role.
2. Intermediate pivot model which is in this case RoleUser.
3. Field of the pivot model that holds the id value of the parent – User – model.
4. Field of the pivot model that holds the id value of the related – Role – model.

You may also define custom local key at 5th and 6th argument.

class User extends Model {
  static entity = 'users'
  static fields () {
    return {
      id: this.attr(null),
      roles: this.belongsToMany(
        Role,
        RoleUser,
        'user_id',
        'role_id',
        'user_local_id',
        'role_local_id'
      )
    }
  }
}

Defining The Inverse Of The Relationship

To define the inverse of a many-to-many relationship, you can place another belongsToMany attribute on your related model. To continue our user roles example, let's define the users method on the Role model:

class User extends Model {
  static entity = 'users'
  static fields () {
    return {
      id: this.attr(null)
    }
  }
}
class Role extends Model {
  static entity = 'roles'
  static fields () {
    return {
      id: this.attr(null),
      users: this.belongsToMany(User, RoleUser, 'role_id', 'user_id')
    }
  }
}
class RoleUser extends Model {
  static entity = 'roleUser'
  static primaryKey = ['role_id', 'user_id']
  static fields () {
    return {
      role_id: this.attr(null),
      user_id: this.attr(null)
    }
  }
}

As you can see, the relationship is defined the same as its User counterpart, except referencing the User model and the order of 3rd and 4th argument is reversed.

Access Intermediate Model

Working with many-to-many relations requires the presence of an intermediate model. Pinia ORM provides some helpful ways of interacting with this model. For example, let's assume our User object has many Role objects that it is related to. After accessing this relationship, we may access the intermediate model using the pivot attribute on the models.

const user = User.query().with('roles').first()
user.roles.forEach((role) => {
  console.log(role.pivot)
})

Notice that each Role model we retrieve is automatically assigned a pivot attribute. This attribute contains a model representing the intermediate model and may be used like any other model.

Customizing The pivot Attribute Name

As noted earlier, attributes from the intermediate model may be accessed on models using the pivot attribute. However, you are free to customize the name of this attribute to better reflect its purpose within your application.

For example, if your application contains users that may subscribe to podcasts, you probably have a many-to-many relationship between users and podcasts. If this is the case, you may wish to rename your intermediate table accessor to subscription instead of pivot. This can be done using the as method when defining the relationship:

class User extends Model {
  static entity = 'users'
  static fields () {
    return {
      id: this.attr(null),
      podcasts: this.belongsToMany(
        Podcast,
        Subscription,
        'user_id',
        'podcast_id'
      ).as('subscription')
    }
  }
}

Once this is done, you may access the intermediate table data using the customized name.

const user = useRepo(User).with('podcasts').first()
user.podcasts.forEach((podcast) => {
  console.log(podcast.subscription)
})