已经有了gorm的准备工作以及单表查询了,实际上我们在业务中更多的是使用一些连表、分组、分类等查询,今天就来学习一下gorm更高级的查询。
// 用户
type User struct{
ID uint
username string
}
// 订单
type Order struct{
// id
ID uint
// 金额
Amount float32
// 用户id
UserId uint
// 商品名称
SpuName string
}
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%')
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
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')
go代码
db.Where("user_id IN ?", [2]uint{1,2}).Find(&order)
执行后sql
SELECT * FROM order WHERE user_id IN (1,2);
这里表关联查询以及统计的话,就直接使用原生的手写sql就行了,使用db.Raw(查询语句)进行查询