Go语言学习之路（十一）gorm查询

引言

已经有了gorm的准备工作以及单表查询了，实际上我们在业务中更多的是使用一些连表、分组、分类等查询，今天就来学习一下gorm更高级的查询。

开始查询

实体

// 用户
type User struct{
	ID    uint
	username string
}
// 订单
type Order struct{
    // id
	ID     uint
	// 金额
	Amount float32
	// 用户id
	UserId uint
	// 商品名称
	SpuName string
	
}

高级查询

1、where条件中子查询

go代码

var order Order
db.Where("amount > (?)", db.Table("order").Select("AVG(amount)")).Find(&orders)
// SELECT * FROM "orders" WHERE amount > (SELECT AVG(amount) FROM "orders");

var result float32
subQuery := db.Select("AVG(amount)").Where("name LIKE ?", "%spu_name%").Table("order")
db.Select("AVG(amount) as avg_amount").Group("spu_name").Having("AVG(amount) > (?)", subQuery).Find(&results)

执行后sql

-- 第一个查询
SELECT * FROM order WHERE amount > (SELECT AVG(amount) FROM order);
-- 第二个查询
SELECT AVG(amount) as avg_amount FROM order GROUP BY spu_name HAVING AVG(amount) > (SELECT AVG(amount) FROM order WHERE name LIKE '%spu_name%')

2、from子查询

go代码

db.Table("(?) as o", db.Model(&Order{}).Select("user_id", "amount")).Where("o.amount = ?", 18).Find(&Order{})

subQuery1 := db.Model(&User{}).Select("ID")
subQuery2 := db.Model(&Order{}).Select("ID")
db.Table("(?) as u, (?) as o", subQuery1, subQuery2).Find(&User{})

执行后sql

-- 第一个
SELECT * FROM (SELECT `user_id`,`amount` FROM order) as o WHERE o.amount = 18
-- 第二个
SELECT * FROM (SELECT `ID` FROM user) as u, (SELECT `ID` FROM order) as o

3、or查询

go代码

db.Where(
    db.Where("ID = ?", 1).Where(db.Where("user_id = ?", 10).Or("spu_name = ?", "test")),
).Or(
    db.Where("amount > ?", 18).Where("spu_name = ?", "mall"),
).Find(&Order{}).Statement

执行后sql

-- sql
SELECT * FROM order WHERE (id = 1 AND (user_id = 10 OR spu_name = 'test')) OR (amount > 18 AND spu_name = 'mall')

4、in查询

go代码

db.Where("user_id IN ?", [2]uint{1,2}).Find(&order)

执行后sql

SELECT * FROM order WHERE user_id IN (1,2);

5、表关联查询

这里表关联查询以及统计的话，就直接使用原生的手写sql就行了，使用db.Raw(查询语句)进行查询