GORM一对一映射问题

huangapple
go评论111阅读模式
英文:

GORM One-to One mapping issue

问题

我在GORM映射中遇到了一个奇怪的问题，我有两个如下所示的结构体。

  1. type ParcelOrder struct {
  2.     gorm.Model
  3.     ID                 int                `json:"id"`
  4.     SenderId           uint               `json:"sender_id"`
  5.     OrderID            string             `json:"order_id"`
  6.     PickupAddress      string             `json:"pickup_address"`
  7.     DeliveryAddress    string             `json:"delivery_address"`
  8.     CreatedAt          time.Time          `json:"created_at"`
  9.     UpdatedAt          time.Time          `json:"updated_at"`
  10.     DeletedAt          sql.NullTime       `json:"deleted_at"`
  11.     ParcelOrderDetails ParcelOrderDetails `gorm:"foreignKey:ParcelOrderID"`
  12. }
  14. type ParcelOrderDetails struct {
  15.     gorm.Model
  16.     ID            int           `json:"id"`
  17.     BikerID       sql.NullInt32 `json:"biker_id"`
  18.     ParcelOrderID int           `json:"parcel_order_id"`
  19.     PickupTime    sql.NullTime  `json:"pickup_time"`
  20.     DeliveryTime  sql.NullTime  `json:"delivery_time"`
  21.     Status        int           `json:"status"`
  22.     CreatedAt     time.Time     `json:"created_at"`
  23.     UpdatedAt     time.Time     `json:"updated_at"`
  24.     DeletedAt     sql.NullTime  `json:"deleted_at"`
  25. }

我正在使用MySQL数据库，当我尝试创建订单时，它没有在第二个表中创建条目。
我使用以下代码进行创建：

  1. order := ParcelOrder{
  2.     DeliveryAddress: pickupdata.DeliveryAddress,
  3.     PickupAddress:   pickupdata.PickupAddress,
  4.     OrderID:         GetUniqueID(),
  5.     SenderId:        userId,
  6.     ParcelOrderDetails: ParcelOrderDetails{
  7.         Status: 0,
  8.     },
  9. }
  10. connection.Create(&order)

此外，当我尝试从表中获取数据时，我收到以下错误：

  1. SELECT `parcel_orders`.`id`,`parcel_orders`.`created_at`,`parcel_orders`.`updated_at`,`parcel_orders`.`deleted_at`,`parcel_orders`.`sender_id`,`parcel_orders`.`order_id`,`parcel_orders`.`pickup_address`,`parcel_orders`.`delivery_address`,`ParcelOrderDetails`.`id` AS `ParcelOrderDetails__id`,`ParcelOrderDetails`.`created_at` AS `ParcelOrderDetails__created_at`,`ParcelOrderDetails`.`updated_at` AS `ParcelOrderDetails__updated_at`,`ParcelOrderDetails`.`deleted_at` AS `ParcelOrderDetails__deleted_at`,`ParcelOrderDetails`.`biker_id` AS `ParcelOrderDetails__biker_id`,`ParcelOrderDetails`.`parcel_order_id` AS `ParcelOrderDetails__parcel_order_id`,`ParcelOrderDetails`.`pickup_time` AS `ParcelOrderDetails__pickup_time`,`ParcelOrderDetails`.`delivery_time` AS `ParcelOrderDetails__delivery_time`,`ParcelOrderDetails`.`status` AS `ParcelOrderDetails__status` FROM `parcel_orders` ParcelOrder LEFT JOIN `parcel_order_details` `ParcelOrderDetails` ON `parcel_orders`.`id` = `ParcelOrderDetails`.`parcel_order_id` WHERE ParcelOrder.sender_id = 2

我的获取代码如下。获取问题非常奇怪，因为别名被分配为ParcelOrder，所以与该名称不匹配的字段仍然使用parcel_orders实际表名。

  1. db.Joins("ParcelOrder").Joins("ParcelOrderDetails").Where("ParcelOrder.sender_id = ?", userId).Find(&order)

该数据库不是由Go应用程序创建的，而是由Laravel应用程序创建的。我正在尝试读取/写入该数据库的数据。

有什么想法吗？

英文:

