关于gorm多表联合查询（left join）

2023-10-30 19:19:46

Golang很流行，但是有些方面资料很少而且不详实，譬如：gorm的联合查询，当然，也不推荐复杂语句使用orm模型。

现将自己总结的写法和遇到的坑记录如下： Golang要求使用“驼峰命名法”，比如systemId，因为我以前用的是Python，使用Django的orm序列化后返回的参数和数据库表字段一致，基于这个不适合Go的思路，我将表字段也建成了systemId，和struct映射参数相同。（其实表字段应该命名为system_id）

一、下面建两张表，用于联合查询（以left join示例）

MySQL > desc go_system_info;
+——————+——————-+———+——-+——————-+————————+
| Field | Type | Null | Key | Default | Extra |
+——————+——————-+———+——-+——————-+————————+
| id | int(11) | NO | PRI | NULL | auto_increment |
| systemId | varchar(30) | NO | MUL | NULL | |
| systemName | varchar(50) | NO | | defaultNull | |
+——————+——————-+———+——-+——————-+————————+
3 rows in set (0.01 sec)

MySQL > desc go_service_info;
+——————-+——————-+———+——-+——————-+————————+
| Field | Type | Null | Key | Default | Extra |
+——————-+——————-+———+——-+——————-+————————+
| id | int(11) | NO | PRI | NULL | auto_increment |
| systemId | varchar(30) | NO | MUL | NULL | |
| serviceId | varchar(50) | NO | MUL | defaultNull | |
| serviceName | varchar(50) | NO | | defaultNull | |
+——————-+——————-+———+——-+——————-+————————+
4 rows in set (0.00 sec)

MySQL >

二、表建好后，我们来定义表结构体：

type GoSystemInfo struct {
    ID           int    `gorm:"primary_key"`
    SystemId     string `gorm:"column:systemId;type:varchar(30);not null;index:SystemId"`
    SystemName   string `gorm:"column:systemName;type:varchar(50);not null;default:'defaultNull'"`
}
type GoServiceInfo struct {
    ID           int    `gorm:"primary_key"`
    SystemId     string `gorm:"column:systemId;type:varchar(30);not null;index:SystemId"`
    ServiceId    string `gorm:"column:serviceId;type:varchar(50);not null;default:'defaultNull';index:ServiceId"`
    ServiceName  string `gorm:"column:serviceName;type:varchar(50);not null;default:'defaultNull'"`
}

小知识：ORM（Object Relation Mapping），对象关系映射，实际上就是对数据库的操作进行封装，对上层开发人员屏蔽数据操作的细节，开发人员看到的就是一个个对象，大大简化了开发工作，提高了生产效率，也可以避免sql注入等问题。

由于gorm是使用的orm映射，所以需要定义要操作的表的model，在go中需要定义一个struct， struct的名字就是对应数据库中的表名，注意gorm查找struct名对应数据库中的表名的时候会默认把你的struct中的大写字母转换为小写并加上“s”，所以可以加上 db.SingularTable(true) 让gorm转义struct名字的时候不用加上“s”。

golang中，首字母大小写来表示public或者private，因此结构体中字段首字母必须大写。

定义model，即struct时，我们可以只定义我们需要从数据库中取回的特定字段： gorm在转义表名的时候会把struct的大写字母(首字母除外) 替换成“_”，所以下面的”GoSystemInfo”会转义成数据库中对应的“go_system_info”的表名，对应的字段名的查找会先按照tag里面的名称去里面查找，如果没有定义标签则按照struct定义的字段查找，查找的时候struct字段中的大写会被转义成“_”，如：“SystemId”会去查找表中的system_id字段。

在本例，我们在struct使用如gorm:”column:systemId”，column映射mysql表字段名称。

三、联合查询

单表查询用上面的原表结构体接收数据就可以了，联合查询涉及两张表中的全部/部分数据，我们定义新的结构体接收取回的特定字段：

type result struct {
    SystemId    string `json:"systemId"`
    SystemName  string `json:"systemName"`
    ServiceId   string `json:"serviceId"`
    ServiceName string `json:"serviceName"`
}

我们从go_service_info取serviceId、serviceName，从go_system_info取对应的systemId、systemName：

db.Table("go_service_info").Select("go_service_info.serviceId as service_id, go_service_info.serviceName as service_name, go_system_info.systemId as system_id, go_system_info.systemName as system_name").Joins("left join go_system_info on go_service_info.systemId = go_system_info.systemId").Scan(&results)

where条件：

db.Table("go_service_info").Select("go_service_info.serviceId as service_id, go_service_info.serviceName as service_name, go_system_info.systemId as system_id, go_system_info.systemName as system_name").Joins("left join go_system_info on go_service_info.systemId = go_system_info.systemId where go_service_info.serviceId <> ? and go_system_info.systemId = ?", "xxx", "xxx").Scan(&results)

注意：这里需要使用别名as system_id，映射返回值结构体，并且因为查找的时候struct字段中的大写会被转义成“_”，所以别名也要将大写转为“_”。

