parenthierarchychildrengo-gormself-reference

Retrieving Parents and Children - Self Referencing Entity gorm


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]

Solution

  • 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)
        }
    }