Gorm的模型对应关系

Bubble857
133 阅读11分钟

以一个订单库为例

entity

package entity

import (
    "gorm.io/gorm"
)

// User 结构体，对应users表
type User struct {
    gorm.Model
    Username    string  `gorm:"not null"`
    Password    string  `gorm:"not null"`
    Email       string  `gorm:"unique"`
    FullName    string  `gorm:""`
    Address     string  `gorm:""`
    PhoneNumber string  `gorm:""`
    Orders      []Order `gorm:"foreignKey:UserID"`
}

// Order 结构体，对应orders表
type Order struct {
    gorm.Model
    UserID          uint64  `gorm:"not null"`
    OrderNumber     string  `gorm:"not null"`
    OrderDate       string  `gorm:"not null"`
    TotalAmount     float64 `gorm:"not null"`
    OrderStatus     string  `gorm:"not null"`
    PaymentMethod   string  `gorm:""`
    ShippingAddress string  `gorm:""`
}

// Product 结构体，对应products表
type Product struct {
    gorm.Model
    Name          string  `gorm:"not null"`
    Description   string  `gorm:""`
    Price         float64 `gorm:"not null"`
    StockQuantity int     `gorm:"not null"`
}

sql

-- 创建数据库（如果不存在）
CREATE DATABASE IF NOT EXISTS gorm_order;

-- 使用创建好的数据库
USE gorm_order;

-- 创建用户表
CREATE TABLE IF NOT EXISTS users (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  username VARCHAR(255) NOT NULL,
  password VARCHAR(255) NOT NULL,
  email VARCHAR(255) UNIQUE,
  full_name VARCHAR(255),
  address VARCHAR(255),
  phone_number VARCHAR(255),
  created_at TIMESTAMP NOT NULL,
  updated_at TIMESTAMP NOT NULL,
  deleted_at TIMESTAMP
);

-- 插入用户数据示例
INSERT INTO users (username, password, email, full_name, address, phone_number, created_at, updated_at, deleted_at)
VALUES
    ('user1', 'password1', 'user1@example.com', '张三', '北京市朝阳区', '13812345678', '2024-11-25 18:00:00', '2024-11-25 18:00:00', NULL),
    ('user2', 'password2', 'user2@example.com', '李四', '上海市浦东新区', '13987654321', '2024-11-25 18:10:00', '2024-11-25 18:10:00', NULL),
    ('user3', 'password3', 'user3@example.com', '王五', '广州市天河区', '13654321876', '2024-11-25 18:20:00', '2024-11-25 18:20:00', NULL);

-- 创建订单表
CREATE TABLE IF NOT EXISTS orders (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  user_id BIGINT UNSIGNED NOT NULL,
  order_number VARCHAR(255) NOT NULL,
  order_date TIMESTAMP NOT NULL,
  total_amount DECIMAL(10, 2) NOT NULL,
  order_status ENUM('pending', 'processing', 'completed', 'cancelled') NOT NULL,
  payment_method VARCHAR(255),
  shipping_address VARCHAR(255),
  created_at TIMESTAMP NOT NULL,
  updated_at TIMESTAMP NOT NULL,
  deleted_at TIMESTAMP,
  FOREIGN KEY (user_id) REFERENCES users(id)
);

-- 插入订单数据示例
INSERT INTO orders (user_id, order_number, order_date, total_amount, order_status, payment_method, shipping_address, created_at, updated_at, deleted_at)
VALUES
    (1, 'ORDER001', '2024-11-25 19:00:00', 100.50, 'pending', 'credit_card', '北京市朝阳区XX小区XX栋', '2024-11-25 19:00:00', '2024-11-25 19:00:00', NULL),
    (2, 'ORDER002', '2024-11-25 19:10:00', 200.00, 'processing', 'paypal', '上海市浦东新区XX大厦XX室', '2024-11-25 19:10:00', '2024-11-25 19:10:00', NULL),
    (1, 'ORDER003', '2024-11-25 19:20:00', 50.25, 'completed', 'bank_transfer', '北京市朝阳区XX街道XX号', '2024-11-25 19:20:00', '2024-11-25 19:20:00', NULL);

