Bulk Update/Remove associations #6748

shanehughes1990 · 2023-12-14T21:42:28Z

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.

The text was updated successfully, but these errors were encountered:

adampl · 2024-03-08T09:35:02Z

@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 · 2024-04-09T00:45:36Z

@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 · 2024-06-03T19:34:29Z

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

Mont4 · 2024-06-13T13:22:31Z

1

tanakornon · 2024-06-16T06:34:42Z

1

alexander-bruun · 2024-07-10T14:29:45Z

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 :)

raphoester · 2024-09-23T12:39:28Z

There is a very simple solution to that.
Just use a soft delete with a deleted_at column.
The associations with the deleted_at field set will be updated, and not deleted, because they will still be in the relations slice. They will then get ignored in the next queries.

shanehughes1990 added the type:question general questions label Dec 14, 2023

shanehughes1990 assigned jinzhu Dec 14, 2023

Provide feedback

Saved searches

Use saved searches to filter your results more quickly

Bulk Update/Remove associations #6748

Bulk Update/Remove associations #6748

shanehughes1990 commented Dec 14, 2023

adampl commented Mar 8, 2024

Millerlm012 commented Apr 9, 2024 •

edited

Loading

alexpts commented Jun 3, 2024

Mont4 commented Jun 13, 2024

tanakornon commented Jun 16, 2024

alexander-bruun commented Jul 10, 2024 •

edited

Loading

raphoester commented Sep 23, 2024

Bulk Update/Remove associations #6748

Bulk Update/Remove associations #6748

Comments

shanehughes1990 commented Dec 14, 2023

Your Question

The document you expected this should be explained

Expected answer

adampl commented Mar 8, 2024

Millerlm012 commented Apr 9, 2024 • edited Loading

alexpts commented Jun 3, 2024

Mont4 commented Jun 13, 2024

tanakornon commented Jun 16, 2024

alexander-bruun commented Jul 10, 2024 • edited Loading

raphoester commented Sep 23, 2024

Millerlm012 commented Apr 9, 2024 •

edited

Loading

alexander-bruun commented Jul 10, 2024 •

edited

Loading