How to Use 1-Many (yes, I read the docs carefully)

[gsexton]

Your Question

I'm at a loss how to make this work. I've extensively experimented, and reviewed the documentation, and I'm just not seeing how to make this go. I'm trying to embed a user's roles in the User model, and it's returning null. I have two models:

type User struct {
	ID        uint      `gorm:"primaryKey" json:"id"`
	UserName     string    `json:"user_name,omitempty"`
	FullName     string    `json:"full_name,omitempty"`
	EmailAddress string    `json:"email_address,omitempty"`
	Password     string    `json:"password,omitempty"`
	ApiKey       string    `json:"api_key,omitempty"`
	Enabled      bool      `json:"enabled,omitempty"`
	LastSignin   time.Time `json:"last_signin,omitempty"`
    Roles        []UserRole `json:"roles"`
}

and:

type UserRole struct {
	UserID uint `gorm:"primaryKey" json:"user_id"`
	RoleID uint `gorm:"primaryKey" json:"role_id"`
    Role string `gorm:"->" json:"role"`
}

func (UserRole) TableName() string {
    return "vuserroles"
}

vUserRoles is:

CREATE TABLE system_roles (
    id BIGINT NOT NULL DEFAULT NEXTVAL('systemroles_seq'),
    role VARCHAR(64),
    created_at TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT CURRENT_TIMESTAMP,
    CONSTRAINT PK_USERROLES PRIMARY KEY (id),
    CONSTRAINT UQ_USERROLES_ROLE UNIQUE (role)
    );

CREATE TABLE user_roles (
    user_id BIGINT NOT NULL,
    role_id BIGINT NOT NULL,
    CONSTRAINT PK_USER_ROLES PRIMARY KEY (user_id, role_id),
    CONSTRAINT FK_USER_ROLES_USERS FOREIGN KEY (user_id) REFERENCES users ON DELETE CASCADE,
    constraint FK_USER_ROLES_ROLES FOREIGN KEY (role_id) REFERENCES system_roles ON DELETE CASCADE
    );

CREATE VIEW vUserRoles AS
    SELECT user_roles.*,
        systemroles.role
    FROM user_roles
        INNER JOIN system_roles
        ON system_roles.id=user_roles.role_id;

I can retrieve the roles directly:

	cfg := config.GetConfig()
	db, err := gorm.Open(postgres.Open(cfg.GetDatabaseDSN()), &gorm.Config{})
	if err != nil {
		fmt.Println(err)
	}
	id := ctx.Param("id")
	var roles []models.UserRole
	db.Where("user_id=?",id).Find(&roles)

returns:

    {
        "user_id": 1,
        "role_id": 1,
        "role": "admin"
    },
    {
        "user_id": 1,
        "role_id": 2,
        "role": "editor"
    }

I tried defining:

    Roles        []UserRole `gorm:"foreignKey:UserID" json:"roles"`

as well as:

    Roles        []UserRole `gorm:"foreignKey:user_id" json:"roles"`

and it doesn't work. They don't generate an error, but roles is null.

   Roles        []UserRole `gorm:"foreignKey:UserIDXX" json:"roles"`.   

generates an error.

The document you expected this should be explained

https://gorm.io/docs/has_many.html

Expected answer

I'd like to know how to have the value for roles populate. I'd really appreciate some help with this. Thanks.

[a631807682]

I don't understand your question.
Is AutoMigrate not working as expected? Or is the query result not as expected? Or wondering how to use View in gorm?

[gsexton]

@a631807682 What's not working is that when I query User, to return an instance, the value for Roles is not populated. I'm following the documentation for how to setup 1->Many as specified in the documentation, and it doesn't work.

I'd like to know how to make it work. In particular, I don't understand how I can query the roles and get data as expected but when I'm embedding the Roles onto the user, it doesn't even though it's precisely following the example (credit cards) in the documentation.

[gsexton]

One additional note.

I revisited the has_many page, and notice that it calls a "Preload()" method to indicate the linked slice should be loaded. I added a Preload() to my statement and it didn't make any difference. My current query statement is:

e := db.Model(&ifType).Order(sort).Preload("Roles").Find(&values).Error

and for the single record:

err = db.Model(&v).Preload("Roles").First(&v, id).Error

[gsexton]

@a631807682 I have a different test case that doesn't involve the view. I have my user model as documented above. Then, I have another model, ItemChecksum

type ItemChecksum struct {
	gorm.Model
	SomeField string
	LastEditedByUserID uint   `json:"last_edited_by_user_id,omitempty"`
	LastEditedBy       User   `gorm:"foreignKey:LastEditedByUserID"`
}