-- 创建商品表
CREATE TABLE IF NOT EXISTS products (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(255) NOT NULL,
  description VARCHAR(255),
  price DECIMAL(10, 2) NOT NULL,
  stock_quantity INT NOT NULL,
  created_at TIMESTAMP NOT NULL,
  updated_at TIMESTAMP NOT NULL,
  deleted_at TIMESTAMP
);

-- 插入商品数据示例
INSERT INTO products (name, description, price, stock_quantity, created_at, updated_at, deleted_at)
VALUES
    ('商品1', '这是一款很棒的商品', 50.00, 100, '2024-11-25 19:30:00', '2024-11-25 19:30:00', NULL),
    ('商品2', '具有特色的商品', 80.00, 50, '2024-11-25 19:40:00', '2024-11-25 19:40:00', NULL),
    ('商品3', '热门商品', 120.00, 30, '2024-11-25 19:50:00', '2024-11-25 19:50:00', NULL);

-- 创建订单商品关联表（多对多关系）
CREATE TABLE IF NOT EXISTS order_products (
  order_id BIGINT UNSIGNED NOT NULL,
  product_id BIGINT UNSIGNED NOT NULL,
  quantity INT NOT NULL,
  PRIMARY KEY (order_id, product_id),
  FOREIGN KEY (order_id) REFERENCES orders(id),
  FOREIGN KEY (product_id) REFERENCES products(id)
);

-- 插入订单商品关联数据示例
INSERT INTO order_products (order_id, product_id, quantity)
VALUES
    (1, 1, 2),
    (1, 2, 1),
    (2, 2, 3),
    (3, 3, 1);
CREATE TABLE user_order_infos (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  user_id BIGINT UNSIGNED NOT NULL,
  order_id BIGINT UNSIGNED NOT NULL,
  created_at TIMESTAMP NOT NULL,
  updated_at TIMESTAMP NOT NULL,
  deleted_at TIMESTAMP,
  FOREIGN KEY (user_id) REFERENCES users(id),
  FOREIGN KEY (order_id) REFERENCES orders(id)
);
-- 插入user_order_infos表数据示例
INSERT INTO user_order_infos (user_id, order_id, created_at, updated_at, deleted_at)
VALUES
    -- 用户user1（id为1）的订单ORDER001（id为1）关联记录
    (1, 1, '2024-11-25 20:00:00', '2024-11-25 20:00:00', NULL),
    -- 用户user1（id为1）的订单ORDER003（id为3）关联记录
    (1, 3, '2024-11-25 20:10:00', '2024-11-25 20:10:00', NULL),
    -- 用户user2（id为2）的订单ORDER002（id为2）关联记录
    (2, 2, '2024-11-25 20:20:00', '2024-11-25 20:20:00', NULL);

Has Many

has many 与另一个模型建立了一对多的连接。 不同于 has one，拥有者可以有零或多个关联模型。

// User 结构体，对应users表
type User struct {
	gorm.Model
	Username    string `gorm:"not null"`
	Password    string `gorm:"not null"`
	Email       string `gorm:"unique"`
	FullName    string `gorm:""`
	Address     string `gorm:""`
	PhoneNumber string `gorm:""`
	Orders         []Order          `gorm:"foreignKey:UserID"`
}

// Order 结构体，对应orders表
type Order struct {
	gorm.Model
	UserID          uint64  `gorm:"not null"`
	OrderNumber     string  `gorm:"not null"`
	OrderDate       string  `gorm:"not null"`
	TotalAmount     float64 `gorm:"not null"`
	OrderStatus     string  `gorm:"not null"`
	PaymentMethod   string  `gorm:""`
	ShippingAddress string  `gorm:""`
}

以用户的订单为例，一个用户会存在多个订单，而这也就是一种一对多的关系。

数据表内容如下


mysql> select * from users;
+----+----------+-----------+-------------------+-----------+-----------------------+--------------+---------------------+---------------------+------------+
| id | username | password  | email             | full_name | address               | phone_number | created_at          | updated_at          | deleted_at |
+----+----------+-----------+-------------------+-----------+-----------------------+--------------+---------------------+---------------------+------------+
|  1 | user1    | password1 | user1@example.com | 张三      | 北京市朝阳区          | 13812345678  | 2024-11-25 18:00:00 | 2024-11-25 18:00:00 | NULL       |
|  2 | user2    | password2 | user2@example.com | 李四      | 上海市浦东新区        | 13987654321  | 2024-11-25 18:10:00 | 2024-11-25 18:10:00 | NULL       |
|  3 | user3    | password3 | user3@example.com | 王五      | 广州市天河区          | 13654321876  | 2024-11-25 18:20:00 | 2024-11-25 18:20:00 | NULL       |
+----+----------+-----------+-------------------+-----------+-----------------------+--------------+---------------------+---------------------+------------+
3 rows in set (0.00 sec)

