Understanding Foreign Keys/One-to-Many

[gsexton]

How can I use 1 - Many Relationships

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 systemroles (
    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 systemroles ON DELETE CASCADE
    );

CREATE VIEW vUserRoles AS
    SELECT user_roles.*,
        systemroles.role
    FROM user_roles
        INNER JOIN systemroles
        ON systemroles.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.

[github-actions]

The issue has been automatically marked as stale as it missing playground pull request link, which is important to help others understand your issue effectively and make sure the issue hasn't been fixed on latest master, checkout https://github.com/go-gorm/playground for details. it will be closed in 30 days if no further activity occurs. if you are asking question, please use the Question template, most likely your question already answered https://github.com/go-gorm/gorm/issues or described in the document https://gorm.io ✨ Search Before Asking ✨

[github-actions]

The issue has been automatically marked as stale as it missing playground pull request link, which is important to help others understand your issue effectively and make sure the issue hasn't been fixed on latest master, checkout https://github.com/go-gorm/playground for details. it will be closed in 30 days if no further activity occurs. if you are asking question, please use the Question template, most likely your question already answered https://github.com/go-gorm/gorm/issues or described in the document https://gorm.io ✨ Search Before Asking ✨