I have a strange issue with GORM mapping , I have two struct like below.

  1. type ParcelOrder struct {
  2. 	gorm.Model
  3. 	ID                 int                `json:"id"`
  4. 	SenderId           uint               `json:"sender_id"`
  5. 	OrderID            string             `json:"order_id"`
  6. 	PickupAddress      string             `json:"pickup_address"`
  7. 	DeliveryAddress    string             `json:"delivery_address"`
  8. 	CreatedAt          time.Time          `json:"created_at"`
  9. 	UpdatedAt          time.Time          `json:"updated_at"`
  10. 	DeletedAt          sql.NullTime       `json:"deleted_at"`
  11. 	ParcelOrderDetails ParcelOrderDetails `gorm:"foreignKey:ParcelOrderID"`
  12. }
  13. type ParcelOrderDetails struct {
  14. 	gorm.Model
  15. 	ID            int           `json:"id"`
  16. 	BikerID       sql.NullInt32 `json:"biker_id"`
  17. 	ParcelOrderID int           `json:"parcel_order_id"`
  18. 	PickupTime    sql.NullTime  `json:"pickup_time"`
  19. 	DeliveryTime  sql.NullTime  `json:"delivery_time"`
  20. 	Status        int           `json:"status"`
  21. 	CreatedAt     time.Time     `json:"created_at"`
  22. 	UpdatedAt     time.Time     `json:"updated_at"`
  23. 	DeletedAt     sql.NullTime  `json:"deleted_at"`
  24. }

I'm using the Mysql DB, when I try to create the orders it didn't create entries on second table.
For creating, I'm using below codes

  1. order := ParcelOrder{
  2. 		DeliveryAddress: pickupdata.DeliveryAddress,
  3. 		PickupAddress:   pickupdata.PickupAddress,
  4. 		OrderID:         GetUniqueID(),
  5. 		SenderId:        userId,
  6. 		ParcelOrderDetails: ParcelOrderDetails{
  7. 			Status: 0,
  8. 		},
  9. 	}
  10. connection.Create(&order)

Also When I try fetch data from the table Im getting an error like below

  1. SELECT `parcel_orders`.`id`,`parcel_orders`.`created_at`,`parcel_orders`.`updated_at`,`parcel_orders`.`deleted_at`,`parcel_orders`.`sender_id`,`parcel_orders`.`order_id`,`parcel_orders`.`pickup_address`,`parcel_orders`.`delivery_address`,`ParcelOrderDetails`.`id` AS `ParcelOrderDetails__id`,`ParcelOrderDetails`.`created_at` AS `ParcelOrderDetails__created_at`,`ParcelOrderDetails`.`updated_at` AS `ParcelOrderDetails__updated_at`,`ParcelOrderDetails`.`deleted_at` AS `ParcelOrderDetails__deleted_at`,`ParcelOrderDetails`.`biker_id` AS `ParcelOrderDetails__biker_id`,`ParcelOrderDetails`.`parcel_order_id` AS `ParcelOrderDetails__parcel_order_id`,`ParcelOrderDetails`.`pickup_time` AS `ParcelOrderDetails__pickup_time`,`ParcelOrderDetails`.`delivery_time` AS `ParcelOrderDetails__delivery_time`,`ParcelOrderDetails`.`status` AS `ParcelOrderDetails__status` FROM `parcel_orders` ParcelOrder LEFT JOIN `parcel_order_details` `ParcelOrderDetails` ON `parcel_orders`.`id` = `ParcelOrderDetails`.`parcel_order_id` WHERE ParcelOrder.sender_id = 2

My fetch code is like below. Fetch issue is very strange bcoz the alias is assigned as ParcelOrder so fields that are not identifying with that name , instead it still using parcel_orders actual table name

  1. db.Joins("ParcelOrder").Joins("ParcelOrderDetails").Where("ParcelOrder.sender_id = ?", userId).Find(&order)

The DB is not created with Go application it with Laravel application. Im trying to read/write data to that database.

any idea ?

答案1

得分: 2

答案很简单，但理解起来有点棘手。

你只需要将以下代码片段中的一行进行更改：

  1. ParcelOrderDetails ParcelOrderDetails `gorm:"foreignKey:ParcelOrderID"`

改为

  1. ParcelOrderDetails *ParcelOrderDetails `gorm:"foreignKey:ParcelOrderID"`

原因是每次初始化ParcelOrder时，ParcelOrderDetails都会创建一个空模型（非空）。当然，在某些情况下，我们不希望意外创建ParcelOrderOrderDetails，因为它是一个空模型，我认为GORM会处理这种情况。

