Golang Gorm 一对多关系与一对一关系

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

Golang Gorm one-to-many with has-one

问题

我正在尝试通过构建一个小型原型订单管理应用程序来学习Go和Gorm。数据库使用的是MySQL。对于简单的查询，Gorm表现出色。然而，当尝试获取涉及一对多和一对一关系组合的结果集时，Gorm似乎有些不足。毫无疑问，实际上是我对此缺乏理解。我似乎找不到任何在线示例来完成我想要实现的目标。非常感谢任何帮助。

下面是代码中的Go结构体和数据库表：

// Order
type Order struct {
    gorm.Model
    Status      string
    OrderItems  []OrderItem
}

// Order line item
type OrderItem struct {
    gorm.Model
    OrderID     uint
    ItemID      uint
    Item        Item
    Quantity    int
}

// Product
type Item struct {
    gorm.Model
    ItemName    string
    Amount      float32
}

数据库表：

orders
id | status
 1 | pending

order_items
id | order_id | item_id | quantity
 1 | 1        | 1       | 1
 2 | 1        | 2       | 4

items
id | item_name   | amount
 1 | Go Mug      | 12.49
 2 | Go Keychain | 6.95
 3 | Go T-Shirt  | 17.99

当前查询：

order := &Order{}
if err := db.Where("id = ? and status = ?", reqOrder.id, "pending").
    First(&order).Error; err != nil {
    fmt.Printf(err.Error())
}

db.Model(&order).Association("OrderItems").Find(&order.OrderItems)

结果（Gorm进行了2次数据库查询）：

