Gorm GORM doesn't fill the foreign key id when doing an insert into

Your Question

I'm having some issues with GORM. I have the following structs:

type Player struct {
    ID                       uint `gorm:"primarykey"`
    ClubID                   uint
    Club                     Club
}
type Club struct {
    ID   uint `gorm:"primarykey"`
    Name string `gorm:"unique"`
}

I'm loading this info from a file, something like:

func TestSQLite(t *testing.T) {

    pr, err := db.Init("file:memdb1?mode=memory&cache=shared")
    if err != nil {
        fmt.Println("Error on Init: " + err.Error())
        t.Fail()
    }

    p := domain.Player{
        Club: domain.Club{Name: "Hello"},
        Shooting: 23,
        PlayerPositions: []domain.Position{
            {
                Name: "SM",
            },
        },
    }
    if err := pr.Create(&p); err != nil {
        fmt.Println("Error creating player: " + err.Error())
        t.Fail()
    }
    c := domain.Player{
        Club: domain.Club{Name: "Hello"},
        Shooting: 23,
        PlayerPositions: []domain.Position{
            {
                Name: "SM",
            },
        },
    }
    if err := pr.Create(&c); err != nil {
        fmt.Println("Error creating player: " + err.Error())
        t.Fail()
    }

    t.Logf("%d", p.ClubID)
    t.Logf("%d", c.ClubID)
    if c.ClubID != p.ClubID {
        t.Fail()
    }
}

and as result I have:

    db_test.go:45: 1
    db_test.go:46: 0

Code for Create is

    return pr.preload().Debug().Create(&p).Error

and preload is

func (pr *PlayerRepository) preload() *gorm.DB {
    return pr.connection.
        Preload("Club")
}

I'm not sure what I'm doing wrong.

I forgot to add debug information:

=== RUN   TestSQLite

2020/12/27 19:39:56 /Users/emanuele/personal/roadmap/io/db/db.go:41
[0.059ms] [rows:1] INSERT INTO `clubs` (`name`) VALUES ("Hello") ON CONFLICT DO NOTHING

2020/12/27 19:39:56 /Users/emanuele/personal/roadmap/io/db/db.go:41
[0.032ms] [rows:1] INSERT INTO `positions` (`name`) VALUES ("KM") ON CONFLICT DO NOTHING

2020/12/27 19:39:56 /Users/emanuele/personal/roadmap/io/db/db.go:41
[0.026ms] [rows:1] INSERT INTO `player_positions` (`player_id`,`position_id`) VALUES (1,1) ON CONFLICT DO NOTHING

2020/12/27 19:39:56 /Users/emanuele/personal/roadmap/io/db/db.go:41
[0.659ms] [rows:1] INSERT INTO `players` (`player_url`,`short_name`,`long_name`,`age`,`date_of_birth`,`height`,`weight`,`nationality_id`,`club_id`,`league_id`,`league_rank`,`overall`,`potential`,`value`,`wage`,`preferred_foot`,`international_reputation`,`weak_foot`,`skill_moves`,`work_rate`,`body_type`,`real_face`,`release_clause_eur`,`team_position_id`,`team_jersey_number`,`loaned_from`,`joined`,`contract_valid_until`,`nation_position_id`,`nation_jersey_number`,`pace`,`shooting`,`passing`,`dribbling`,`defending`,`physic`,`goal_keeper_diving`,`goal_keeper_handling`,`goal_keeper_kicking`,`goal_keeper_reflexes`,`goal_keeper_speed`,`goal_keeper_positioning`,`attacking_crossing`,`attacking_finishing`,`attacking_heading_accuracy`,`attacking_short_passing`,`attacking_volleys`,`skill_dribbling`,`skill_curve`,`skill_fk_accuracy`,`skill_long_passing`,`skill_ball_control`,`movement_acceleration`,`movement_sprint_speed`,`movement_agility`,`movement_reactions`,`movement_balance`,`power_shot_power`,`power_jumping`,`power_stamina`,`power_strength`,`power_long_shots`,`mentality_aggression`,`mentality_interceptions`,`mentality_positioning`,`mentality_vision`,`mentality_penalties`,`mentality_composure`,`defending_marking`,`defending_standing_tackle`,`defending_sliding_tackle`,`goal_keeping_diving`,`goal_keeping_handling`,`goal_keeping_kicking`,`goal_keeping_positioning`,`goal_keeping_reflexes`,`left_striker`,`striker`,`right_striker`,`left_winger`,`left_forward`,`center_forward`,`right_forward`,`right_winger`,`left_attacking_midfielder`,`central_attacking_midfielder`,`right_attacking_midfielder`,`left_midfielder`,`left_centre_midfielder`,`central_midfielder`,`right_centre_midfielder`,`right_midfielder`,`left_wing_back`,`left_defensive_midfielder`,`central_defensive_midfielder`,`right_defensive_midfielder`,`right_wing_back`,`left_back`,`left_center_attack`,`centre_back`,`right_centre_back`,`right_back`) VALUES ("","","",0,"",0,0,0,1,0,0,0,0,0,0,"","",0,0,"","","",0,0,0,"","","",0,0,0,23,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0)

