Retrieving Parents and Children - Self Referencing Entity gorm

Manuelarte 0
Viewed 25

I have a table that have a hierarchy structure with a parent having many children and a children having many parents.

As an example the following struct:

type User struct {
    gorm.Model
    Name       string
    SubUsers   []*User `gorm:"many2many:user_sub_users;constraint:OnDelete:CASCADE"`
    SuperUsers []*User `gorm:"many2many:user_sub_users.......` // no idea what to fill here

}

What's the gorm configuration I need to add to being able to retrieve the super users (parents) for one entity?

So, as an example, imagine that I have the following

Table users
| ID | name   |
--------------=
| 1  | Alice  |
| 2  | Bob    |
| 3  | Joe    |
| 4  | Manuel |
---------------

Table users_sub_users
| ID | user_id  | sub_user_id |
-------------------------------
| 1  |   1      |    2        |
| 2  |   1      |    3        |
| 3  |   4      |    1        |
-------------------------------

So if I retrieve the user Alice, I want to get the following:

Alice ->
   SubUsers: [Bob, Joe]
   SuperUsers: [Manuel]
children hierarchy go-gorm self-reference parent
1 Answers

You can specify the fields in the table user_sub_users with joinForeignKey and joinReferences.

Instead of:

SubUsers   []*User `gorm:"many2many:user_sub_users"`

it would be more explicit:

SubUsers   []*User `gorm:"many2many:user_sub_users;joinForeignKey:sub_user_id;joinReferences:user_id;"`

For the SuperUsers just swap the fields:

SuperUsers []*User `gorm:"many2many:user_sub_users;joinForeignKey:user_id;joinReferences:sub_user_id;"`

Minimal example:

package main

import (
    "fmt"

    "github.com/glebarez/sqlite"
    "gorm.io/gorm"
)

type User struct {
    gorm.Model
    Name       string
    SubUsers   []*User `gorm:"many2many:user_sub_users;joinForeignKey:sub_user_id;joinReferences:user_id;"`
    SuperUsers []*User `gorm:"many2many:user_sub_users;joinForeignKey:user_id;joinReferences:sub_user_id;"`
}

func main() {
    db, err := gorm.Open(sqlite.Open("test.db"), &gorm.Config{})
    if err != nil {
        panic(err)
    }

    db.AutoMigrate(&User{})

    alice := &User{Name: "Alice"}
    db.Create(alice)
    bob := &User{Name: "Bob"}
    db.Create(bob)
    joe := &User{Name: "Joe"}
    db.Create(joe)
    manuel := &User{Name: "Manuel"}
    db.Create(manuel)

    err = db.Model(alice).Association("SubUsers").Append(bob, joe)
    if err != nil {
        panic(err)
    }
    err = db.Model(manuel).Association("SubUsers").Append(alice)
    if err != nil {
        panic(err)
    }

    var user User
    err = db.Preload("SubUsers").Preload("SuperUsers").First(&user, alice.ID).Error
    if err != nil {
        panic(err)
    }
    for _, u := range user.SubUsers {
        fmt.Println("Subuser", u.Name)
    }
    for _, u := range user.SuperUsers {
        fmt.Println("Superuser", u.Name)
    }
}
some-user0
Related