mysql> select * from orders;
+----+---------+--------------+---------------------+--------------+--------------+----------------+------------------------------------+---------------------+---------------------+------------+
| id | user_id | order_number | order_date          | total_amount | order_status | payment_method | shipping_address                   | created_at          | updated_at          | deleted_at |
+----+---------+--------------+---------------------+--------------+--------------+----------------+------------------------------------+---------------------+---------------------+------------+
|  1 |       1 | ORDER001     | 2024-11-25 19:00:00 |       100.50 | pending      | credit_card    | 北京市朝阳区XX小区XX栋             | 2024-11-25 19:00:00 | 2024-11-25 19:00:00 | NULL       |
|  2 |       2 | ORDER002     | 2024-11-25 19:10:00 |       200.00 | processing   | paypal         | 上海市浦东新区XX大厦XX室           | 2024-11-25 19:10:00 | 2024-11-25 19:10:00 | NULL       |
|  3 |       1 | ORDER003     | 2024-11-25 19:20:00 |        50.25 | completed    | bank_transfer  | 北京市朝阳区XX街道XX号             | 2024-11-25 19:20:00 | 2024-11-25 19:20:00 | NULL       |
+----+---------+--------------+---------------------+--------------+--------------+----------------+------------------------------------+---------------------+---------------------+------------+
3 rows in set (0.00 sec)

以id为1的用户为例，他在orders表里存在两条记录，而这也表示has many，也就是业务上的模型存在一对多的关系。

接下来我们看看代码怎么写

package main

import (
    "GormDemo/entity"
    "fmt"
    "gorm.io/driver/mysql"
    "gorm.io/gorm"
    "gorm.io/gorm/logger"
)

func main() {
    mysqlConnect := "root:000000@tcp(192.168.23.233:3306)/gorm_order?charset=utf8mb4&parseTime=True&loc=Local"
    //GRANT ALL PRIVILEGES ON *.* TO 'root'@'192.168.23.1' IDENTIFIED BY '000000' WITH GRANT OPTION;

    mysqlDialector := mysql.New(mysql.Config{
        DSN:               mysqlConnect,
        DefaultStringSize: 256,
    })
    db, err := gorm.Open(mysqlDialector, &gorm.Config{
        PrepareStmt: true,
        Logger:      logger.Default.LogMode(logger.Info),
    })
    if err != nil {
        fmt.Println(err.Error())
    }
    var user1 *entity.User
    _ = QueryOrderInfo(db, user1)
}

func QueryOrderInfo(tx *gorm.DB, users *entity.User) error {
    var userlist []entity.User
    find := tx.Model(&entity.User{}).Preload("Orders").Find(&userlist)
    fmt.Println("Row:", find.Row())
    fmt.Println("users:", len(userlist))
    PrintAllUser(userlist)
    return nil
}

func PrintAllUser(userlist []entity.User) {
    n := len(userlist)
    fmt.Println("===============================")
    for i := 0; i < n; i++ {
        fmt.Println("UserId:", userlist[i].ID)
        fmt.Println("UserName:", userlist[i].Username)
        orderLen := len(userlist[i].Orders)
        if orderLen < 1 {
            fmt.Println("该用户无订单！")
        }
        for j := 0; j < orderLen; j++ {
            fmt.Println(">OrderId:", userlist[i].Orders[j].ID)
            fmt.Println(">TotalAmount:", userlist[i].Orders[j].TotalAmount)
            fmt.Println(">OrderStatus:", userlist[i].Orders[j].OrderStatus)
        }
        fmt.Println("===============================")
    }
}

运行结果

可以看到，这里userId为1的存在2个订单也都打印出来了。

2024/11/27 15:31:12 D:/development/Go Project/GormDemo/main.go:179
[2.605ms] [rows:3] SELECT * FROM `user_order_infos` WHERE `user_order_infos`.`user_id` IN (1,2,3)

