Golang Gorm one-to-many with has-one

Question

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!

Halfslip

I realize this has been asked a while ago, but can you please explain what you are trying to accomplish? i.e. what is the sql query you would like to have run, and we go backwards from there? – Nagging

Answer 1

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

Mavismavra

What if you need to get a list of orders? – Euchology

Change the where to: Where("orders.id in (?) and status = ?", [order.ID], "pending") – Mavismavra