一对一的Golang Gorm [英] Golang Gorm one-to-many with has-one
本文介绍了一对一的Golang Gorm的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！
问题描述
我正在尝试通过构建一个原型订单管理应用程序来学习Go和Gorm.该数据库是MySQL.通过简单的查询，Gorm表现出色.但是，当尝试获得涉及具有一对一关系的一对多组合的结果集时，Gorm似乎不足.毫无疑问，实际上是我缺乏理解.我似乎找不到任何我想完成的在线示例.任何帮助将不胜感激.
执行结构
// 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的一些关系魔术.
感谢一堆！
解决方案
如本 issue ，gorm并非设计为使用联接来预加载其他结构值.如果您想继续使用gorm并能够使用联接加载值，则必须使用在gorm中公开的SQL Builder ，并编写一些代码以扫描所需的值.
如果必须考虑许多表，这将变得很麻烦. 如果 xorm 作为选项可用，则它们支持加载结构值. 此处.
注意:我没有扫描所有字段，仅能说明问题.
示例:
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}]}
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!
解决方案
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}]}
这篇关于一对一的Golang Gorm的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
查看全文