order == Order {
  id: 1,
  status: pending,
  OrderItems[]: {
    {
      ID: 1,
      OrderID: 1,
      ItemID: 1,
      Item: nil,
      Quantity: 1,
    },
    {
      ID: 2,
      OrderID: 1,
      ItemID: 2,
      Item: nil,
      Quantity: 4,
    }
 }

替代查询：

order := &Order{}
db.Where("id = ? and status = ?", reqOrder.id, "cart").
    Preload("OrderItems").Preload("OrderItems.Item").First(&order)

结果（Gorm进行了3次数据库查询）：

order == Order {
  id: 1,
  status: pending,
  OrderItems[]: {
    {
      ID: 1,
      OrderID: 1,
      ItemID: 1,
      Item: {
        ID: 1,
        ItemName: Go Mug,
        Amount: 12.49,
      }
      Quantity: 1,
    },
    {
      ID: 2,
      OrderID: 1,
      ItemID: 2,
      Item: {
        ID: 2,
        ItemName: Go Keychain,
        Amount: 6.95,
      },
      Quantity: 4,
    }
 }

理想结果：

上述的"替代查询"产生了理想的查询结果。然而，Gorm进行了3次单独的数据库查询来实现这一点。理想情况下，可以通过1次（或2次）数据库查询来完成相同的结果。

在MySQL中可以通过几个连接来实现这一点。Gorm允许连接操作。但是，我希望能够利用Gorm的一些关系魔法。

非常感谢！

英文:

I'm trying to learn Go and Gorm by building a little prototype order management app. The database is MySQL. With simple queries Gorm has been stellar. However, when trying to obtain a result set involving a combination one-to-many with a has-one relationship Gorm seems to fall short. No doubt, it is my lack of understanding that is actually falling short. I can't seem to find any online examples of what I am trying to accomplish. Any help would be greatly appreciated.

Go Structs

// Order
type Order struct {
	gorm.Model
	Status  string
	OrderItems   []OrderItem
}

// Order line item
type OrderItem struct {
	gorm.Model
	OrderID uint
	ItemID  uint
	Item 	Item
	Quantity int
}

// Product
type Item struct {
	gorm.Model
	ItemName     string
	Amount       float32
}

Database tables

orders
id | status
 1 | pending

order_items
id | order_id | item_id | quantity
 1 | 1        | 1       | 1
 2 | 1        | 2       | 4

items
id | item_name   | amount
 1 | Go Mug      | 12.49
 2 | Go Keychain | 6.95
 3 | Go T-Shirt  | 17.99

Current query

order := &Order 
if err := db.Where("id = ? and status = ?", reqOrder.id, "pending")
.First(&order).Error; err != nil {
    fmt.Printf(err.Error())
}

db.Model(&order).Association("OrderItems").Find(&order.OrderItems)

Results (gorm makes 2 db queries)

order == Order {
  id: 1,
  status: pending,
  OrderItems[]: {
    {
      ID: 1,
      OrderID: 1,
      ItemID: 1,
      Item: nil,
      Quantity: 1,
    },
    {
      ID: 2,
      OrderID: 1,
      ItemID: 2,
      Item: nil,
      Quantity: 4,
    }
 }

Alternative query

order := &Order
db.Where("id = ? and status = ?", reqOrder.id, "cart")
.Preload("OrderItems").Preload("OrderItems.Item").First(&order)

Results (gorm makes 3 db queries)

order == Order {
  id: 1,
  status: pending,
  OrderItems[]: {
    {
      ID: 1,
      OrderID: 1,
      ItemID: 1,
      Item: {
        ID: 1,
        ItemName: Go Mug,
        Amount: 12.49,
      }
      Quantity: 1,
    },
    {
      ID: 2,
      OrderID: 1,
      ItemID: 2,
      Item: {
        ID: 2,
        ItemName: Go Keychain,
        Amount: 6.95,
      },
      Quantity: 4,
    }
 }

Ideal results

The "Alternative query" above produces the ideal query results. However, Gorm makes 3 separate database queries to do so. Ideally, the same results would be accomplished with 1 (or 2) database queries.

This could be accomplished in MySQL with a couple of joins. Gorm allows for joins. But, I was hoping to take advantage of some of Gorm's relational magic.

Thanks a bunch!

答案1

得分: 11

根据这个问题描述，gorm并不支持使用join来预加载其他结构体的值。如果你想继续使用gorm并且希望能够使用join来加载值，你必须使用gorm中提供的SQL Builder，并编写一些代码来扫描所需的值。

如果可以选择使用xorm，它支持加载结构体的值。在这里的"find"项目下有详细描述。

注意：我没有扫描所有字段，只扫描了足够的字段来说明问题。

示例代码如下：

package main

import (
	"log"

	"github.com/jinzhu/gorm"
	_ "github.com/jinzhu/gorm/dialects/sqlite"
	"github.com/kylelemons/godebug/pretty"
)

// Order
type Order struct {
	gorm.Model
	Status     string
	OrderItems []OrderItem
}

// Order line item
type OrderItem struct {
	gorm.Model
	OrderID  uint
	ItemID   uint
	Item     Item
	Quantity int
}

// Product
type Item struct {
	gorm.Model
	ItemName string
	Amount   float32
}

var (
	items = []Item{
		{ItemName: "Go Mug", Amount: 12.49},
		{ItemName: "Go Keychain", Amount: 6.95},
		{ItemName: "Go Tshirt", Amount: 17.99},
	}
)

func main() {
	db, err := gorm.Open("sqlite3", "/tmp/gorm.db")
	db.LogMode(true)
	if err != nil {
		log.Panic(err)
	}
	defer db.Close()

	// Migrate the schema
	db.AutoMigrate(&OrderItem{}, &Order{}, &Item{})

	// Create Items
	for index := range items {
		db.Create(&items[index])
	}
	order := Order{Status: "pending"}
	db.Create(&order)
	item1 := OrderItem{OrderID: order.ID, ItemID: items[0].ID, Quantity: 1}
	item2 := OrderItem{OrderID: order.ID, ItemID: items[1].ID, Quantity: 4}
	db.Create(&item1)
	db.Create(&item2)

	// Query with joins
	rows, err := db.Table("orders").Where("orders.id = ? and status = ?", order.ID, "pending").
		Joins("Join order_items on order_items.order_id = orders.id").
		Joins("Join items on items.id = order_items.id").
		Select("orders.id, orders.status, order_items.order_id, order_items.item_id, order_items.quantity" +
			", items.item_name, items.amount").Rows()
	if err != nil {
		log.Panic(err)
	}

	defer rows.Close()
	// Values to load into
	newOrder := &Order{}
	newOrder.OrderItems = make([]OrderItem, 0)

	for rows.Next() {
		orderItem := OrderItem{}
		item := Item{}
		err = rows.Scan(&newOrder.ID, &newOrder.Status, &orderItem.OrderID, &orderItem.ItemID, &orderItem.Quantity, &item.ItemName, &item.Amount)
		if err != nil {
			log.Panic(err)
		}
		orderItem.Item = item
		newOrder.OrderItems = append(newOrder.OrderItems, orderItem)
	}
	log.Print(pretty.Sprint(newOrder))
}

输出结果如下：

/tmp/main.go.go:55) 
[2018-06-18 18:33:59]  [0.74ms]  INSERT INTO "items" ("created_at","updated_at","deleted_at","item_name","amount") VALUES ('2018-06-18 18:33:59','2018-06-18 18:33:59',NULL,'Go Mug','12.49')  
[1 rows affected or returned ] 
(/tmp/main.go.go:55) 
[2018-06-18 18:33:59]  [0.50ms]  INSERT INTO "items" ("created_at","updated_at","deleted_at","item_name","amount") VALUES ('2018-06-18 18:33:59','2018-06-18 18:33:59',NULL,'Go Keychain','6.95')  
[1 rows affected or returned ] 
(/tmp/main.go.go:55) 
[2018-06-18 18:33:59]  [0.65ms]  INSERT INTO "items" ("created_at","updated_at","deleted_at","item_name","amount") VALUES ('2018-06-18 18:33:59','2018-06-18 18:33:59',NULL,'Go Tshirt','17.99')  
[1 rows affected or returned ] 
(/tmp/main.go.go:58) 
[2018-06-18 18:33:59]  [0.71ms]  INSERT INTO "orders" ("created_at","updated_at","deleted_at","status") VALUES ('2018-06-18 18:33:59','2018-06-18 18:33:59',NULL,'pending')  
[1 rows affected or returned ] 
(/tmp/main.go.go:61) 
[2018-06-18 18:33:59]  [0.62ms]  INSERT INTO "order_items" ("created_at","updated_at","deleted_at","order_id","item_id","quantity") VALUES ('2018-06-18 18:33:59','2018-06-18 18:33:59',NULL,'49','145','1')  
[1 rows affected or returned ] 
(/tmp/main.go.go:62) 
[2018-06-18 18:33:59]  [0.45ms]  INSERT INTO "order_items" ("created_at","updated_at","deleted_at","order_id","item_id","quantity") VALUES ('2018-06-18 18:33:59','2018-06-18 18:33:59',NULL,'49','146','4')  
[1 rows affected or returned ] 
(/tmp/main.go.go:69) 
[2018-06-18 18:33:59]  [0.23ms]  SELECT orders.id, orders.status, order_items.order_id, order_items.item_id, order_items.quantity, items.item_name, items.amount FROM "orders" Join order_items on order_items.order_id = orders.id Join items on items.id = order_items.id WHERE (orders.id = '49' and status = 'pending')  
[0 rows affected or returned ] 
--- ONLY ONE QUERY WAS USED TO FILL THE STRUCT BELOW
2018/06/18 18:33:59 {Model:      {ID:        49,
CreatedAt: 0001-01-01 00:00:00 +0000 UTC,
UpdatedAt: 0001-01-01 00:00:00 +0000 UTC,
DeletedAt: nil},
Status:     "pending",
OrderItems: [{Model:    {ID:        0,
CreatedAt: 0001-01-01 00:00:00 +0000 UTC,
UpdatedAt: 0001-01-01 00:00:00 +0000 UTC,
DeletedAt: nil},
OrderID:  49,
ItemID:   145,
Item:     {Model:    {ID:        0,
CreatedAt: 0001-01-01 00:00:00 +0000 UTC,
UpdatedAt: 0001-01-01 00:00:00 +0000 UTC,
DeletedAt: nil},
ItemName: "Go Mug",
Amount:   12.489999771118164},
Quantity: 1},
{Model:    {ID:        0,
CreatedAt: 0001-01-01 00:00:00 +0000 UTC,
UpdatedAt: 0001-01-01 00:00:00 +0000 UTC,
DeletedAt: nil},
OrderID:  49,
ItemID:   146,
Item:     {Model:    {ID:        0,
CreatedAt: 0001-01-01 00:00:00 +0000 UTC,
UpdatedAt: 0001-01-01 00:00:00 +0000 UTC,
DeletedAt: nil},
ItemName: "Go Keychain",
Amount:   6.949999809265137},
Quantity: 4}]}
英文:

As described in this issue, gorm is not designed to use joins to preload other structs values. If you would like to continue to use gorm and have the ability to use joins to load values, one must use the SQL Builder exposed in gorm, and write some code to scan the desired values.

This would become burdensome if there are numerous tables that have to be accounted for.
If xorm is available as an option, they support loading struct values. Described under the find bullet point, here.

Note: I did not scan all the fields, just enough to get the point across.

EXAMPLE:

package main
import (
"log"
"github.com/jinzhu/gorm"
_ "github.com/jinzhu/gorm/dialects/sqlite"
"github.com/kylelemons/godebug/pretty"
)
// Order
type Order struct {
gorm.Model
Status     string
OrderItems []OrderItem
}
// Order line item
type OrderItem struct {
gorm.Model
OrderID  uint
ItemID   uint
Item     Item
Quantity int
}
// Product
type Item struct {
gorm.Model
ItemName string
Amount   float32
}
var (
items = []Item{
{ItemName: "Go Mug", Amount: 12.49},
{ItemName: "Go Keychain", Amount: 6.95},
{ItemName: "Go Tshirt", Amount: 17.99},
}
)
func main() {
db, err := gorm.Open("sqlite3", "/tmp/gorm.db")
db.LogMode(true)
if err != nil {
log.Panic(err)
}
defer db.Close()
// Migrate the schema
db.AutoMigrate(&OrderItem{}, &Order{}, &Item{})
// Create Items
for index := range items {
db.Create(&items[index])
}
order := Order{Status: "pending"}
db.Create(&order)
item1 := OrderItem{OrderID: order.ID, ItemID: items[0].ID, Quantity: 1}
item2 := OrderItem{OrderID: order.ID, ItemID: items[1].ID, Quantity: 4}
db.Create(&item1)
db.Create(&item2)
// Query with joins
rows, err := db.Table("orders").Where("orders.id = ? and status = ?", order.ID, "pending").
Joins("Join order_items on order_items.order_id = orders.id").
Joins("Join items on items.id = order_items.id").
Select("orders.id, orders.status, order_items.order_id, order_items.item_id, order_items.quantity" +
", items.item_name, items.amount").Rows()
if err != nil {
log.Panic(err)
}
defer rows.Close()
// Values to load into
newOrder := &Order{}
newOrder.OrderItems = make([]OrderItem, 0)
for rows.Next() {
orderItem := OrderItem{}
item := Item{}
err = rows.Scan(&newOrder.ID, &newOrder.Status, &orderItem.OrderID, &orderItem.ItemID, &orderItem.Quantity, &item.ItemName, &item.Amount)
if err != nil {
log.Panic(err)
}
orderItem.Item = item
newOrder.OrderItems = append(newOrder.OrderItems, orderItem)
}
log.Print(pretty.Sprint(newOrder))
}

Output:

/tmp/main.go.go:55) 
[2018-06-18 18:33:59]  [0.74ms]  INSERT INTO "items" ("created_at","updated_at","deleted_at","item_name","amount") VALUES ('2018-06-18 18:33:59','2018-06-18 18:33:59',NULL,'Go Mug','12.49')  
[1 rows affected or returned ] 
(/tmp/main.go.go:55) 
[2018-06-18 18:33:59]  [0.50ms]  INSERT INTO "items" ("created_at","updated_at","deleted_at","item_name","amount") VALUES ('2018-06-18 18:33:59','2018-06-18 18:33:59',NULL,'Go Keychain','6.95')  
[1 rows affected or returned ] 
(/tmp/main.go.go:55) 
[2018-06-18 18:33:59]  [0.65ms]  INSERT INTO "items" ("created_at","updated_at","deleted_at","item_name","amount") VALUES ('2018-06-18 18:33:59','2018-06-18 18:33:59',NULL,'Go Tshirt','17.99')  
[1 rows affected or returned ] 
(/tmp/main.go.go:58) 
[2018-06-18 18:33:59]  [0.71ms]  INSERT INTO "orders" ("created_at","updated_at","deleted_at","status") VALUES ('2018-06-18 18:33:59','2018-06-18 18:33:59',NULL,'pending')  
[1 rows affected or returned ] 
(/tmp/main.go.go:61) 
[2018-06-18 18:33:59]  [0.62ms]  INSERT INTO "order_items" ("created_at","updated_at","deleted_at","order_id","item_id","quantity") VALUES ('2018-06-18 18:33:59','2018-06-18 18:33:59',NULL,'49','145','1')  
[1 rows affected or returned ] 
(/tmp/main.go.go:62) 
[2018-06-18 18:33:59]  [0.45ms]  INSERT INTO "order_items" ("created_at","updated_at","deleted_at","order_id","item_id","quantity") VALUES ('2018-06-18 18:33:59','2018-06-18 18:33:59',NULL,'49','146','4')  
[1 rows affected or returned ] 
(/tmp/main.go.go:69) 
[2018-06-18 18:33:59]  [0.23ms]  SELECT orders.id, orders.status, order_items.order_id, order_items.item_id, order_items.quantity, items.item_name, items.amount FROM "orders" Join order_items on order_items.order_id = orders.id Join items on items.id = order_items.id WHERE (orders.id = '49' and status = 'pending')  
[0 rows affected or returned ] 
--- ONLY ONE QUERY WAS USED TO FILL THE STRUCT BELOW
2018/06/18 18:33:59 {Model:      {ID:        49,
CreatedAt: 0001-01-01 00:00:00 +0000 UTC,
UpdatedAt: 0001-01-01 00:00:00 +0000 UTC,
DeletedAt: nil},
Status:     "pending",
OrderItems: [{Model:    {ID:        0,
CreatedAt: 0001-01-01 00:00:00 +0000 UTC,
UpdatedAt: 0001-01-01 00:00:00 +0000 UTC,
DeletedAt: nil},
OrderID:  49,
ItemID:   145,
Item:     {Model:    {ID:        0,
CreatedAt: 0001-01-01 00:00:00 +0000 UTC,
UpdatedAt: 0001-01-01 00:00:00 +0000 UTC,
DeletedAt: nil},
ItemName: "Go Mug",
Amount:   12.489999771118164},
Quantity: 1},
{Model:    {ID:        0,
CreatedAt: 0001-01-01 00:00:00 +0000 UTC,
UpdatedAt: 0001-01-01 00:00:00 +0000 UTC,
DeletedAt: nil},
OrderID:  49,
ItemID:   146,
Item:     {Model:    {ID:        0,
CreatedAt: 0001-01-01 00:00:00 +0000 UTC,
UpdatedAt: 0001-01-01 00:00:00 +0000 UTC,
DeletedAt: nil},
ItemName: "Go Keychain",
Amount:   6.949999809265137},
Quantity: 4}]}

huangapple
  • 本文由 发表于 2016年3月6日 08:57:37
  • 转载请务必保留本文链接：https://go.coder-hub.com/35821810.html
  • go
  • go-gorm
  • one-to-many
  • one-to-one
匿名

发表评论

匿名网友

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

确定