Trouble with QRM while joining the same tables multiple times using aliases

[olivierstamand]

Hi, i'm doing a query where i need to join multiples times the same tables. I'm using aliases to differentiate them, but when mapping to my struct, it's not working. Here is my query .

`
workoutMedia := Media.AS("workout_media")
exerciseMedia := Media.AS("exercise_media")
muscleMedia := Media.AS("muscle_media")

stmt := postgres.SELECT(
	Workouts.WorkoutID,
	Workouts.Name,
	Workouts.Description,
	Workouts.Template,
	Workouts.CreatedAt,
	Workouts.MadeBy,
	Workouts.TemplateWorkoutID,
	workoutMedia.ID,
	workoutMedia.Blurhash,
	WorkoutBlocks.WorkoutBlockID,
	WorkoutBlocks.Index,
	WorkoutBlocks.Notes,
	WorkoutExercises.WorkoutExerciseID,
	WorkoutExercises.ExerciseID,
	WorkoutExercises.Index,
	Exercises.FrName,
	Exercises.EnName,
	Exercises.FrInstructions,
	Exercises.EnInstructions,
	Exercises.ExerciseID,
	Exercises.MediaID,
	exerciseMedia.ID,
	exerciseMedia.Blurhash,
	Exercises.Official,
	Exercises.ExerciseTypeID,
	Exercises.TypicalWeightIncrement,
	Muscles.FrName,
	Muscles.EnName,
	Muscles.MuscleID,
	Muscles.MediaID,
	muscleMedia.ID,
	Sets.SetID,
	Sets.Weight,
	Sets.Reps,
	Sets.Rest,
	Sets.Duration,
	Sets.Distance,
	Sets.Index,
	Sets.WorkoutExerciseID,
).
	FROM(Workouts.
		LEFT_JOIN(WorkoutBlocks, Workouts.WorkoutID.EQ(WorkoutBlocks.WorkoutID)).
		LEFT_JOIN(WorkoutExercises, WorkoutBlocks.WorkoutBlockID.EQ(WorkoutExercises.WorkoutBlockID)).
		LEFT_JOIN(Exercises, WorkoutExercises.ExerciseID.EQ(Exercises.ExerciseID)).
		LEFT_JOIN(ExercisesMuscles, Exercises.ExerciseID.EQ(ExercisesMuscles.ExerciseID)).
		LEFT_JOIN(Muscles, ExercisesMuscles.MuscleID.EQ(Muscles.MuscleID)).
		LEFT_JOIN(exerciseMedia, Exercises.MediaID.EQ(exerciseMedia.ID)).
		LEFT_JOIN(workoutMedia, Workouts.ThumbnailMedia.EQ(workoutMedia.ID)).
		LEFT_JOIN(muscleMedia, Muscles.MediaID.EQ(muscleMedia.ID)).
		LEFT_JOIN(Sets, WorkoutExercises.WorkoutExerciseID.EQ(Sets.WorkoutExerciseID)),
	).
	WHERE(Workouts.TemplateWorkoutID.EQ(postgres.UUID(workoutID)).OR(Workouts.WorkoutID.EQ(postgres.UUID(workoutID)))).
	ORDER_BY(postgres.GREATEST(Workouts.CreatedAt, Workouts.UpdatedAt).DESC(), Workouts.WorkoutID, WorkoutBlocks.Index.ASC(), WorkoutExercises.Index.ASC(), Sets.Index.ASC())
var dest []dbObjects.WorkoutDetails
log.Println(stmt.DebugSql())
err := stmt.QueryContext(ctx, db, &dest)`

And here is my struct being mapped to

type WorkoutDetails struct {
	model.Workouts
	WorkoutMedia  model.Media
	WorkoutBlocks []struct {
		model.WorkoutBlocks
		WorkoutExercises []struct {
			Exercise      model.Exercises
			ExerciseMedia model.Media
			Muscles       []struct {
				model.Muscles
				MuscleMedia model.Media
			}
			model.WorkoutExercises
			Sets []struct {
				model.Sets
			}
		}
	}
}

Everything maps correctly, except Medias.

[go-jet]

For self join you need to specify an alias tag:

type WorkoutDetails struct {
	model.Workouts
	WorkoutMedia  model.Media `alias:"workout_media.*"`
	WorkoutBlocks []struct {
		model.WorkoutBlocks
		WorkoutExercises []struct {
			Exercise      model.Exercises
			ExerciseMedia model.Media `alias:"exerciseMedia.*"`
			Muscles       []struct {
				model.Muscles
				MuscleMedia model.Media `alias:"muscleMedia.*"`
			}
			model.WorkoutExercises
			Sets []struct {
				model.Sets
			}
		}
	}
}

Also you have a lot of joins here, in case of performance issue consider SELECT_JSON instead(for whole or part of the query).