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.
Comment From: 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?
Comment From: 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.
Comment From: 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
Comment From: 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?
Comment From: 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
Comment From: 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"`