go-gorm使用子查询查询数据

gorm 使用子查询查询数据

我想实现如下SQL的查询

1
2
>SELECT id,hostname FROM `physical_nodes`  WHERE `physical_nodes`.`deleted_at` IS NULL AND ((model = '' and id not in ((SELECT hid FROM `hardware_infos`  WHERE (cpu_core = '' or mem_size = '' or 
disk = ''))))) ;

通过gorm 实现

1
2
3
4
5
6
7
8
9
10
11
12
ds := db.GetDB()
var pns []model.PhysicalNode
//查询没有model而且hardware info cpu ram disk 为null的
sql := fmt.Sprintf("model = '' and id not in (?)")
subSql := fmt.Sprintf("cpu_core = '' or mem_size = '' or disk = ''")
subQuery := ds.Select("hid").Table("hardware_infos").Where(subSql).SubQuery()

err := ds.Select("id,hostname").Where(sql,subQuery).Find(&pns).Error
if err != nil {
	logger.Error("db query error : ",err)
	return nil
}