2020/12/27 19:39:56 /Users/emanuele/personal/roadmap/io/db/db.go:41
[0.024ms] [rows:0] INSERT INTO `clubs` (`name`) VALUES ("Hello") ON CONFLICT DO NOTHING

2020/12/27 19:39:56 /Users/emanuele/personal/roadmap/io/db/db.go:41
[0.023ms] [rows:1] INSERT INTO `positions` (`name`) VALUES ("SM") ON CONFLICT DO NOTHING

2020/12/27 19:39:56 /Users/emanuele/personal/roadmap/io/db/db.go:41
[0.026ms] [rows:1] INSERT INTO `player_positions` (`player_id`,`position_id`) VALUES (2,2) ON CONFLICT DO NOTHING

2020/12/27 19:39:56 /Users/emanuele/personal/roadmap/io/db/db.go:41
[0.396ms] [rows:1] INSERT INTO `players` (`player_url`,`short_name`,`long_name`,`age`,`date_of_birth`,`height`,`weight`,`nationality_id`,`club_id`,`league_id`,`league_rank`,`overall`,`potential`,`value`,`wage`,`preferred_foot`,`international_reputation`,`weak_foot`,`skill_moves`,`work_rate`,`body_type`,`real_face`,`release_clause_eur`,`team_position_id`,`team_jersey_number`,`loaned_from`,`joined`,`contract_valid_until`,`nation_position_id`,`nation_jersey_number`,`pace`,`shooting`,`passing`,`dribbling`,`defending`,`physic`,`goal_keeper_diving`,`goal_keeper_handling`,`goal_keeper_kicking`,`goal_keeper_reflexes`,`goal_keeper_speed`,`goal_keeper_positioning`,`attacking_crossing`,`attacking_finishing`,`attacking_heading_accuracy`,`attacking_short_passing`,`attacking_volleys`,`skill_dribbling`,`skill_curve`,`skill_fk_accuracy`,`skill_long_passing`,`skill_ball_control`,`movement_acceleration`,`movement_sprint_speed`,`movement_agility`,`movement_reactions`,`movement_balance`,`power_shot_power`,`power_jumping`,`power_stamina`,`power_strength`,`power_long_shots`,`mentality_aggression`,`mentality_interceptions`,`mentality_positioning`,`mentality_vision`,`mentality_penalties`,`mentality_composure`,`defending_marking`,`defending_standing_tackle`,`defending_sliding_tackle`,`goal_keeping_diving`,`goal_keeping_handling`,`goal_keeping_kicking`,`goal_keeping_positioning`,`goal_keeping_reflexes`,`left_striker`,`striker`,`right_striker`,`left_winger`,`left_forward`,`center_forward`,`right_forward`,`right_winger`,`left_attacking_midfielder`,`central_attacking_midfielder`,`right_attacking_midfielder`,`left_midfielder`,`left_centre_midfielder`,`central_midfielder`,`right_centre_midfielder`,`right_midfielder`,`left_wing_back`,`left_defensive_midfielder`,`central_defensive_midfielder`,`right_defensive_midfielder`,`right_wing_back`,`left_back`,`left_center_attack`,`centre_back`,`right_centre_back`,`right_back`) VALUES ("","","",0,"",0,0,0,0,0,0,0,0,0,0,"","",0,0,"","","",0,0,0,"","","",0,0,0,24,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0)
--- FAIL: TestSQLite (0.01s)

The document you expected this should be explained

https://gorm.io/docs/belongs_to.html or https://gorm.io/docs/create.html

Expected answer

What I need to fix to get the expected test to pass.

Comment From: invasionofsmallcubes

To add more info, maybe what I wanted to do is using a different reference like stated here: https://gorm.io/docs/belongs_to.html#Override-References

So I changed the structs this way:

type Club struct {
    ID   uint   `gorm:"primarykey"`
    Name string
}
type Player struct {
    ID                       uint `gorm:"primarykey"`
    ClubID                   string
    Club                     Club `gorm:"references:Name"`
}

but when I try now I get

[error] invalid field found for struct roadmap/domain.Player's field Club, need to define a foreign key for relations or it need 
to implement the Valuer/Scanner interface

PS: not clear why in the docs CompanyID string becomes string.

Comment From: github-actions[bot]

