Bulk Update/Remove associations

[shanehughes1990]

Your Question

My goal for what I am trying to accomplish, Replace database records with contents of input

This can either be delete/insert, range over each record and do db calls. Ideally I want to be able to do it all in bulk just receive said slice and update everything and all there associations (add/update/remove)

go.mod:

	gorm.io/driver/mysql v1.5.2
	gorm.io/gorm v1.25.5

Sample of what would be considered the insert(doesn't exist yet in database), I would expect it to insert the employee with the 3 allowance codes (this part works). I use copier to copy the input xml model to the database model and use that as the input param for UpsertEmployees method.

Mapping from xml to database schema: EmpID = ProcuraEmpID

<?xml version="1.0" encoding="utf-8"?>
<SendEmployees>
	<SendEmployee>
		<EmpID>DEMO776329720</EmpID>
		<AllowanceCodes>
			<AllowanceCode>
				<Code>1</Code>
			</AllowanceCode>
			<AllowanceCode>
				<Code>2</Code>
			</AllowanceCode>
			<AllowanceCode>
				<Code>3</Code>
			</AllowanceCode>
		</AllowanceCodes>
		<AllowActivities>T</AllowActivities>
</SendEmployees>

If I were to then send the same EmpID record with modified allowance codes, I would expect to remove (I need hard delete, not soft delete). allowance code 3 as it's no longer present.

<?xml version="1.0" encoding="utf-8"?>
<SendEmployees>
	<SendEmployee>
		<EmpID>DEMO776329720</EmpID>
		<AllowanceCodes>
			<AllowanceCode>
				<Code>1</Code>
			</AllowanceCode>
			<AllowanceCode>
				<Code>2</Code>
			</AllowanceCode>
		</AllowanceCodes>
		<AllowActivities>T</AllowActivities>
</SendEmployees>

The relevant schema for reference, you can see I manually added the foreighKey/references gorm tags on the Employee AllowanceCodes

type Employee struct {
	ProcuraEmpID           string               `gorm:"primaryKey;column:ProcuraEmpID;type:varchar(14);" json:"ProcuraEmpID" copier:"ProcuraEmpID"`
	AllowanceCodes         []*EmployeeAllowance `gorm:"foreignKey:ProcuraEmpID;references:ProcuraEmpID;" json:"AllowanceCodes" copier:"AllowanceCodes"`
}

type EmployeeAllowance struct {
	ProcuraEmpID string `gorm:"primaryKey;column:ProcuraEmpID;type:varchar(14);" json:"ProcuraEmpID" copier:"-"`
	Code         string `gorm:"primaryKey;column:Code;type:varchar(10);" json:"Code" copier:"Code"`
}

// TableName sets the insert table name for this struct type
func (e *EmployeeAllowance) TableName() string {
	return "EmployeeAllowance"
}

Original Gorm method which works when it comes to inserting and updating the main employee doc, it fails when removing attendance codes not present in the input

func (db *database) UpsertEmployees(employees []*models.Employee) error {
	// save all employees and their associations
	tx := db.Debug().Session(&gorm.Session{FullSaveAssociations: true}).Model(&models.Employee{}).Save(employees)
	if tx.Error != nil {
		return tx.Error
	}

	return nil
}

Research on gorm docs lead me to change it to this, which throws the following error unsupported relations: EmployeeAllowance being the gorm error itself

DEBUG  [2023-12-14T21:17:23] clearing employee allowances for employee DEMO776329720 
ERROR  [2023-12-14T21:17:23] failed to upsert employees: caught error clearing employee allowances for employee DEMO776329720: unsupported relations: EmployeeAllowance 
ERROR  [2023-12-14T21:17:23] Error #01: caught error clearing employee allowances for employee DEMO776329720: unsupported relations: EmployeeAllowance  clientIP="127.0.0.1" dataLength="0" method="POST" path="/inbound/inbound.php" referer="" requestId="d6b4b28f-1d93-430f-9f1f-e1651c61d36a" statusCode="500" userAgent="PostmanRuntime/7.36.0"

func (db *database) UpsertEmployees(employees []*models.Employee) error {
	var association *models.EmployeeAllowance
	// go through each employee and clear the association data
	for _, employee := range employees {
		logger.Debugf("clearing employee allowances for employee %s", employee.ProcuraEmpID)
		if err := db.Unscoped().Model(employee).Association(association.TableName()).Unscoped().Clear(); err != nil {
			return fmt.Errorf("caught error clearing employee allowances for employee %s: %w", employee.ProcuraEmpID, err)
		}
		logger.Debugf("cleared employee allowances for employee %s", employee.ProcuraEmpID)
	}

	// save all employees and their associations
	tx := db.Debug().Session(&gorm.Session{FullSaveAssociations: true}).Model(&models.Employee{}).Save(employees)
	if tx.Error != nil {
		return tx.Error
	}

	return nil
}

I also changed it to this to try replacing it all first, but I get the same relations error with this as well

DEBUG  [2023-12-14T21:19:48] clearing employee allowances for employee DEMO776329720 
ERROR  [2023-12-14T21:19:48] failed to upsert employees: caught error clearing employee allowances for employee DEMO776329720: unsupported relations: EmployeeAllowance 
ERROR  [2023-12-14T21:19:48] Error #01: caught error clearing employee allowances for employee DEMO776329720: unsupported relations: EmployeeAllowance  clientIP="127.0.0.1" dataLength="0" method="POST" path="/inbound/inbound.php" referer="" requestId="80cc2664-5959-4b3e-8260-9836e9e774df" statusCode="500" userAgent="PostmanRuntime/7.36.0"

func (db *database) UpsertEmployees(employees []*models.Employee) error {
	var association *models.EmployeeAllowance
	// go through each employee and replace the association data
	for _, employee := range employees {
		if err := db.Model(&employee).Association(association.TableName()).Replace(&employee.AllowanceCodes); err != nil {
			return fmt.Errorf("caught error replacing employee allowances for employee %s: %w", employee.ProcuraEmpID, err)
		}
	}

	// save all employees and their associations
	tx := db.Debug().Session(&gorm.Session{FullSaveAssociations: true}).Model(&models.Employee{}).Save(employees)
	if tx.Error != nil {
		return tx.Error
	}

	return nil
}

The document you expected this should be explained

I've looked through all gorm docs at this point but the relevant ones

https://gorm.io/docs/has_many.html#Self-Referential-Has-Many https://gorm.io/docs/associations.html#Association-Tags https://gorm.io/docs/associations.html#Delete-Association-Record

Expected answer

The most database efficient way of updating a slice of employees and there associations with input, that includes inserts/updates/deletes(associations only, if no longer present in the input)

Asterisk on efficient as part of the reason for the rewrite is to improve database performance. If I'm able to just take the input and make the database reflect that all in one shot on the slice of employees that would be best. Otherwise I'm open to ranging the slices and doing database modifications on the element level if needed.

[adampl]

@jinzhu TL;DR: There's a bug in updating an object with associations. It doesn't set the object with the provided associations, but only adds new associations, so the preexisting associations don't get deleted (but they should).

[Millerlm012]

@jinzhu I've come across the same issue. Is this in your plans to be worked on and implemented? / Do you need any assistance?

[alexpts]

I expected nested associations to be replaced on update, but new duplicates are created

[Mont4]

+1

[tanakornon]

+1

[alexander-bruun]

I've come across the same issue, I expected omitted keys on update to be deleted, but they are not. New associations can be created, and updated, but not deleted.

I was almost about to switch to a different ORM, or just go with self made plain SQL queries. But I like the migrations and the struct to table generation in GORM so much, that I gave a workaround a try, the following is what i did.

In order to work around the issue I updated my function with a map comparison before / after. Consider a library object, which contains a list of folders. Each folder has a foreign key to the library. This function will update the library and the associated folders, and delete any folders not in the update objects:

func UpdateLibrary(library *models.Library) error {
	// Fetch the current state of the library including associated folders
	var currentLibrary models.Library
	if err := db.Preload("Folders").First(&currentLibrary, library.ID).Error; err != nil {
		return err
	}

	// Create a map of current folder IDs for quick lookup
	currentFolderIDs := make(map[uint]bool)
	for _, folder := range currentLibrary.Folders {
		currentFolderIDs[folder.ID] = true
	}

	// Create a map of new folder IDs for quick lookup and to track seen folders
	newFolderIDs := make(map[uint]bool)
	for _, folder := range library.Folders {
		newFolderIDs[folder.ID] = true
	}

	// Find folders to delete
	for id := range currentFolderIDs {
		if !newFolderIDs[id] {
			// Folder is not in the new list, so delete it using Unscoped()
			if err := db.Unscoped().Delete(&models.Folder{}, id).Error; err != nil {
				return err
			}
		}
	}

	// Save the updated library with FullSaveAssociations
	if err := db.Session(&gorm.Session{FullSaveAssociations: true}).Save(library).Error; err != nil {
		return err
	}

       // Trigger notifications for logic that depends on instant update notifications
	NotifyListeners(models.Notification{Type: "library_updated", Payload: library})
	return nil
}

With this change, it is able to update and remove associations... Not sure if this is the "correct" way to work around the issue, let me know :)