goone-to-manyone-to-onego-gorm

Golang Gorm one-to-many with has-one


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!


Solution

  • 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}]}