Your Question
I have a nested schema. The nested relationships are many-many relationships. How do I delete nested the data until it's bottom most child ?
for eg: The below schema contains 3 tables
type Product struct {
gorm.Model
ProductName string `gorm:"type:varchar(250);column:product_name"`
ProductCode string `gorm:"type:varchar(250);column:product_code"`
ProductVariant []ProductVariant `gorm:"ForeignKey:ProductID"`
}
type ProductVariant struct {
gorm.Model
IsPrimary bool `gorm:"type:bool;column:is_primary"`
ProductID int `gorm:"type:bigint;column:product_id"`
Size string `gorm:"type:text;column:size"`
Colour string `gorm:"type:text;column:color"`
ProductVariantImage []ProductVariantImage `gorm:"ForeignKey:ProductVariantID"`
}
type ProductVariantImage struct {
gorm.Model
ProductVariantID int `gorm:"type:bigint;column:product_variant_id"`
MediaID string `gorm:"type:text;column:media_id"`
}
How can I delete rows of all the tables i.e Product, ProductVariant and ProductVariantImage by just using Product.ID ?
Using the below command only deletes upto one level.
product := Product{}
product.ID = 10
db.Select(clause.Associations).Delete(&product)
i.e it deletes data from Product and ProductVariant table but does not delete data present in ProductVariantImage table.
Expected answer
Delete all the nested children of the given schema using the primary key of the parent schema.
Comment From: ghost
@LochanRn hello, I try to run following code in https://github.com/go-gorm/playground:
func TestGORM(t *testing.T) {
user := User{Name: "jinzhu", Pets: []*Pet{
{Name: "1", Toy: Toy{
Name: "2",
}},
}}
//var total int64
DB.Create(&user)
DB.Select(clause.Associations).Delete(&user)
}
result:
[0.093ms] [rows:1] INSERT INTO `toys` (`created_at`,`updated_at`,`deleted_at`,`name`,`owner_id`,`owner_type`) VALUES ("2021-10-18 19:39:45.279","2021-10-18 19:39:45.279",NULL,"2","1","pets") ON CONFLICT (`id`) DO UPDATE SET `owner_type`=`excluded`.`owner_type`,`owner_id`=`excluded`.`owner_id`
[0.314ms] [rows:1] INSERT INTO `pets` (`created_at`,`updated_at`,`deleted_at`,`user_id`,`name`) VALUES ("2021-10-18 19:39:45.279","2021-10-18 19:39:45.279",NULL,1,"1") ON CONFLICT (`id`) DO UPDATE SET `user_id`=`excluded`.`user_id`
[1.217ms] [rows:1] INSERT INTO `users` (`created_at`,`updated_at`,`deleted_at`,`name`,`age`,`birthday`,`company_id`,`manager_id`,`active`) VALUES ("2021-10-18 19:39:45.279","2021-10-18 19:39:45.279",NULL,"jinzhu",0,NULL,NULL,NULL,false)
[0.169ms] [rows:0] UPDATE `accounts` SET `deleted_at`="2021-10-18 19:39:45.28" WHERE `accounts`.`user_id` = 1 AND `accounts`.`deleted_at` IS NULL
[0.220ms] [rows:1] UPDATE `pets` SET `deleted_at`="2021-10-18 19:39:45.28" WHERE `pets`.`user_id` = 1 AND `pets`.`deleted_at` IS NULL
[0.056ms] [rows:0] UPDATE `toys` SET `deleted_at`="2021-10-18 19:39:45.281" WHERE `toys`.`owner_type` = "users" AND `toys`.`owner_id` = 1 AND `toys`.`deleted_at` IS NULL
[0.029ms] [rows:0] DELETE FROM `user_speaks` WHERE `user_speaks`.`user_id` IN (NULL)
[0.020ms] [rows:0] DELETE FROM `user_friends` WHERE `user_friends`.`user_id` IN (NULL)
[0.161ms] [rows:0] UPDATE `users` SET `deleted_at`="2021-10-18 19:39:45.281" WHERE `users`.`manager_id` = 1 AND `users`.`deleted_at` IS NULL
[0.022ms] [rows:0] DELETE FROM `user_speaks` WHERE `user_speaks`.`user_id` = 1
[0.018ms] [rows:0] DELETE FROM `user_friends` WHERE `user_friends`.`user_id` = 1
[1.303ms] [rows:1] UPDATE `users` SET `deleted_at`="2021-10-18 19:39:45.281" WHERE `users`.`id` = 1 AND `users`.`deleted_at` IS NULL
--- PASS: TestGORM (0.00s)
PASS
doc: https://gorm.io/docs/associations.html#Delete-with-Select
Comment From: zhuxubin01
` package main
import ( "gorm.io/driver/mysql" "gorm.io/gorm" "gorm.io/gorm/clause" "time" )
type Model struct {
ID uint gorm:"primaryKey"
CreatedAt time.Time
UpdatedAt time.Time
DeletedAt gorm.DeletedAt gorm:"index"
}
type User struct { gorm.Model Pets []*Pet Name string }
type Pet struct { gorm.Model UserID uint Name string Toy Toy }
type Toy struct { gorm.Model PetID uint Name string }
func main() { dsn := "root:root@tcp(127.0.0.1:3306)/gorm_test?charset=utf8mb4&parseTime=True&loc=Local" db, err := gorm.Open(mysql.Open(dsn), &gorm.Config{}) if err != nil { panic(err) }
db.AutoMigrate(&User{}, &Pet{}, &Toy{})
user := User{Name: "jinzhu", Pets: []*Pet{
{Name: "1", Toy: Toy{
Name: "2",
}},
}}
//var total int64
db.Create(&user)
db.Debug().Select(clause.Associations).Delete(&user)
} `
I try to test it, but Toy is not deleted
Comment From: zhuxubin01
user := User{Name: "jinzhu", Pets: []*Pet{ {Name: "1", Toy: Toy{ Name: "2", }}, }} //var total int64 DB.Create(&user) DB.Select(clause.Associations).Delete(&user)
这里会删除应该是User里面定义了的[]Toy,如果User里面没有定义[]Toy,只是Pet里面定义了Toy不会被删除