Gorm Associations Many to Many

Question

I understand that gorm Many to Many associations creates a join table but what if I want/need a custom join table with additional fields

My example below

So I have two gorm Models

type Exercise struct {
    gorm.Model
    Name        string `gorm:"not null" json:"name"`
    Description string `gorm:"not null json:"description"`
}
type Workout struct {
    gorm.Model
    Name string `gorm:"not null" json:"name"`
    CreatedAt time.Time `gorm:"not null" json:"created_at"`
}

And if I use the gorm many to many associations it will create a workout_exercise join table but how would I go about ensuring that additional fields like reps and sets get filled out. Is that something that would need to be done in the controller and not really something gorm handles. I have done somethings like this with Nodejs and sequelize but really new to the go and gorm world.

user_904521 · Answer

A bit late to the party but... I was aiming to create something similar, and after spending a full day on it, and hitting a wall, took a step back to rethink it...

After some contemplation, many games, and good advice from a friend, decided to approach from a slightly different angle.

If you look at it differently, instead of a many-to-many you can "rephrase" it as two has-one (or belongs-to), and two one-to-many:

exercise has many workout-exercise
workout has many workout-exercise
workout-exercise has on workout and one exercise

Also, during my games, I realized that properties (columns) of the many-to-many table are not referenced anywhere in the model above so it is not useful anyway.

If you look at workout-exercise as a "first-class" model, it starts to make a little more sense and become more usable:

You can create exercises - it is sort of a static pool of resources
You can create a workout
You can then populate the workout with exercises using the workout-exercise model.

All in all, what I suggest would look like:

type Exercise struct {
    gorm.Model
    Name        string `gorm:"not null" json:"name"`
    Description string `gorm:"not null" json:"description"`
    Workouts    []WorkoutExercise
}

type Workout struct {
    gorm.Model
    Name      string    `gorm:"not null" json:"name"`
    CreatedAt time.Time `gorm:"not null" json:"created_at"`
    Exercises []WorkoutExercise
}

type WorkoutExercise struct {
    gorm.Model
    WorkoutID  uint
    Workout    Workout `gorm:"foreignKey:WorkoutID;references:ID"`
    ExerciseID uint
    Exercise   Exercise `gorm:"foreignKey:ExerciseID;references:ID"`
    Sets       uint
    Reps       uint
    Weights    uint
}

It results with the following schema (which I believe is what needed):

gym=# \dt
               List of relations
 Schema |       Name        | Type  |   Owner   
--------+-------------------+-------+-----------
 public | installations     | table | gym
 public | workout_exercises | table | gym
 public | workouts          | table | gym
(3 rows)

gym=# \d workouts
                                       Table "public.workouts"
   Column   |           Type           | Collation | Nullable |               Default                
------------+--------------------------+-----------+----------+--------------------------------------
 id         | bigint                   |           | not null | nextval('workouts_id_seq'::regclass)
 created_at | timestamp with time zone |           | not null | 
 updated_at | timestamp with time zone |           |          | 
 deleted_at | timestamp with time zone |           |          | 
 name       | text                     |           | not null | 
Indexes:
    "workouts_pkey" PRIMARY KEY, btree (id)
    "idx_workouts_deleted_at" btree (deleted_at)
Referenced by:
    TABLE "workout_exercises" CONSTRAINT "fk_workouts_exercises" FOREIGN KEY (workout_id) REFERENCES workouts(id)

gym=# \d exercises
                                       Table "public.exercises"
   Column    |           Type           | Collation | Nullable |                Default                
-------------+--------------------------+-----------+----------+---------------------------------------
 id          | bigint                   |           | not null | nextval('exercises_id_seq'::regclass)
 created_at  | timestamp with time zone |           |          | 
 updated_at  | timestamp with time zone |           |          | 
 deleted_at  | timestamp with time zone |           |          | 
 name        | text                     |           | not null | 
 description | text                     |           | not null | 
Indexes:
    "exercises_pkey" PRIMARY KEY, btree (id)
    "idx_exercises_deleted_at" btree (deleted_at)
Referenced by:
    TABLE "workout_exercises" CONSTRAINT "fk_exercises_workouts" FOREIGN KEY (exercise_id) REFERENCES exercises(id)

gym=# \d workout_exercises
                                       Table "public.workout_exercises"
   Column    |           Type           | Collation | Nullable |                    Default                    
-------------+--------------------------+-----------+----------+-----------------------------------------------
 id          | bigint                   |           | not null | nextval('workout_exercises_id_seq'::regclass)
 created_at  | timestamp with time zone |           |          | 
 updated_at  | timestamp with time zone |           |          | 
 deleted_at  | timestamp with time zone |           |          | 
 workout_id  | bigint                   |           |          | 
 exercise_id | bigint                   |           |          | 
 sets        | bigint                   |           |          | 
 reps        | bigint                   |           |          | 
 weights     | bigint                   |           |          | 
Indexes:
    "workout_exercises_pkey" PRIMARY KEY, btree (id)
    "idx_workout_exercises_deleted_at" btree (deleted_at)
Foreign-key constraints:
    "fk_exercises_workouts" FOREIGN KEY (exercise_id) REFERENCES exercises(id)
    "fk_workouts_exercises" FOREIGN KEY (workout_id) REFERENCES workouts(id)

If you think about it, pulling the exercises for a workout, without the additional properties does not make sense. If you want to get just the exercises for a workout, you can pull it from the intermediary model*:

workout := Workout
db.Preload("WorkoutExercise.Exercise").First(&workout, 10)

for _, we := range workout.Exercises {
    // access reps, etc directly from we
    // and access the exercise from the property we.Exercise
    log.Printf("do %d sets of %d repetitions of %s", we.Sets, we.Reps, 
               we.Exercise.Name)
}

*disclaimer - have not actually run this code (the rest works). Do check out Nested Preloading to achieve some deeply nested eager-loading.

user_9430029 · Answer

type Exercise struct {
    gorm.Model
    Name        string `gorm:"not null" json:"name"`
    Description string `gorm:"not null json:"description"`
    Workouts []Workout `gorm:"many2many:exercise_workout;"`
}
type Workout struct {
    gorm.Model
    Name string `gorm:"not null" json:"name"`
    CreatedAt time.Time `gorm:"not null" json:"created_at"`
}

The many2many's tag value is the name of the associate table name you define . And use the following code to load exercises with workout data .

var exercises []Exercise
db.Preload("Workouts").Find(&exercises)

If you want to load workout with exercise data , just add many2many associate in Workout struct , e.g :

type Exercise struct {
    gorm.Model
    Name        string `gorm:"not null" json:"name"`
    Description string `gorm:"not null json:"description"`
    Workouts []Workout `gorm:"many2many:exercise_workout;"`
}
type Workout struct {
    gorm.Model
    Name string `gorm:"not null" json:"name"`
    CreatedAt time.Time `gorm:"not null" json:"created_at"`
    Exercises []Exercise `gorm:"many2many:exercise_workout;"`
}

var workouts []Workout
db.Preload("Exercises").Find(&workouts)

Gorm Associations Many to Many

2 Answers