when I query, I get:

    ...
    "last_edited_by_user_id": 7778015,
    "LastEditedBy": {
        "ID": 0,
        "CreatedAt": "0001-01-01T00:00:00Z",
        "UpdatedAt": "0001-01-01T00:00:00Z",
        "DeletedAt": null,
        "enabled": false,
        "last_signin": "0001-01-01T00:00:00Z",
        "roles": null
    }

So it's working better, in that the LastEditedBy JSON object is present, but it doesn't have any data. I setup a logger to run at info level, and I can see the call made to read the record, but no references to the user table are happening.

Loading preload:  LastEditedBy

2023/04/21 15:45:42 /Users/gsexton/Work/xxx/src/handlers/generic.go:104
[125.989ms] [rows:1] SELECT * FROM "item_checksums" WHERE "item_checksums"."id" = $1$ ORDER BY "item_checksums"."id" LIMIT 1
[GIN] 2023/04/21 - 15:45:42 | 200 |  651.607541ms |       127.0.0.1 | GET      "/ItemChecksum/1267100"

I've tried every combination that I can think of for foreignKey and references and I can't seem to figure it out. I also tried calling Preload with clause.Associations per the docs, and that didn't change anything either.

Perhaps if you can help me with this, the other would work.

Just to confirm something: The auto migrate calls really only update the schema to match the model correct? There's not a dependency on calling AutoMigrate to populate some sort of information/linkage table right?

[a631807682]

There's not a dependency on calling AutoMigrate to populate some sort of information/linkage table right?

Yes, we determine the table relationship based on parsing struct and tag, AutoMigrate is not necessary.

I've tried every combination that I can think of for foreignKey and references and I can't seem to figure it out. I also tried calling Preload with clause.Associations per the docs, and that didn't change anything either.

If your table relationships match what we parsed, Preload should work.

It seems to me that what you describe is part of the thing and not the whole thing, so I hope you can provide easy reproduction code, including
table ddl, model definition, and query code

[gsexton]

@a631807682 You're correct. I wasn't showing the whole picture. I made a simplified test case and was able to sort out the problem.

What I was trying to do was use interfaces to create a generic REST API endpoint. Here's an example:

type GormModelAI struct {
	ID        uint      `gorm:"primaryKey;autoincrement" json:"id"`
	CreatedAt time.Time `json:"created_at"`
	UpdatedAt time.Time `json:"updated_at"`
}

type Team struct {
	GormModelAI
	TeamName string `json:"team_name"`
}

type Player struct {
	GormModelAI
	UserName string `json:"user_name"`
	FullName string `json:"full_name"`
	TeamID   uint   `json:"team_id"`
	Team     Team `json:"team"`
}

func getInterfaces() (interface{}, interface{}) {
	v := &models.Player{}
	sl := make([]models.Player, 0)

	return v, sl
}

The code to do the queries was:

	singlePlayer, pls := getInterfaces()


	err = db.Preload("Team").Find(&pls).Error

	if err == nil {
		jtext, _ :=json.MarshalIndent(pls,"","\t")
		fmt.Printf("\nMarshalled string=%s\n",jtext)
	} else {
		fmt.Println(err)
	}

	fmt.Printf("Type of singlePlayer=%T\nType of &singlePlayer=%T",singlePlayer,&singlePlayer)
	db.Preload("Team").First(&singlePlayer, 2)

This code actually worked in that I would get a single player, or a collection of players, but the Team value wasn't loaded.

In my production code, I had two problems. The first was I had a function:

func getInterfaces() (v interface{},sl interface{}) {
	v = &models.Player{}
	sl = make([]models.Player, 0)

	return v, sl
}

what I found is that the type of v and sl was *interface{}. Evidently, when named results are used, the variables are initialized to zero of the type. In this case, for the interface, the dynamic type was *interface{} and the value was initially nil.

When this code is used:

func getInterfaces() (interface{},interface{}) {
	v = &models.Player{}
	sl = make([]models.Player, 0)
	return v, &sl
}

then v is *models.Player, and sl is *[]models.Player.

Once I had that, and understood the types I was returning, then I saw that I needed to change the calls to be just the variable, not the address of the variable:

    err = db.Preload("Team").Find(pls).Error
    db.Preload("Team").First(singlePlayer, 2)

Once those two changes were made, the Pre loading worked as expected. For my original effort with []UserRole, I found the correct definition to be:

    UserRoles    []UserRole `gorm:"foreignKey:UserID;references:ID" json:"user_roles"`