因此，如果将其更改为*ParcelOrderOrderDetails，它将默认为nil，这意味着GORM不会创建ParcelOrderOrderDetails，反之亦然。

这是完整的代码：

  1. // https://stackoverflow.com/questions/69977516/unsupported-relations-for-schema-with-has-many-relation-in-gorm/70082006#70082006
  2. package main
  4. import (
  5. 	"database/sql"
  6. 	"fmt"
  7. 	"time"
  9. 	"gorm.io/driver/mysql"
  10. 	"gorm.io/gorm"
  11. 	"gorm.io/gorm/logger"
  12. )
  14. type ParcelOrder struct {
  15. 	gorm.Model
  16. 	ID                 int                `json:"id"`
  17. 	SenderId           uint               `json:"sender_id"`
  18. 	OrderID            string             `json:"order_id"`
  19. 	PickupAddress      string             `json:"pickup_address"`
  20. 	DeliveryAddress    string             `json:"delivery_address"`
  21. 	CreatedAt          time.Time          `json:"created_at"`
  22. 	UpdatedAt          time.Time          `json:"updated_at"`
  23. 	DeletedAt          sql.NullTime       `json:"deleted_at"`
  24. 	ParcelOrderDetails *ParcelOrderDetails `gorm:"foreignKey:ParcelOrderID"`
  25. }
  27. type ParcelOrderDetails struct {
  28. 	gorm.Model
  29. 	ID            int           `json:"id"`
  30. 	BikerID       sql.NullInt32 `json:"biker_id"`
  31. 	ParcelOrderID int           `json:"parcel_order_id"`
  32. 	PickupTime    sql.NullTime  `json:"pickup_time"`
  33. 	DeliveryTime  sql.NullTime  `json:"delivery_time"`
  34. 	Status        int           `json:"status"`
  35. 	CreatedAt     time.Time     `json:"created_at"`
  36. 	UpdatedAt     time.Time     `json:"updated_at"`
  37. 	DeletedAt     sql.NullTime  `json:"deleted_at"`
  38. }
  40. var DB *gorm.DB
  42. func main() {
  43. 	databaseConfig := fmt.Sprintf("%s:%s@tcp(%s:%s)/%s?multiStatements=true&parseTime=true", "root", "", "127.0.0.1", "3306", "tester")
  45. 	DB, _ = gorm.Open(mysql.Open(databaseConfig), &gorm.Config{
  46. 		Logger: logger.Default.LogMode(logger.Info),
  47. 	})
  48. 	sqlDB, _ := DB.DB()
  49. 	defer sqlDB.Close()
  51. 	DB.AutoMigrate(&ParcelOrder{}, &ParcelOrderDetails{})
  53. 	order := ParcelOrder{
  54. 		DeliveryAddress: "dummy address",
  55. 		PickupAddress:   "dummy pickup address",
  56. 		OrderID:         "unique-order-id-1",
  57. 		SenderId:        1,
  58. 		ParcelOrderDetails: &ParcelOrderDetails{
  59. 			Status: 0,
  60. 		},
  61. 	}
  62. 	DB.Create(&order)
  63. }

日志：

  1. 2021/11/23 20:57:53 D:/go/src/udemy-solving/00003/main.go:62
  2. [0.780ms] [rows:1] INSERT INTO `parcel_order_details` (`created_at`,`updated_at`,`deleted_at`,`biker_id`,`parcel_order_id`,`pickup_time`,`delivery_time`,`status`) VALUES ('2021-11-23 20:57:53.393','2021-11-23 20:57:53.393',NULL,NULL,4,NULL,NULL,0) ON DUPLICATE KEY UPDATE `parcel_order_id`=VALUES(`parcel_order_id`)
  3. 2021/11/23 20:57:53 D:/go/src/udemy-solving/00003/main.go:62
  4. [14.900ms] [rows:1] INSERT INTO `parcel_orders` (`created_at`,`updated_at`,`deleted_at`,`sender_id`,`order_id`,`pickup_address`,`delivery_address`) VALUES ('2021-11-23 20:57:53.392','2021-11-23 20:57:53.392',NULL,1,'unique-order-id-1','dummy pickup address','dummy address')
英文:

The answer is simple yet tricky to be understood.

You just need to add change:

  1. ParcelOrderDetails ParcelOrderDetails `gorm:"foreignKey:ParcelOrderID"`

into

  1. ParcelOrderDetails *ParcelOrderDetails `gorm:"foreignKey:ParcelOrderID"`

