Question 340992 in PostgreSQL Question posted in
The official documentation can be found here.

Postgresql – gorm preload loads duplicated values for many to many relation

i have a many to many relation like a customers and orders.

 type EmployeeShiftList struct {
ID        uint
UUID      uuid.UUID  `gorm:"<-:create"`
FirstName string     `validate:"required,max=250"`
LastName  string     `validate:"omitempty,max=250"`
ShiftMaps []ShiftMap `gorm:"many2many:attendance.employee_shifts;foreignKey:ID;References:ShiftID;joinForeignKey:EmployeeID;joinReferences:ShiftID"}



  type ShiftMap struct {
    ShiftID  uint                  `gorm:"primaryKey"`
    FromDate time.Time
    ToDate   time.Time}

when i preload ShiftMaps using
db.Preload("ShiftMaps").Find(&employeeShiftList) same shift of an employee is populated more than once inside ShiftMaps struct of the customer. how can i avoid this.

Tags:

2

Answers


  1. Chosen as BEST ANSWER
    0

    Since my join table had additional fields FromDate and ToDate it was difficult to populate them using many to many table structure. so i simplified my structure. i created a table employee_shifts. employees

    type EmployeeShiftList struct {
    ID        uint
    UUID      uuid.UUID `gorm:"<-:create"`
    FirstName string    `validate:"required,max=250"`
    LastName  string    `validate:"omitempty,max=250"`
    EmployeeShifts []EmployeeShift `gorm:"foreignKey:EmployeeID"`
}

func (EmployeeShiftList) TableName() string {
     return "employees"
}

type EmployeeShift struct {
    EmployeeID uint
    ShiftID    uint
    Shift      ShiftRef `gorm:"foreignKey:ShiftID;->" json:",omitempty"`
    FromDate   time.Time
    ToDate     time.Time
}

type ShiftRef struct {
    ID        uint      `gorm:"primaryKey"`
    UUID      uuid.UUID `gorm:"<-:create"`
    Name      string    `validate:"required,max=250"`
    StartTime time.Time
    EndTime   time.Time 
}

var employeeShifts []employeeshift_models.EmployeeShiftList
tx = tx.Preload("EmployeeShifts") .Preload("EmployeeShifts.Shift")
if err := tx.Find(&employeeShifts).Error; err != nil {
    return nil, err
}

  2. 0

    One possible solution is to use the distinct option in GORM’s Preload to ensure that only distinct values are loaded. Here’s how you can modify your query:

    db.Preload("ShiftMaps", func(db *gorm.DB) *gorm.DB {
    return db.Distinct("shift_maps.shift_id") // Assuming ShiftID is the unique identifier in ShiftMap
}).Find(&employeeShiftList)
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search