2024/11/27 15:31:12 D:/development/Go Project/GormDemo/main.go:179
[1.505ms] [rows:3] SELECT * FROM `orders` WHERE `orders`.`id` IN (1,3,2) AND `orders`.`deleted_at` IS NULL

2024/11/27 15:31:12 D:/development/Go Project/GormDemo/main.go:179
[23.610ms] [rows:3] SELECT * FROM `users` WHERE `users`.`deleted_at` IS NULL

2024/11/27 15:31:12 D:/development/Go Project/GormDemo/main.go:180
[0.444ms] [rows:-] SELECT * FROM `users` WHERE `users`.`deleted_at` IS NULL
Row: &{<nil> 0xc00020a900}
users: 3
===============================
UserId: 1
UserName: user1
>OrderId: 1
>TotalAmount: 100.5
>OrderStatus: pending
>OrderId: 3
>TotalAmount: 50.25
>OrderStatus: completed
===============================
UserId: 2
UserName: user2
>OrderId: 2
>TotalAmount: 200
>OrderStatus: processing
===============================
UserId: 3
UserName: user3
该用户无订单！
===============================

Many2Many

Many to Many 会在两个 model 中添加一张连接表。

在刚刚我们有用户表也有订单表，现在我们使用以订单产品为中间表。

sql命令所示

-- 创建订单商品关联表（多对多关系）
CREATE TABLE IF NOT EXISTS order_products (
  order_id BIGINT UNSIGNED NOT NULL,
  product_id BIGINT UNSIGNED NOT NULL,
  quantity INT NOT NULL,
  PRIMARY KEY (order_id, product_id),
  FOREIGN KEY (order_id) REFERENCES orders(id),
  FOREIGN KEY (product_id) REFERENCES products(id)
);

-- 插入订单商品关联数据示例
INSERT INTO order_products (order_id, product_id, quantity)
VALUES
    (1, 1, 2),
    (1, 2, 1),
    (2, 2, 3),
    (3, 3, 1);

mysql查询结果

mysql> select * from order_products;
+----------+------------+----------+
| order_id | product_id | quantity |
+----------+------------+----------+
|        1 |          1 |        2 |
|        1 |          2 |        1 |
|        2 |          2 |        3 |
|        3 |          3 |        1 |
+----------+------------+----------+
4 rows in set (0.00 sec)

接下来看看代码怎么写

entity

这里我们order_products作为连接表，其中存在order_id和product_id因此，我们需要在Order结构体中定义Products []*Product `gorm:"many2many:order_products"` ，然后在Product结构体中定义Orders []*Order `gorm:"many2many:order_products"`

// Order 结构体，对应orders表
type Order struct {
    gorm.Model
    UserID          uint64  `gorm:"not null"`
    OrderNumber     string  `gorm:"not null"`
    OrderDate       string  `gorm:"not null"`
    TotalAmount     float64 `gorm:"not null"`
    OrderStatus     string  `gorm:"not null"`
    PaymentMethod   string  `gorm:""`
    ShippingAddress string  `gorm:""`
    Users           []*User `gorm:"many2many:user_order_infos"`
    Products        []*Product `gorm:"many2many:order_products"` // 添加这一行用于表示与产品的多对多关系
}

// Product 结构体，对应products表
type Product struct {
    gorm.Model
    Name          string  `gorm:"not null"`
    Description   string  `gorm:""`
    Price         float64 `gorm:"not null"`
    StockQuantity int     `gorm:"not null"`
    Orders        []*Order `gorm:"many2many:order_products"` // 添加这一行用于表示与订单的多对多关系
}

我们看看主程序代码编写

package main

import (
    "GormDemo/entity"
    "fmt"
    "gorm.io/driver/mysql"
    "gorm.io/gorm"
    "gorm.io/gorm/logger"
)

func main() {
    mysqlConnect := "root:000000@tcp(192.168.23.233:3306)/gorm_order?charset=utf8mb4&parseTime=True&loc=Local"
    //GRANT ALL PRIVILEGES ON *.* TO 'root'@'192.168.23.1' IDENTIFIED BY '000000' WITH GRANT OPTION;

    mysqlDialector := mysql.New(mysql.Config{
        DSN:               mysqlConnect,
        DefaultStringSize: 256,
    })
    db, err := gorm.Open(mysqlDialector, &gorm.Config{
        PrepareStmt: true,
        Logger:      logger.Default.LogMode(logger.Info),
    })
    if err != nil {
        fmt.Println(err.Error())
    }
    _ = GetAllOrder(db)
}

