关于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.