gorm golang self-referencing onetomany relationship in postgres

Question

I'm trying to create a self-referencing ONE TO MANY reltionship using golang gorm. Each user can create multiple other users (Created), but a user is always created by one other user (UserID).

type User struct {
    ID        uint
    Mail      string
    Password  string
    AuthLevel uint
    Created   []User `gorm:"foreignkey:UserID"`
    UserID    uint
}

var db *gorm.DB
var err error
var (
    users = []User{
        {Mail: "JimmyJohnson@Test.com", Password: "1234", AuthLevel: 1, UserID: 0},
        {Mail: "HowardHills@Test.com", Password: "1234", AuthLevel: 2, UserID: 1},
        {Mail: "CraigColbin@Test.com", Password: "1234", AuthLevel: 2, UserID: 1},
    }
)

func main() {

    //Connection to db

    db.AutoMigrate(&User{})
    for index := range users {
        db.Create(&users[index])
    }

    router.HandleFunc("/users/{id}", GetUser).Methods("GET")

    handler := cors.Default().Handler(router)

    log.Fatal(http.ListenAndServe(":8080", handler))
}

//GetUser Returns one user by ID
func GetUser(w http.ResponseWriter, r *http.Request) {
    params := mux.Vars(r)
    var user User
    var users []User
    db.First(&user, params["id"])
    db.Debug().Model(&user).Related(&users)
    user.Created = users

    json.NewEncoder(w).Encode(&user)
}

With this code the GetUser function returns the following for id = 1 :

{
  "ID": 1,
  "Mail": "JimmyJohnson@Test.com",
  "Password": "1234",
  "AuthLevel": 1,
  "Created": [
    {
      "ID": 2,
      "Mail": "HowardHills@Test.com",
      "Password": "1234",
      "AuthLevel": 2,
      "Created": null,
      "UserID": 1
    }
  ],
  "UserID": 2
}

So it looks like my relationship is set up the wrong way.

UserID is suppose to be referencing the ID of the creator, and when db.Debug().Model(&user).Related(&users) is called I would like it to query : SELECT * FROM "users" WHERE ("user_id" = "id") instead of SELECT * FROM "users" WHERE ("id" = "user_id").

So far the documentation or other similar questions havent been able to help me, as they are adressing many to many realtionships.

Any help would be greatly appreciated :)

user_415628 · Answer

You are using db.Related which is from the old version of the library. V2 has more robust methods for handling relations. If you're stuck in V1 the following may still work (I found Related in v1 to be buggy, but Preload should work).

In V2 this works as you expect:

type User struct {
    ID        uint
    Mail      string
    Password  string
    AuthLevel uint
    Created   []User `gorm:"foreignkey:UserID"`
    UserID    *uint
}

//...

user1 := User{Mail: "JimmyJohnson@Test.com", Password: "1234", AuthLevel: 1}
db.Create(&user1)

user2 := User{Mail: "HowardHills@Test.com", Password: "1234", AuthLevel: 2, UserID: &user1.ID}
db.Create(&user2)

var result User
db.Preload("Created").First(&result, user1.ID)
// result.Created[0] is user2

Note that I made the UserID an *uint and didn't give it a value when creating user1, this avoids a foreign key violation when trying to insert 0 for this column.

Also, by using Preload you don't have to make another call to Related or Association.

user_3484955 · Answer

Error on user2 initialization

cannot use &node1.ID (type *uint) as type uint in field value

gorm golang self-referencing onetomany relationship in postgres

2 Answers