若使用原生语句：

db.Raw("SELECT a.serviceId as service_id,a.serviceName as service_name, b.systemId as system_id, b.systemName as system_name FROM go_service_info a LEFT JOIN go_system_info b ON a.systemId = b.systemId").Scan(&results)

where条件：

db.Raw("SELECT a.serviceId as service_id,a.serviceName as service_name, b.systemId as system_id, b.systemName as system_name FROM go_service_info a LEFT JOIN go_system_info b ON a.systemId = b.systemId where a.serviceId <> ? and b.systemId = ?", "xxx", "xxx").Scan(&results)

结果相同。

避坑建议：表字段命名为如system_id，默认映射到结构体字段SystemId。当然建表原则上也是用小写和下划线，不过历史表难免会有大写命名的情况，所以新表还是遵照相关规范吧。

源码：

package main
import (
    "fmt"
    "log"
    "encoding/json"
    "github.com/jinzhu/gorm"
    _ "github.com/jinzhu/gorm/dialects/mysql"
)
type GoSystemInfo struct {
    ID           int    `gorm:"primary_key"`
    SystemId     string `gorm:"column:systemId;type:varchar(30);not null;index:SystemId"`
    SystemName   string `gorm:"column:systemName;type:varchar(50);not null;default:'defaultNull'"`
}
type GoServiceInfo struct {
    ID           int    `gorm:"primary_key"`
    SystemId     string `gorm:"column:systemId;type:varchar(30);not null;index:SystemId"`
    ServiceId    string `gorm:"column:serviceId;type:varchar(50);not null;default:'defaultNull';index:ServiceId"`
    ServiceName  string `gorm:"column:serviceName;type:varchar(50);not null;default:'defaultNull'"`
}
type result struct {
    SystemId    string `json:"systemId"`
    SystemName  string `json:"systemName"`
    ServiceId   string `json:"serviceId"`
    ServiceName string `json:"serviceName"`
}
//定义数据库连接
type ConnInfo struct { 
    MyUser   string 
    Password string 
    Host     string 
    Port     int 
    Db       string
}
func dbConn(MyUser, Password, Host, Db string, Port int) *gorm.DB {
    connArgs := fmt.Sprintf("%s:%s@(%s:%d)/%s?charset=utf8&parseTime=True&loc=Local", MyUser,Password, Host, Port, Db )
    db, err := gorm.Open("mysql", connArgs)
    if err != nil {  
        log.Fatal(err) 
    } 
    db.SingularTable(true)
    return db
}
func mapToJson(result interface{}) string {
    // map转 json str
    jsonBytes, _ := json.Marshal(result)
    jsonStr := string(jsonBytes)
    return jsonStr
}
func main() {
    var results []result
    cn := ConnInfo{  
      "xxx",  
      "xxx",  
      "127.0.0.1",  
      3306,  
      "xxx", 
    }  
    db := dbConn(cn.MyUser,cn.Password,cn.Host,cn.Db,cn.Port)
    defer db.Close()
    /*
    // 创建表
    db.AutoMigrate(&GoSystemInfo{})
    product := GoSystemInfo{SystemId:"sysid", SystemName:"sysname"}
    fmt.Println(db.NewRecord(product))
    db.AutoMigrate(&GoServiceInfo{})
    products := GoServiceInfo{SystemId:"sysid", ServiceId:"serid", ServiceName:"sername"}
    fmt.Println(db.NewRecord(products))
    */
    // 联合查询(left join)
    db.Table("go_service_info").Select("go_service_info.serviceId as service_id, go_service_info.serviceName as service_name, go_system_info.systemId as system_id, go_system_info.systemName as system_name").Joins("left join go_system_info on go_service_info.systemId = go_system_info.systemId").Scan(&results)
    fmt.Println(mapToJson(results))
    // where
    db.Table("go_service_info").Select("go_service_info.serviceId as service_id, go_service_info.serviceName as service_name, go_system_info.systemId as system_id, go_system_info.systemName as system_name").Joins("left join go_system_info on go_service_info.systemId = go_system_info.systemId where go_service_info.serviceId <> ? and go_system_info.systemId = ?", "xxx", "xxx").Scan(&results)
    fmt.Println(mapToJson(results))
    // 原生sql
    db.Raw("SELECT a.serviceId as service_id,a.serviceName as service_name, b.systemId as system_id, b.systemName as system_name FROM go_service_info a LEFT JOIN go_system_info b ON a.systemId = b.systemId").Scan(&results)
    fmt.Println(mapToJson(results))
    // where
    db.Raw("SELECT a.serviceId as service_id,a.serviceName as service_name, b.systemId as system_id, b.systemName as system_name FROM go_service_info a LEFT JOIN go_system_info b ON a.systemId = b.systemId where a.serviceId <> ? and b.systemId = ?", "xxx", "xxx").Scan(&results)
    fmt.Println(mapToJson(results))
}

示例结果：

参考：https://www.jb51.net/article/151051.htm

码农公寓

相关文章