I am trying to learn Go and Gorm by creating a small prototype order management application. The database is MySQL. With simple inquiries, Gorm was stellar. However, when you try to get a result set that includes a one-to-many combination with a one-to-one relationship, it seems that Gorm is not suitable. Undoubtedly, this is my lack of understanding, which is actually falling. I can't seem to find any online examples of what I'm trying to accomplish. Any help would be greatly appreciated.
Go structures
// 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 request
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 2dB 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 request
order := &Order db.Where("id = ? and status = ?", reqOrder.id, "cart") .Preload("OrderItems").Preload("OrderItems.Item").First(&order)
Results (gorm makes 3 db of 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, } }
Perfect results
The “alternative query” above gives perfect query results. However, Gorm makes 3 separate database queries for this. Ideally, the same results will be performed with 1 (or 2) database queries.
This can be done in MySQL with multiple connections. Gorm allows union. But I was hoping to take advantage of some of Gorm's relational magicians.
Thanks a bunch!