Many To Many

Many To Many

When you develop a RBAC system, a user may have many roles, a role maybe attached to many users.
This is a typical many to many scenario.

In this case you will need three tables.
A user table , a role table , and a pivot table user_has_roles logs users’ roles.
In pivot table, user_has_roles.user_id is user table id ,user_has_roles.role_id is role table id.
user_has_roles table may have some extra info about the role status suspended,revoked,expired time,granted by who etc.

type User struct {
	*goeloquent.EloquentModel
	Id       int64          `goelo:"column:id;primaryKey"`
	UserName sql.NullString `goelo:"column:username"`
	Age      int            `goelo:"column:age"`
	Balance  int            `goelo:"column:balance"`
	Email    sql.NullString `goelo:"column:email"`
	Roles    []Role         `goelo:"BelongsToMany:RolesRelation"`
}
func (u *User) TableName() string {
	return "users"
}
func (u *User) RolesRelation() *goeloquent.RelationBuilder {
	return u.BelongsToMany(u, &Role{}, "user_roles", "user_id", "role_id", "id", "id")
}

BelongsToMany takes 6 parameter,first one is a pointer of current model(user),second is a pointer of related model(role), third one
is pivotTable , 4th is pivotTable.selfKey(user_id) , 5th is pivotTable.relatedKey(role_id) ,last one is related model field
correspond to 4th parameter.

Usage Example

Use With when retrive

var u UserT
var us []UserT
DB.Model(&UserT{}).With("Roles").Find(&u,4)
//{select * from `users` where `id` in (?) limit 1 [4] {1} 61.560446ms}
//{select `role`.*, `user_has_roles`.`user_id` as `goelo_pivot_user_id`, `user_has_roles`.`role_id` as `goelo_pivot_role_id`, `user_has_roles`.`status` as `goelo_pivot_status`, `user_has_roles`.`user_id` as `goelo_pivot_user_id`, `user_has_roles`.`granted_by` as `goelo_pivot_granted_by` from `role` inner join `user_has_roles` on `user_has_roles`.`role_id` = `role`.`rid` where `user_has_roles`.`user_id` in (?) and `user_has_roles`.`status` = ? [1 2] {0} 81.765708ms}

DB.Model(&UserT{}).With("Roles").Where("id", "<", 10).Get(&us)
//{select * from `users` where `id` < ? [10] {3} 60.056161ms}
//{select `role`.*, `user_has_roles`.`user_id` as `goelo_pivot_user_id`, `user_has_roles`.`role_id` as `goelo_pivot_role_id` from `role` inner join `user_has_roles` on `user_has_roles`.`role_id` = `role`.`rid` where `user_has_roles`.`user_id` in (?,?,?,?) [1 2 1 2] {0} 81.8815ms}

Directly Call Relation Method

var uu UserT
var roles []Role
DB.Model(&uu).Find(&uu,6)
//{select * from `users` where `id` in (?) limit 1 [6] {1} 60.646603ms}


uu.RolesRelation().Get(&roles)
//{select `roles`.*,`user_roles`.`user_id` as `goelo_pivot_user_id`,`user_roles`.`role_id` as `goelo_pivot_role_id` from `roles` inner join user_roles on `user_roles`.`role_id` = `roles`.`id` where `user_id` = ? [6] {2} 59.866877ms}

Many To Many (Inverse)

type Role struct {
	*goeloquent.EloquentModel
	Id          int64  `goelo:"column:id;primaryKey"`
	Name        string `goelo:"column:name"`
	DisplayName string `goelo:"column:display_name"`
	Users       []User `goelo:"BelongsToMany:UsersRelation"`
}

func (r *Role) UsersRelation() *goeloquent.RelationBuilder {
	return r.BelongsToMany(r, &User{}, "user_roles", "role_id", "user_id", "id", "id")
}
func (r *Role) TableName() string {
	return "roles"
}

Well same to BelongsToMany

Usage Example

Use With when retrive

var role Role
var rs []Role

DB.Model(&Role{}).With("Users").Get(&rs)
{select * from `role` [] {1} 81.365833ms}
{select `user`.*, `user_has_roles`.`role_id` as `goelo_pivot_role_id`, `user_has_roles`.`user_id` as `goelo_pivot_user_id` from `user` inner join `user_has_roles` on `user_has_roles`.`user_id` = `user`.`id` where `user_has_roles`.`role_id` in (?) [1] {0} 81.665125ms}

Directly Call Relation Method

var rr models.Role
var users []models.User
DB.Model(&rr).Find(&rr,10)
//{select * from `roles` where `id` in (?) limit 1 [10] {1} 62.39588ms}
rr.Load("Users")
//{select `users`.*,`user_roles`.`role_id` as `goelo_pivot_role_id`,`user_roles`.`user_id` as `goelo_pivot_user_id` from `users` inner join user_roles on `user_roles`.`user_id` = `users`.`id` where `user_roles`.`role_id` in (?) [10] {2} 60.394408ms}

rr.UsersRelation().Get(&users)
//{select `users`.*,`user_roles`.`role_id` as `goelo_pivot_role_id`,`user_roles`.`user_id` as `goelo_pivot_user_id` from `users` inner join user_roles on `user_roles`.`user_id` = `users`.`id` where `role_id` = ? [10] {2} 60.804572ms}

Working with intermediate table

Like we said before in this chapter,

user_has_roles table may have some extra info about the role status suspended,revoked,expired time,granted by who etc.

Sometimes we want filter out roles granted by a certtain user , and with its expired time
You can use WherePivot to filter intermediate table rows and WithPivot to select intermediate column

var manager UserT
DB.Model(&UserT{}).With("Roles").Where("id", "<", 10).WherePivot("granted_by",manager.Id).WithPivot("ended_at","status").Get(&us)
//{select `role`.*, `user_has_roles`.`user_id` as `goelo_pivot_user_id`, `user_has_roles`.`role_id` as `goelo_pivot_role_id`, `user_has_roles`.`ended_at` as `goelo_pivot_ended_at`, `user_has_roles`.`status` as `goelo_pivot_status` from `role` inner join `user_has_roles` on `user_has_roles`.`role_id` = `role`.`rid` where `user_has_roles`.`user_id` in (?,?,?,?,?,?,?) and `user_has_roles`.`granted_by` = ? [1 2 1 2 3 4 5 1] {0} 81.563375ms}
//currently all intermediate table column are mapped as string , you need to convent it by yourself

for _, role := range user.Roles {
      fmt.Println(role.Pivot["status"].String)
  }