I have a struct which looks like this:
type Category struct {
Code *int `gorm:"unique;primaryKey;"`
ParentCategory *Category `gorm:"foreignKey:Code"`
}
A Category itself can have a ParentCategory which is also from type Category. So it references to itself. If its the first Category it has NO ParentCategory.
I have an array whith 4 Categories, as said, the first one doesnt have ab ParentCategory.
When saving these Categories one after one (Beginning with the first which has NO ParentCategory, I get these errors (Just print here the first two):
Error 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`ps_product_service`.`categories`, CONSTRAINT `fk_categories_parent_category` FOREIGN KEY (`code`) REFERENCES `categories` (`code`))
[20.890ms] [rows:0] INSERT INTO `categories` (`code`) VALUES (0) RETURNING `code`
Error when creating category: Error 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`ps_product_service`.`categories`, CONSTRAINT `fk_categories_parent_category` FOREIGN KEY (`code`) REFERENCES `categories` (`code`))&{Code:0x140003c6a00 ParentCategory:<nil>}
2023/06/19 21:31:44 Error 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`ps_product_service`.`categories`, CONSTRAINT `fk_categories_parent_category` FOREIGN KEY (`code`) REFERENCES `categories` (`code`))
[7.689ms] [rows:0] INSERT INTO `categories` (`code`) VALUES (99) RETURNING `code`
Error when creating category: Error 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`ps_product_service`.`categories`, CONSTRAINT `fk_categories_parent_category` FOREIGN KEY (`code`) REFERENCES `categories` (`code`))&{Code:0x140003c6a20 ParentCategory:<nil>}
I really cant figure out what I have to do here. Could someone help me with this please?
I should have managed your requirements with the following code:
package main
import (
"fmt"
"github.com/samber/lo"
"gorm.io/driver/postgres"
"gorm.io/gorm"
)
type category struct {
Code uint `gorm:"unique;primaryKey;"`
ParentCategoryId *uint
ParentCategory *category `gorm:"foreignKey:ParentCategoryId"`
}
func main() {
dsn := "host=localhost port=54322 user=postgres password=postgres dbname=postgres sslmode=disable"
db, err := gorm.Open(postgres.Open(dsn))
if err != nil {
panic(err)
}
db.AutoMigrate(&category{})
// load dummy data
db.Create(&category{
Code: 1,
ParentCategoryId: nil,
})
db.Create(&category{
Code: 2,
ParentCategoryId: lo.ToPtr[uint](1),
})
db.Create(&category{
Code: 3,
ParentCategoryId: lo.ToPtr[uint](2),
})
db.Create(&category{
Code: 4,
ParentCategoryId: lo.ToPtr[uint](1),
})
// reading logic
var categories []category
if err := db.Model(&category{}).Find(&categories).Error; err != nil {
panic(err)
}
for _, v := range categories {
if v.ParentCategoryId == nil {
fmt.Printf("id: %v\tparentCategoryId: <nil>\n", v.Code)
continue
}
fmt.Printf("id: %v\tparentCategoryId: %v\n", v.Code, *v.ParentCategoryId)
}
}
If I got well your needs, you need to have a table that references itself. That's why I defined the category
struct in this way. Each category has its own ParentCategoryId
unless the category has no parent. If you try to execute the previous code, you should get something like:
id: 1 parentCategoryId: <nil>
id: 2 parentCategoryId: 1
id: 3 parentCategoryId: 2
id: 4 parentCategoryId: 1
Maybe I didn't get something from your question, if that's the case just let me know and I'll update my answer, thanks!