func GetAllOrder(tx *gorm.DB) []entity.Order {
	var orderlist []entity.Order
	err := tx.Model(&entity.Order{}).Preload("Products").Find(&orderlist).Error
	if err != nil {
		fmt.Println("Failed to Get orderList")
	}
	fmt.Println("len(orderlist):", len(orderlist))
	fmt.Println("===============================")
	for i := 0; i < len(orderlist); i++ {
		fmt.Println("OrderId:", orderlist[i].ID)
		fmt.Println("TotalAmount:", orderlist[i].TotalAmount)
		fmt.Println("UserID:", orderlist[i].UserID)
		if len(orderlist[i].Products) < 1 {
			fmt.Println("该订单不存在对应商品！")
		} else {
			for j := 0; j < len(orderlist[i].Products); j++ {
				fmt.Println(">Name:", orderlist[i].Products[j].Name)
				fmt.Println(">StockQuantity:", orderlist[i].Products[j].StockQuantity)
				fmt.Println(">Description:", orderlist[i].Products[j].Description)
			}
		}
		fmt.Println("===============================")
	}
	return orderlist
}
运行结果
2024/11/27 16:26:23 D:/development/Go Project/GormDemo/main.go:218
[2.011ms] [rows:4] SELECT * FROM `order_products` WHERE `order_products`.`order_id` IN (1,2,3)

2024/11/27 16:26:23 D:/development/Go Project/GormDemo/main.go:218
[1.493ms] [rows:3] SELECT * FROM `products` WHERE `products`.`id` IN (1,2,3) AND `products`.`deleted_at` IS NULL

2024/11/27 16:26:23 D:/development/Go Project/GormDemo/main.go:218
[19.477ms] [rows:3] SELECT * FROM `orders` WHERE `orders`.`deleted_at` IS NULL
len(orderlist): 3
===============================
OrderId: 1
TotalAmount: 100.5
UserID: 1
>Name: 商品1
>StockQuantity: 100
>Description: 这是一款很棒的商品
>Name: 商品2
>StockQuantity: 50
>Description: 具有特色的商品
===============================
OrderId: 2
TotalAmount: 200
UserID: 2
>Name: 商品2
>StockQuantity: 50
>Description: 具有特色的商品
===============================
OrderId: 3
TotalAmount: 50.25
UserID: 1
>Name: 商品3
>StockQuantity: 30
>Description: 热门商品
===============================

可以看到这里查询出来了结果。

我们分析sql语句发现，当我们定义many2many:order_products时，执行了

2024/11/27 16:26:23 D:/development/Go Project/GormDemo/main.go:218
[2.011ms] [rows:4] SELECT * FROM `order_products` WHERE `order_products`.`order_id` IN (1,2,3)

而这个也对应着mysql中的


mysql> select * from order_products;
+----------+------------+----------+
| order_id | product_id | quantity |
+----------+------------+----------+
|        1 |          1 |        2 |
|        1 |          2 |        1 |
|        2 |          2 |        3 |
|        3 |          3 |        1 |
+----------+------------+----------+
4 rows in set (0.00 sec)

也就是说，当我们使用Preload("Products")时，会去找到Order这个Model里的字段Products成员变量，然后读取到gorm:"many2many:order_products"，所以他会先去查询order_products这个表，然后把数据加载到Order结构体里的Products，然后在查询数据时我们看到：

2024/11/27 16:26:23 D:/development/Go Project/GormDemo/main.go:218
[1.493ms] [rows:3] SELECT * FROM `products` WHERE `products`.`id` IN (1,2,3) AND `products`.`deleted_at` IS NULL

也就说明了，会去查询这个products表，然后根据查询的结果左连接回表给Order，然后当执行Find()时，就会查询把刚刚预加载查询出来的数据填充到这个orderlist里。

2024/11/27 16:26:23 D:/development/Go Project/GormDemo/main.go:218
[19.477ms] [rows:3] SELECT * FROM `orders` WHERE `orders`.`deleted_at` IS NULL
avatar
文章
阅读
粉丝