How to soft delete from many2many associations table?

[Sophie1142]

Your Question

I'd like to soft-delete instead of hard-delete records in a custom many2many custom associations table so that I can have a history of record. I've read the docs a few times through and tried a few different things, but I'm still at a lost.

Here is a fully-working test file on Go playground, with snippets below:

Here are simplified versions of my models:

var db *gorm.DB

type (
	Load struct {
		gorm.Model
		ExternalID string  `gorm:"uniqueIndex"`
		Emails            []Email `gorm:"many2many:email_loads;"`
		IsPlaceholder     bool    `json:"isPlaceholder"`
	}

	Email struct {
		gorm.Model
		ExternalID string `gorm:"uniqueIndex"`
		Loads      []Load `gorm:"many2many:email_loads;"`
	}

	EmailLoad struct {
		CreatedAt time.Time
		UpdatedAt time.Time
		DeletedAt gorm.DeletedAt `gorm:"softDelete:flag"`
		EmailID   uint           `gorm:"primaryKey"`
		LoadID    uint           `gorm:"primaryKey"`
	}
)

The DB table correctly has the 3 time-tracking columns in the email_loads table when I run migrate.

Here is the DeleteLoads function I'm testing.

func DeleteLoads(ctx context.Context) error {
	return db.Transaction(func(tx *gorm.DB) error {
		var loadsToDelete []Load
		err := tx.Where("created_at = updated_at AND updated_at < ? AND is_placeholder = true", time.Now().AddDate(0, 0, -7)).
			Find(&loadsToDelete).Error
		if err != nil {
			return fmt.Errorf("error finding loads: %w", err)
		}

		// FIXME: I want this to set the join table's deleted_at field, not hard-delete the association row
		return tx.Select("Emails").Delete(&loadsToDelete).Error
	})

}

And here is the test (helper functions not included for brevity, see Go playground link for full definitions):

func TestGormJoinTableSoftDelete(t *testing.T) {

	ctx := context.Background()
	MustOpenTestDB(ctx, "beacon_test_db")

	ClearDB(t)

	email := Email{
		ExternalID: "email1",
	}

	now := time.Now()
	L8D := now.AddDate(0, 0, -8)
	loads := []Load{
		{
			ExternalID: "load1",
		},
		{
			Model: gorm.Model{
				CreatedAt: L8D,
				UpdatedAt: L8D,
			},
			ExternalID: "placeholderLoad",
			IsPlaceholder:     true,
		},
	}
	email.Loads = loads

	err := UpsertEmail(ctx, &email)
	require.NoError(t, err)

	// Override Gorm's automated time-tracking behavior to match delete conditions
	require.NoError(t, db.Model(&email.Loads[1]).UpdateColumn("updated_at", L8D).Error)

	t.Run("OK", func(t *testing.T) {
		err = DeleteLoads(ctx)
		require.NoError(t, err)

		// Verify there's only 1 load now associated with email
		// NOTE: Once soft deletion of associations is enabled, this should still return 1, not 2
		dbEmail, err := GetEmailByExternalID(ctx, "email1")
		require.NoError(t, err)
		require.Len(t, dbEmail.Loads, 1)
		require.False(t, dbEmail.Loads[0].IsPlaceholder)

		// Verify placeholder email was deleted
		var dbLoads []Load
		err = db.Unscoped().Where("is_placeholder = TRUE").Find(&dbLoads).Error
		require.NoError(t, err)
		assert.Len(t, dbLoads, 1)
		assert.NotEmpty(t, dbLoads[0].DeletedAt)
		assert.True(t, dbLoads[0].IsPlaceholder)

		// Verify other association was soft deleted, not hard deleted
		var associations []EmailLoad
		err = db.Unscoped().Model(&EmailLoad{}).Where("deleted_at IS NULL").Find(&associations).Error
		require.NoError(t, err)

		// FIXME this fails because there's no row where deleted_at IS NOT NULL
		assert.Len(t, associations, 1)
		assert.NotEmpty(t, associations[0].DeletedAt)
	},
	)
}

When I run the test, everything is fine except the association is hard-deleted from the email_loads table instead of soft-deleted.
I also want to ensure that if soft-deleting an association is possible, then preloading Loads when getting an Email from the DB still excludes records that are soft-deleted, like all other scoped Gorm queries.

The document you expected this should be explained

https://gorm.io/docs/associations.html#Delete-Associations
https://gorm.io/docs/delete.html#Delete-Flag

Expected answer

• Association record should be soft deleted, with deleted_at set to NOT NULL.
• When getting email and preloading associated loads, Gorm query should exclude soft deleted associations like other Gorm queries.

[Sophie1142]

Hey @jinzhu I know you're probably slammed but checking in on this