关于gorm多表联合查询(left join)

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

示例结果:

关于gorm多表联合查询(left join)

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

上一篇:gorm中使用Updates更新非空的字段


下一篇:golang的简易教程(不定期更新中...)