This issue has been marked as invalid question, please give more information by following the Question template, if you believe there is a bug of GORM, please create a pull request that could reproduce the issue on https://github.com/go-gorm/playground, the issue will be closed in 2 days if no further activity occurs. most likely your question already answered https://github.com/go-gorm/gorm/issues or described in the document https://gorm.io ✨ Search Before Asking ✨

Comment From: invasionofsmallcubes

This is the PR on playground that proves the point: https://github.com/go-gorm/playground/pull/241

Not sure what else you need. Question looks clear to me, so let me know what info are not clear.

Comment From: jinzhu

Fixed, thank you for your report.

Comment From: invasionofsmallcubes

Thanks @jinzhu!

I updated my go mod to master branch (gorm.io/gorm v1.20.10-0.20201228102055-065787c54ef8

According to your commit (https://github.com/go-gorm/gorm/commit/065787c54ef80199482ef3d245de213e7f751423#diff-8d07e51e92aa7412603a8362e69a118a5c91a595e2ab5df818708b17991ca506R77) the structs are now defined this way:

type Club struct {
    ID   uint   `gorm:"primarykey"`
    Name string `gorm:"unique"`
}

type Player struct {
    ID                       uint `gorm:"primarykey"`
    ClubID                   uint
    Club                     Club `gorm:"References:Name"`
}

now I get

Error creating player: strconv.ParseUint: parsing "Hello": invalid syntax

from the test above.

if I change to ClubID string then it works but then the Player table will have a field with

ClubID:Hello

I was assuming to find the integer key (ID) from Club, not the string. Is this the behaviour you expected?

Because at this point doing

type Club struct {
    ID   uint   `gorm:"primarykey"`
    Name string `gorm:"unique"`
}
type Player struct {
    ID                      uint `gorm:"primarykey"`
    ClubID               string
    Club                   Club `gorm:"References:Name"`
}

or doing

type Club struct {
    Name string `gorm:"primarykey"`
}
type Player struct {
    ID                      uint `gorm:"primarykey"`
    ClubID               string
    Club                   Club
}

is basically the same for Player which I don't think it makes sense?

Comment From: jinzhu

yes, this is the expected behavior, ClubID's reference is Name, it need has same type with it.

BTW, you could also rename the ClubID to ClubName like:

type Club struct {
    ID   uint   `gorm:"primarykey"`
    Name string `gorm:"unique"`
}
type Player struct {
    ID                      uint `gorm:"primarykey"`
    ClubName       string
    Club                 Club `gorm:"References:Name"`
}

Comment From: jinzhu

BTW, If you want to use club's ID, you could define the struct like (no references tag):

type Club struct {
    ID   uint   `gorm:"primarykey"`
    Name string `gorm:"unique"`
}
type Player struct {
    ID                      uint `gorm:"primarykey"`
    ClubID               uint
    Club                   Club
}

Comment From: invasionofsmallcubes

Thanks @jinzhu but unfortunately it doesn't work the way I want. I need to create these players in batch from a CSV and every time I create the Club object (without ID). If I use it without reference, the second time the club is not recovered because the ID is empty so the reference is needed for this two work. Otherwise I need to check before adding every player if the club already exists but then it means there is not reason to use an ORM. But I can use the reference just fine. It's just weird that the foreign key is the string and not the primary key.

Comment From: jinzhu

You have to query the Club's ID in this case, but I think for your case, you don't necessary to have another primary key ID for Club, just use the name or code

Comment From: invasionofsmallcubes

No I removed everything and just used the code as primary key and be done with it for my case is more than enough.

If I had more knowledge of the internal I would propose a PR to add my suggested behaviour.

To me seems quite normal (in enterprise ORM I used) that you want to keep a technical key and a logical key separated and hydrate the PK when needed (like in my creation case case).

Thanks for the support though!

Comment From: sandeshrathod

**type Clients struct { gorm.Model Name string json:"name" Mobile int64 json:"mobile" gorm:"unique" Gmail string json:"gmail" Brands []Brand json:"brand" }

type Brand struct { gorm.Model Brandname string json:"brandname" ClientsID uint json:"clientID" }**

var client Clients
rbody, _ := ioutil.ReadAll(r.Body)
err := json.Unmarshal(rbody, &client)
if err != nil {
    log.Panic(err)
    return
}

e := db.Create(&client.Brands)


_****var client Clients
rbody, _ := ioutil.ReadAll(r.Body)
err := json.Unmarshal(rbody, &client)
if err != nil {
    log.Panic(err)
    return
}

e := db.Create(&client.Brands)****_

request :- error showing { "ID": 0, "brand": [ { "ID": 0, "CreatedAt": "0001-01-01T00:00:00Z", "UpdatedAt": "0001-01-01T00:00:00Z", "DeletedAt": null, "brandname": "sandesh", "clientID": 1 } ] }