The reason is ParcelOrderDetails will create a empty model (not null) everytime ParcelOrder is initialized, of course on some cases we don't intended to accidentally create ParcelOrderOrderDetails because it is an empty model, I think GORM handle this case.

So if you make it into *ParcelOrderOrderDetails, it will be nil by default, which mean GORM will not create ParcelOrderOrderDetails and vice versa.

This is my full code

  1. // https://stackoverflow.com/questions/69977516/unsupported-relations-for-schema-with-has-many-relation-in-gorm/70082006#70082006
  2. package main
  3. import (
  4. "database/sql"
  5. "fmt"
  6. "time"
  7. "gorm.io/driver/mysql"
  8. "gorm.io/gorm"
  9. "gorm.io/gorm/logger"
  10. )
  11. type ParcelOrder struct {
  12. gorm.Model
  13. ID                 int                `json:"id"`
  14. SenderId           uint               `json:"sender_id"`
  15. OrderID            string             `json:"order_id"`
  16. PickupAddress      string             `json:"pickup_address"`
  17. DeliveryAddress    string             `json:"delivery_address"`
  18. CreatedAt          time.Time          `json:"created_at"`
  19. UpdatedAt          time.Time          `json:"updated_at"`
  20. DeletedAt          sql.NullTime       `json:"deleted_at"`
  21. ParcelOrderDetails *ParcelOrderDetails `gorm:"foreignKey:ParcelOrderID"`
  22. }
  23. type ParcelOrderDetails struct {
  24. gorm.Model
  25. ID            int           `json:"id"`
  26. BikerID       sql.NullInt32 `json:"biker_id"`
  27. ParcelOrderID int           `json:"parcel_order_id"`
  28. PickupTime    sql.NullTime  `json:"pickup_time"`
  29. DeliveryTime  sql.NullTime  `json:"delivery_time"`
  30. Status        int           `json:"status"`
  31. CreatedAt     time.Time     `json:"created_at"`
  32. UpdatedAt     time.Time     `json:"updated_at"`
  33. DeletedAt     sql.NullTime  `json:"deleted_at"`
  34. }
  35. var DB *gorm.DB
  36. func main() {
  37. databaseConfig := fmt.Sprintf("%s:%s@tcp(%s:%s)/%s?multiStatements=true&parseTime=true", "root", "", "127.0.0.1", "3306", "tester")
  38. DB, _ = gorm.Open(mysql.Open(databaseConfig), &gorm.Config{
  39. Logger: logger.Default.LogMode(logger.Info),
  40. })
  41. sqlDB, _ := DB.DB()
  42. defer sqlDB.Close()
  43. DB.AutoMigrate(&ParcelOrder{}, &ParcelOrderDetails{})
  44. order := ParcelOrder{
  45. DeliveryAddress: "dummy address",
  46. PickupAddress:   "dummy pickup address",
  47. OrderID:         "unique-order-id-1",
  48. SenderId:        1,
  49. ParcelOrderDetails: &ParcelOrderDetails{
  50. Status: 0,
  51. },
  52. }
  53. DB.Create(&order)
  54. }

Logging:

  1. 2021/11/23 20:57:53 D:/go/src/udemy-solving/00003/main.go:62
  2. [0.780ms] [rows:1] INSERT INTO `parcel_order_details` (`created_at`,`updated_at`,`deleted_at`,`biker_id`,`parcel_order_id`,`pickup_time`,`delivery_time`,`status`) VALUES ('2021-11-23 20:57:53.393','2021-11-23 20:57:53.393',NULL,NULL,4,NULL,NULL,0) ON DUPLICATE KEY UPDATE `parcel_order_id`=VALUES(`parcel_order_id`)
  3. 2021/11/23 20:57:53 D:/go/src/udemy-solving/00003/main.go:62
  4. [14.900ms] [rows:1] INSERT INTO `parcel_orders` (`created_at`,`updated_at`,`deleted_at`,`sender_id`,`order_id`,`pickup_address`,`delivery_address`) VALUES ('2021-11-23 20:57:53.392','2021-11-23 20:57:53.392',NULL,1,'unique-order-id-1','dummy pickup address','dummy address')

huangapple
  • 本文由 发表于 2021年11月15日 14:20:34
  • 转载请务必保留本文链接：https://go.coder-hub.com/69970135.html
  • go
  • go-gorm
  • mysql
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定