Find all in a list containing ForeignKey MySQL Gorm get null result

user_16659422 0
Viewed 36

I am writing a function to process get all data from 2 linked tables, Order and OrderItem with foreign keys attached as the code below. But the result I get is OrderItem[ ] Null, even though I have Preload("OrderItems"). 

type Order struct {
    Model
    TransactionId     string  `json:"transaction_id" gorm:"null"`
    TransactionMethod string  `json:"transaction_method"`
    CreatePayParams   string  `json:"create_payparams" gorm:"null"`
    StatusPayParams   bool    `json:"status_payparams" gorm:"default:false"`
    UserId            uint    `json:"user_id"`
    OrderCode         string  `json:"order_code"`
    Amount            float64 `json:"amount"`
    Complete          bool    `json:"-" gorm:"default:false"`
    ReceiverName      string  `json:"receiver_name"`
    Phone             string  `json:"phone"`
    Email             string  `json:"email"`
    DetailAddress     string  `json:"detail_address" gorm:"null"`
    Ward              string  `json:"ward" gorm:"null"`
    District          string  `json:"district" gorm:"null"`
    City              string  `json:"city" gorm:"null"`
    OrderItems        []OrderItem `json:"order_items" gorm:"foreignKey:OrderId"` /* <--HERE */
    CreatedAt         time.Time   `json:"-"`
    UpdatedAt         time.Time   `json:"-"`
}

type OrderItem struct {
    Model
    OrderId      uint    `json:"order_id"`
    ProductId    uint    `json:"product_id"`
    Size         string  `json:"size"`
    Color        string  `json:"color"`
    Price        float64 `json:"price"`
    Quantity     uint    `json:"quantity"`
    AdminRevenue float64 `json:"admin_revenue"`
    ClientPoint  float64 `json:"client_point"`
}

Here is my code to get all Order. But the resulting [ ]OrderItems is always 'null'. I use Gorm, MySql, Golang (GoFiber framework).

func GetAllOrder(c *fiber.Ctx) error {
    var orders []models.Order
    database.DB.Preload("OrderItems").Find(&orders)

    return c.JSON(orders)
}

Result

[
  {
    "id": 1,
    "transaction_id": "",
    "transaction_method": "MOMO",
    "create_payparams": "https://test-payment.momo.vn/v2/gateway/pay?t=TU9NT1JNRk8yMDIyMDYxOXw1ZDEzMTIxMWQwMDAxZGY=",
    "status_payparams": true,
    "user_id": 0,
    "order_code": "uHFTOsl",
    "amount": 880000,
    "receiver_name": "Jane Doe",
    "phone": "+84 xxxxxxxxxx",
    "email": "customer@demo.com",
    "detail_address": "Detail Address Example",
    "ward": "Ward Example",
    "district": "District Example",
    "city": "City Example",
    "order_items": null /* <--HERE */
  },
  {
    ...
  },
  {
    ...
  }
]

go-fiber go-gorm go foreign-keys mysql
1 Answers

use this code.

in this code can get all orders and order items.

note: in tables set deleted_at column as null

package main

import (
    "fmt"
    "gorm.io/driver/mysql"
    "gorm.io/gorm"
)

type Database struct {
    Gorm *gorm.DB
}

func NewDatabase(url string) (*Database, error) {
    cfg := &gorm.Config{}

    db, err := gorm.Open(mysql.Open(url), cfg)
    if err != nil {
        return nil, err
    }

    instance := &Database{
        Gorm: db,
    }

    err = instance.setup()
    if err != nil {
        return nil, err
    }

    return instance, nil
}
func (rDb *Database) setup() error {
    return rDb.Gorm.AutoMigrate(
        &Order{},
        &OrderItem{},
    )
}

type Order struct {
    gorm.Model
    TransactionId     string      `json:"transaction_id" gorm:"null"`
    TransactionMethod string      `json:"transaction_method"`
    CreatePayParams   string      `json:"create_payparams" gorm:"null"`
    StatusPayParams   bool        `json:"status_payparams" gorm:"default:false"`
    UserId            uint        `json:"user_id"`
    OrderCode         string      `json:"order_code"`
    Amount            float64     `json:"amount"`
    Complete          bool        `json:"-" gorm:"default:false"`
    ReceiverName      string      `json:"receiver_name"`
    Phone             string      `json:"phone"`
    Email             string      `json:"email"`
    DetailAddress     string      `json:"detail_address" gorm:"null"`
    Ward              string      `json:"ward" gorm:"null"`
    District          string      `json:"district" gorm:"null"`
    City              string      `json:"city" gorm:"null"`
    OrderItems        []OrderItem `json:"order_items" gorm:"foreignKey:OrderId"` /* <--HERE */
}

type OrderItem struct {
    gorm.Model
    OrderId      uint    `json:"order_id"`
    ProductId    uint    `json:"product_id"`
    Size         string  `json:"size"`
    Color        string  `json:"color"`
    Price        float64 `json:"price"`
    Quantity     uint    `json:"quantity"`
    AdminRevenue float64 `json:"admin_revenue"`
    ClientPoint  float64 `json:"client_point"`
}

func main() {
    database, _ := NewDatabase("root:root@tcp(127.0.0.1:3306)/OrderManager?parseTime=true")
    var orders []Order
    database.Gorm.Preload("OrderItems").Find(&orders)
    for _, order := range orders {
        fmt.Println(order.ID)
        for _, orderItem := range order.OrderItems {
            fmt.Println(orderItem.OrderId)
        }
    }
}

user_197451370
Related