GORM模型查询

model

type CreditCard struct {
	gorm.Model
	Number string
	UserID uint
}
type CreateUsers struct {
	gorm.Model
	Name         string
	Email        *string
	Age          uint8
	Birthday     *time.Time
	MemberNumber sql.NullString
	ActivatedAt  sql.NullTime
	CreatedAt    time.Time
	UpdatedAt    time.Time
	CreditCard   CreditCard `gorm:"foreignKey:ID"`
}

表数据：

$E:\Lenrn_notes\21go\2.0笔记整理\image\image-20211202082746321.png$

一、检索单个对象

GORM 提供了 First、Take、Last 方法，以便从数据库中检索单个对象。当查询数据库时它添加了 LIMIT 1 条件，且没有找到记录时，它会返回 ErrRecordNotFound 错误

1.1 First

package main

import (
	"database/sql"
	"errors"
	"fmt"
	"gorm.io/driver/mysql"
	"gorm.io/gorm"
	"time"
)

type CreditCard struct {
	gorm.Model
	Number string
	UserID uint
}
type CreateUsers struct {
	gorm.Model
	Name         string
	Email        *string
	Age          uint8
	Birthday     *time.Time
	MemberNumber sql.NullString
	ActivatedAt  sql.NullTime
	CreatedAt    time.Time
	UpdatedAt    time.Time
	CreditCard   CreditCard `gorm:"foreignKey:ID"`
}

func (u *CreateUsers) BeforeCreate(tx *gorm.DB) (err error) {
	fmt.Println("创建之前触发钩子")
	if u.Name == "RandySun" {
		return errors.New("invalid role")
	}
	return
}
func main() {
	dsn := "root:@tcp(127.0.0.1:3306)/gorm?charset=utf8mb4&parseTime=True&loc=Local"
	db, err := gorm.Open(mysql.Open(dsn), &gorm.Config{})
	if err != nil {
		panic(err)
	}
	// 迁移表创建对应关系
	db.AutoMigrate(&CreateUsers{}, &CreditCard{})

	// 查询
	var user  CreateUsers
	// 获取第一条记录（主键升序）
    // SELECT * FROM `create_users` WHERE `create_users`.`deleted_at` IS NULL ORDER BY `create_users`.`id` LIMIT 1
	db.Debug().First(&user)
	fmt.Printf(user.Name)	
}

$E:\Lenrn_notes\21go\2.0笔记整理\image\image-20211202083012514.png$

1.2 Take

package main

import (
	"database/sql"
	"errors"
	"fmt"
	"gorm.io/driver/mysql"
	"gorm.io/gorm"
	"time"
)

func main() {
	dsn := "root:@tcp(127.0.0.1:3306)/gorm?charset=utf8mb4&parseTime=True&loc=Local"
	db, err := gorm.Open(mysql.Open(dsn), &gorm.Config{})
	if err != nil {
		panic(err)
	}
	// 迁移表创建对应关系
	db.AutoMigrate(&CreateUsers{}, &CreditCard{})

	// 查询
	var user  CreateUsers
	// 获取一条记录，没有指定排序字段
    //  SELECT * FROM `create_users` WHERE `create_users`.`deleted_at` IS NULL LIMIT 1
	db.Debug().Take(&user)
	fmt.Printf(user.Name)	
}

1.3 Last

package main

import (
	"database/sql"
	"errors"
	"fmt"
	"gorm.io/driver/mysql"
	"gorm.io/gorm"
	"time"
)

func main() {
	dsn := "root:@tcp(127.0.0.1:3306)/gorm?charset=utf8mb4&parseTime=True&loc=Local"
	db, err := gorm.Open(mysql.Open(dsn), &gorm.Config{})
	if err != nil {
		panic(err)
	}
	// 迁移表创建对应关系
	db.AutoMigrate(&CreateUsers{}, &CreditCard{})

	// 查询
	var user  CreateUsers
	// 获取最后一条记录（主键降序）
    // SELECT * FROM `create_users` WHERE `create_users`.`deleted_at` IS NULL ORDER BY `create_users`.`id` DESC LIMIT 1
	db.Debug().Last(&user)
	fmt.Println(user.ID, user.Name)
}

$E:\Lenrn_notes\21go\2.0笔记整理\image\image-20211202083316521.png$

1.4 返回结果

package main

import (
	"database/sql"
	"errors"
	"fmt"
	"gorm.io/driver/mysql"
	"gorm.io/gorm"
	"time"
)

func main() {
	dsn := "root:@tcp(127.0.0.1:3306)/gorm?charset=utf8mb4&parseTime=True&loc=Local"
	db, err := gorm.Open(mysql.Open(dsn), &gorm.Config{})
	if err != nil {
		panic(err)
	}
	// 迁移表创建对应关系
	db.AutoMigrate(&CreateUsers{}, &CreditCard{})

	// 查询
	var user  CreateUsers
	result := db.First(&user)
	// 返回找到的记录数
	fmt.Println(result.RowsAffected)
	fmt.Println(result.Error) // returns error or nil

	// 检查 ErrRecordNotFound 错误
	isErrors := errors.Is(result.Error, gorm.ErrRecordNotFound)
	fmt.Println(isErrors)
}

$E:\Lenrn_notes\21go\2.0笔记整理\image\image-20211202083823224.png$

如果你想避免ErrRecordNotFound错误，你可以使用Find，比如db.Limit(1).Find(&user)，Find方法可以接受struct和slice的数据。

First 和 Last 会根据主键排序，分别查询第一条和最后一条记录。只有在目标 struct 是指针或者通过 db.Model() 指定 model 时，该方法才有效。此外，如果相关 model 没有定义主键，那么将按 model 的第一个字段进行排序。例如:

var user User
var users []User  

// 有效，因为目标 struct 是指针
db.First(&user)
// SELECT * FROM `users` ORDER BY `users`.`id` LIMIT 1


// 有效，因为通过 `db.Model()` 指定了 mod
result := map[string]interface{}{}
db.Debug().Model(&CreateUsers{}).First(&result)
fmt.Printf("%#v\n", result)
fmt.Printf("%#v\n", result["name"])

$E:\Lenrn_notes\21go\2.0笔记整理\image\image-20211202084902277.png$

// 无效
result := map[string]interface{}{}
db.Table("create_users").First(&result)
fmt.Printf("%#v\n", result)
fmt.Printf("%#v\n", result["name"])

$E:\Lenrn_notes\21go\2.0笔记整理\image\image-20211202090215626.png$

// 配合 Take 有效
result := map[string]interface{}{}
db.Table("create_users").Take(&result) // 表名
fmt.Printf("%#v\n", result)
fmt.Printf("%#v\n", result["name"])

$E:\Lenrn_notes\21go\2.0笔记整理\image\image-20211202090115482.png$

// 未指定主键，会根据第一个字段排序(即：`Code`)
type Language struct {
  Code string
  Name string
}
db.First(&Language{})
// SELECT * FROM `languages` ORDER BY `languages`.`code` LIMIT 1

二、用主键检索

2.1 First

package main

import (
	"database/sql"
	"errors"
	"fmt"
	"gorm.io/driver/mysql"
	"gorm.io/gorm"
	"time"
)

func main() {
	dsn := "root:@tcp(127.0.0.1:3306)/gorm?charset=utf8mb4&parseTime=True&loc=Local"
	db, err := gorm.Open(mysql.Open(dsn), &gorm.Config{})
	if err != nil {
		panic(err)
	}
	// 迁移表创建对应关系
	db.AutoMigrate(&CreateUsers{}, &CreditCard{})

	// 根据主键查询第一条记录
	var user CreateUsers
	db.Debug().First(&user, 5)
	fmt.Println(user.ID)
}

$E:\Lenrn_notes\21go\2.0笔记整理\image\image-20211202090602443.png$

2.2 First 字符串

package main

import (
	"database/sql"
	"errors"
	"fmt"
	"gorm.io/driver/mysql"
	"gorm.io/gorm"
	"time"
)

func main() {
	dsn := "root:@tcp(127.0.0.1:3306)/gorm?charset=utf8mb4&parseTime=True&loc=Local"
	db, err := gorm.Open(mysql.Open(dsn), &gorm.Config{})
	if err != nil {
		panic(err)
	}
	// 迁移表创建对应关系
	db.AutoMigrate(&CreateUsers{}, &CreditCard{})

		// 根据主键查询第一条记录
	var user CreateUsers
	db.Debug().First(&user, "10")
	fmt.Println(user.ID)
}

$E:\Lenrn_notes\21go\2.0笔记整理\image\image-20211202090937960.png$

2.3 Find

package main

import (
	"database/sql"
	"errors"
	"fmt"
	"gorm.io/driver/mysql"
	"gorm.io/gorm"
	"time"
)

func main() {
	dsn := "root:@tcp(127.0.0.1:3306)/gorm?charset=utf8mb4&parseTime=True&loc=Local"
	db, err := gorm.Open(mysql.Open(dsn), &gorm.Config{})
	if err != nil {
		panic(err)
	}
	// 迁移表创建对应关系
	db.AutoMigrate(&CreateUsers{}, &CreditCard{})
// 根据主键查询第多条记录
	var users []CreateUsers
	db.Debug().Find(&users, []int{1,2,3})
	
	for _, u :=range users{
		fmt.Println(u.ID)
	}
}

$E:\Lenrn_notes\21go\2.0笔记整理\image\image-20211202091304509.png$

2.4 检索全部对象

package main

import (
	"database/sql"
	"errors"
	"fmt"
	"gorm.io/driver/mysql"
	"gorm.io/gorm"
	"time"
)

func main() {
	dsn := "root:@tcp(127.0.0.1:3306)/gorm?charset=utf8mb4&parseTime=True&loc=Local"
	db, err := gorm.Open(mysql.Open(dsn), &gorm.Config{})
	if err != nil {
		panic(err)
	}
	// 迁移表创建对应关系
	db.AutoMigrate(&CreateUsers{}, &CreditCard{})
    
	// 获取全部记录
	var users []CreateUsers
	result := db.Debug().Find(&users)

	fmt.Println(result.RowsAffected) // 返回找到的记录数，相当于 `len(users)`
	fmt.Println(result.Error)        // returns error
}

$E:\Lenrn_notes\21go\2.0笔记整理\image\image-20211202220356558.png$

$E:\Lenrn_notes\21go\2.0笔记整理\image\image-20211202220435027.png$

三、Where String 条件

3.1 条件查询First

package main

import (
	"database/sql"
	"errors"
	"fmt"
	"gorm.io/driver/mysql"
	"gorm.io/gorm"
	"time"
)

func main() {
	dsn := "root:@tcp(127.0.0.1:3306)/gorm?charset=utf8mb4&parseTime=True&loc=Local"
	db, err := gorm.Open(mysql.Open(dsn), &gorm.Config{})
	if err != nil {
		panic(err)
	}
	// 迁移表创建对应关系
	db.AutoMigrate(&CreateUsers{}, &CreditCard{})
    
	var user CreateUsers
	db.Debug().Where("name = ?", "RandySun").First(&user)
	fmt.Println(user.ID)
}

$E:\Lenrn_notes\21go\2.0笔记整理\image\image-20211202222307102.png$

$E:\Lenrn_notes\21go\2.0笔记整理\image\image-20211202222447952.png$

3.2 条件查询First

package main

import (
	"database/sql"
	"errors"
	"fmt"
	"gorm.io/driver/mysql"
	"gorm.io/gorm"
	"time"
)

func main() {
	dsn := "root:@tcp(127.0.0.1:3306)/gorm?charset=utf8mb4&parseTime=True&loc=Local"
	db, err := gorm.Open(mysql.Open(dsn), &gorm.Config{})
	if err != nil {
		panic(err)
	}
	// 迁移表创建对应关系
	db.AutoMigrate(&CreateUsers{}, &CreditCard{})
    
	var user CreateUsers
	db.Debug().Where("name = ?", "RandySun").First(&user)
	fmt.Println(user.ID)
}

3.3 全部查询

package main

import (
	"database/sql"
	"errors"
	"fmt"
	"gorm.io/driver/mysql"
	"gorm.io/gorm"
	"time"
)

func main() {
	dsn := "root:@tcp(127.0.0.1:3306)/gorm?charset=utf8mb4&parseTime=True&loc=Local"
	db, err := gorm.Open(mysql.Open(dsn), &gorm.Config{})
	if err != nil {
		panic(err)
	}
	// 迁移表创建对应关系
	db.AutoMigrate(&CreateUsers{}, &CreditCard{})
    
   // 获取全部匹配的记录
    // SELECT * FROM `create_users` WHERE name <> 'RandySun' AND `create_users`.`deleted_at` IS NULL

	var users []CreateUsers
	db.Debug().Where("name <> ?", "RandySun").Find(&users)
	for _, u := range users{
		fmt.Println(u.ID, u.Name)
	}
}

$E:\Lenrn_notes\21go\2.0笔记整理\image\image-20211203083054241.png$

3.4 IN查询

package main

import (
	"database/sql"
	"errors"
	"fmt"
	"gorm.io/driver/mysql"
	"gorm.io/gorm"
	"time"
)

func main() {
	dsn := "root:@tcp(127.0.0.1:3306)/gorm?charset=utf8mb4&parseTime=True&loc=Local"
	db, err := gorm.Open(mysql.Open(dsn), &gorm.Config{})
	if err != nil {
		panic(err)
	}
	// 迁移表创建对应关系
	db.AutoMigrate(&CreateUsers{}, &CreditCard{})
    
   	// IN
	var users []CreateUsers
	// SELECT * FROM `create_users` WHERE name IN ('RandySun','RandySun03') AND `create_users`.`deleted_at` IS NULL
	db.Debug().Where("name IN ?", []string{"RandySun", "RandySun03"}).Find(&users)
	for _, u := range users{
		fmt.Println(u.ID, u.Name)
	}
}

3.5 LIKE

package main

import (
	"database/sql"
	"errors"
	"fmt"
	"gorm.io/driver/mysql"
	"gorm.io/gorm"
	"time"
)

func main() {
	dsn := "root:@tcp(127.0.0.1:3306)/gorm?charset=utf8mb4&parseTime=True&loc=Local"
	db, err := gorm.Open(mysql.Open(dsn), &gorm.Config{})
	if err != nil {
		panic(err)
	}
	// 迁移表创建对应关系
	db.AutoMigrate(&CreateUsers{}, &CreditCard{})
    
  // LIKE
	var users []CreateUsers
	//  SELECT * FROM `create_users` WHERE name LIKE '%sun%' AND `create_users`.`deleted_at` IS NULL
	db.Debug().Where("name LIKE ?", "%sun%").Find(&users)
	for _, u := range users{
		fmt.Println(u.ID, u.Name)
	}
}

$E:\Lenrn_notes\21go\2.0笔记整理\image\image-20211203083610326.png$

3.6 AND

package main

import (
	"database/sql"
	"errors"
	"fmt"
	"gorm.io/driver/mysql"
	"gorm.io/gorm"
	"time"
)

func main() {
	dsn := "root:@tcp(127.0.0.1:3306)/gorm?charset=utf8mb4&parseTime=True&loc=Local"
	db, err := gorm.Open(mysql.Open(dsn), &gorm.Config{})
	if err != nil {
		panic(err)
	}
	// 迁移表创建对应关系
	db.AutoMigrate(&CreateUsers{}, &CreditCard{})
    
    // AND
	var users []CreateUsers
	
    //  SELECT * FROM `create_users` WHERE (name = 'RandySun' AND age >= '18') AND `create_users`.`deleted_at` IS NULL
	db.Debug().Where("name = ? AND age >= ?", "RandySun", "18").Find(&users)
	// SELECT * FROM users WHERE name = 'jinzhu' AND age >= 22;
	for _, u := range users{
		fmt.Println(u.ID, u.Name)
	}
}

$E:\Lenrn_notes\21go\2.0笔记整理\image\image-20211203084041160.png$

3.7 Time

package main

import (
	"database/sql"
	"errors"
	"fmt"
	"gorm.io/driver/mysql"
	"gorm.io/gorm"
	"time"
)

func main() {
	dsn := "root:@tcp(127.0.0.1:3306)/gorm?charset=utf8mb4&parseTime=True&loc=Local"
	db, err := gorm.Open(mysql.Open(dsn), &gorm.Config{})
	if err != nil {
		panic(err)
	}
	// 迁移表创建对应关系
	db.AutoMigrate(&CreateUsers{}, &CreditCard{})
    
   // Time
	var users []CreateUsers
	lasterWeek := time.Now()
	// SELECT * FROM `create_users` WHERE updated_at < '2021-12-03 08:43:00.694' AND `create_users`.`deleted_at` IS NULL

	db.Debug().Where("updated_at < ?", lasterWeek).Find(&users)
	for _, u := range users{
		fmt.Println(u.ID, u.Name)
	}
}

$E:\Lenrn_notes\21go\2.0笔记整理\image\image-20211203084400595.png$

3.8 BETWEEN

package main

import (
	"database/sql"
	"errors"
	"fmt"
	"gorm.io/driver/mysql"
	"gorm.io/gorm"
	"time"
)

func main() {
	dsn := "root:@tcp(127.0.0.1:3306)/gorm?charset=utf8mb4&parseTime=True&loc=Local"
	db, err := gorm.Open(mysql.Open(dsn), &gorm.Config{})
	if err != nil {
		panic(err)
	}
	// 迁移表创建对应关系
	db.AutoMigrate(&CreateUsers{}, &CreditCard{})
    
   	// BETWEEN
	var users []CreateUsers

	// SELECT * FROM `create_users` WHERE (age BETWEEN 1 AND 18) AND `create_users`.`deleted_at` IS NULL
	db.Debug().Where("age BETWEEN ? AND ?", 1, 18).Find(&users)
	for _, u := range users{
		fmt.Println(u.ID, u.Name)
	}

}

$E:\Lenrn_notes\21go\2.0笔记整理\image\image-20211203084914543.png$

四、Struct & Map查询

4.1 Struct 查询

package main

import (
	"database/sql"
	"errors"
	"fmt"
	"gorm.io/driver/mysql"
	"gorm.io/gorm"
	"time"
)

func main() {
	dsn := "root:@tcp(127.0.0.1:3306)/gorm?charset=utf8mb4&parseTime=True&loc=Local"
	db, err := gorm.Open(mysql.Open(dsn), &gorm.Config{})
	if err != nil {
		panic(err)
	}
	// 迁移表创建对应关系
	db.AutoMigrate(&CreateUsers{}, &CreditCard{})
    
   	// Struct
	var user CreateUsers
    // SELECT * FROM `create_users` WHERE `create_users`.`name` = 'RandySun' AND `create_users`.`age` = 18 AND `create_users`.`deleted_at` IS NULL ORDER BY `create_users`.`id` LIMIT 1
	db.Debug().Where(&CreateUsers{Name: "RandySun", Age: 18}).First(&user)
	fmt.Println(user.ID, user.Name)

}

$E:\Lenrn_notes\21go\2.0笔记整理\image\image-20211203085313242.png$

4.2 Map 查询

package main

import (
	"database/sql"
	"errors"
	"fmt"
	"gorm.io/driver/mysql"
	"gorm.io/gorm"
	"time"
)

func main() {
	dsn := "root:@tcp(127.0.0.1:3306)/gorm?charset=utf8mb4&parseTime=True&loc=Local"
	db, err := gorm.Open(mysql.Open(dsn), &gorm.Config{})
	if err != nil {
		panic(err)
	}
	// 迁移表创建对应关系
	db.AutoMigrate(&CreateUsers{}, &CreditCard{})
    
   //// Map
	var users []CreateUsers
    // SELECT * FROM `create_users` WHERE `age` = 18 AND `name` = 'RandySun' AND `create_users`.`deleted_at` IS NULL
	db.Debug().Where(map[string]interface{}{"name": "RandySun", "age": 18}).Find(&users)

	for _, u := range users{
		fmt.Println(u.ID, u.Name)
	}

}

$E:\Lenrn_notes\21go\2.0笔记整理\image\image-20211203085545241.png$

4.3 切片条件

package main

import (
	"database/sql"
	"errors"
	"fmt"
	"gorm.io/driver/mysql"
	"gorm.io/gorm"
	"time"
)

func main() {
	dsn := "root:@tcp(127.0.0.1:3306)/gorm?charset=utf8mb4&parseTime=True&loc=Local"
	db, err := gorm.Open(mysql.Open(dsn), &gorm.Config{})
	if err != nil {
		panic(err)
	}
	// 迁移表创建对应关系
	db.AutoMigrate(&CreateUsers{}, &CreditCard{})
    
  	// 主键切片条件
	var users []CreateUsers
	// SELECT * FROM `create_users` WHERE `create_users`.`id` IN (18,8,1) AND `create_users`.`deleted_at` IS NULL
	db.Debug().Where([]int64{18, 8, 1}).Find(&users)
	for _, u := range users{
		fmt.Println(u.ID, u.Name)
	}


}

$E:\Lenrn_notes\21go\2.0笔记整理\image\image-20211203090111613.png$

注意当使用结构作为条件查询时，GORM 只会查询非零值字段。这意味着如果您的字段值为 0、''、false 或其他零值，该字段不会被用于构建查询条件，例如：

db.Where(&User{Name: "jinzhu", Age: 0}).Find(&users)
// SELECT * FROM users WHERE name = "jinzhu";

你可以使用指针或实现 Scanner/Valuer 接口来避免这个问题.

// 使用指针
type User struct {
  gorm.Model
  Name string
  Age  *int
}

// 使用 Scanner/Valuer
type User struct {
  gorm.Model
  Name string
  Age  sql.NullInt64  // sql.NullInt64 实现了 Scanner/Valuer 接口
}

如果想要包含零值查询条件，你可以使用 map，其会包含所有 key-value 的查询条件，例如：

db.Where(map[string]interface{}{"Name": "jinzhu", "Age": 0}).Find(&users)
// SELECT * FROM users WHERE name = "jinzhu" AND age = 0;

五、指定结构体查询字段

当使用 struct 进行查询时，你可以通过向 Where() 传入 struct 来指定查询条件的字段、值、表名，例如：

var users []CreateUsers
//  SELECT * FROM `create_users` WHERE `create_users`.`name` = 'RandySun' AND `create_users`.`age` = 0 AND `create_users`.`deleted_at` IS NULL
db.Debug().Where(&CreateUsers{Name: "RandySun"}, "name", "Age").Find(&users)
for _, u :=range users{
    fmt.Println(u.ID, u.Name)
}

$E:\Lenrn_notes\21go\2.0笔记整理\image\image-20211203213953587.png$

var users []CreateUsers
db.Debug().Where(&CreateUsers{Name: "RandySun"}, "Age").Find(&users)
for _, u :=range users{
    fmt.Println(u.ID, u.Name)
}

$E:\Lenrn_notes\21go\2.0笔记整理\image\image-20211203213927518.png$

六、内联条件

作用与Where查询类似，当内联条件与多个立即执行方法一起使用时, 内联条件不会传递给后面的立即执行方法。

var user CreateUsers
// 根据主键获取记录 (只适用于整形主键)
// SELECT * FROM `create_users` WHERE `create_users`.`id` = 18 AND `create_users`.`deleted_at` IS NULL ORDER BY `create_users`.`id` LIMIT 1
db.Debug().First(&user, 18)
fmt.Println(user.ID, user.Name)

$E:\Lenrn_notes\21go\2.0笔记整理\image\image-20211203214758233.png$

// 根据主键获取记录, 如果它是一个非整形主键
db.First(&user, "id = ?", "string_primary_key")

var user CreateUsers
db.Debug().First(&user, "name = ?", "RandySun01")
fmt.Println(user.ID, user.Name)

var user1 CreateUsers
db.Debug().First(&user1, "id = ?", "2")
fmt.Println(user1.ID, user1.Name)

$E:\Lenrn_notes\21go\2.0笔记整理\image\image-20211203215928386.png$

// Plain SQL
var users []CreateUsers
// find可群查和单查
// SELECT * FROM `create_users` WHERE name = 'RandySun' AND `create_users`.`deleted_at` IS NULL

db.Debug().Find(&users, "name = ?", "RandySun")
for _, u :=range users{
    fmt.Println(u.ID, u.Name)
}

var user CreateUsers
// find单查
// SELECT * FROM `create_users` WHERE name = 'RandySun' AND `create_users`.`deleted_at` IS NULL
db.Debug().Find(&user, "name = ?", "RandySun")
fmt.Println(user.ID, user.Name)

$E:\Lenrn_notes\21go\2.0笔记整理\image\image-20211203221227001.png$


// SELECT * FROM `create_users` WHERE (name <> 'RandySun' AND age >= 18) AND `create_users`.`deleted_at` IS NULL
db.Find(&users, "name <> ? AND age > ?", "jinzhu", 20)

$E:\Lenrn_notes\21go\2.0笔记整理\image\image-20211203221433555.png$


// Struct
//  SELECT * FROM `create_users` WHERE `create_users`.`age` = 18 AND `create_users`.`deleted_at` IS NULL
db.Find(&users, User{Age: 20})

$E:\Lenrn_notes\21go\2.0笔记整理\image\image-20211203221629413.png$

// Map
var users []CreateUsers
// SELECT * FROM `create_users` WHERE `age` = 20 AND `create_users`.`deleted_at` IS NULL
db.Debug().Find(&users, map[string]interface{}{"age": 20})

for _, u :=range users{
    fmt.Println(u.ID, u.Name)
}

$E:\Lenrn_notes\21go\2.0笔记整理\image\image-20211203221816662.png$

七、Not 条件

作用与 Where 类似的情形如下：

// NOT
var user CreateUsers
//  SELECT * FROM `create_users` WHERE `name` <> 'RandySun' AND `create_users`.`deleted_at` IS NULL ORDER BY `create_users`.`id` LIMIT 1
db.Debug().Not("name", "RandySun").First(&user)
fmt.Println(user.ID, user.Name)

$E:\Lenrn_notes\21go\2.0笔记整理\image\image-20211203222145346.png$

// Not In
var users []CreateUsers
//  SELECT * FROM `create_users` WHERE `name` NOT IN ('RandySun','RandySun02') AND `create_users`.`deleted_at` IS NULL
db.Debug().Not("name", []string{"RandySun", "RandySun02"}).Find(&users)
for _, u :=range users{
    fmt.Println(u.ID, u.Name)
}

$E:\Lenrn_notes\21go\2.0笔记整理\image\image-20211207080240683.png$

// Not In slice of primary keys
// SELECT * FROM `create_users` WHERE `create_users`.`id` NOT IN (1,2,3) AND `create_users`.`deleted_at` IS NULL
var users []CreateUsers
db.Debug().Not([]int64{1,2,3}).Find(&users)
for _, u :=range users{
    fmt.Println(u.ID, u.Name)
}

$E:\Lenrn_notes\21go\2.0笔记整理\image\image-20211207080613164.png$

var users []CreateUsers
// SELECT * FROM `create_users` WHERE `create_users`.`deleted_at` IS NULL
db.Not([]int64{}).Find(&users)
for _, u :=range users{
    fmt.Println(u.ID, u.Name)
}
//// SELECT * FROM users;

$E:\Lenrn_notes\21go\2.0笔记整理\image\image-20211207082908264.png$

// Plain SQL
var users []CreateUsers
// SELECT * FROM `create_users` WHERE NOT name = 'RandySun' AND `create_users`.`deleted_at` IS NULL
db.Debug().Not("name = ?", "RandySun").Find(&users)

for _, u :=range users{
    fmt.Println(u.ID, u.Name)
}

$E:\Lenrn_notes\21go\2.0笔记整理\image\image-20211207083216053.png$

// Struct
var users []CreateUsers
// SELECT * FROM `create_users` WHERE `create_users`.`name` <> 'RandySun' AND `create_users`.`deleted_at` IS NULL
db.Debug().Not(CreateUsers{Name: "RandySun"}).Find(&users)
for _, u :=range users{
    fmt.Println(u.ID, u.Name)
}

$E:\Lenrn_notes\21go\2.0笔记整理\image\image-20211207083412855.png$

八、Or条件

var users []CreateUsers

// SELECT * FROM `create_users` WHERE (name = 'RandySun' OR age = 20) AND `create_users`.`deleted_at` IS NULL
db.Debug().Where("name = ?", "RandySun").Or("age = ?", 20).Find(&users)

for _, u :=range users{
    fmt.Println(u.ID, u.Name)
}

$E:\Lenrn_notes\21go\2.0笔记整理\image\image-20211207083916734.png$

$E:\Lenrn_notes\21go\2.0笔记整理\image\image-20211207083743914.png$

// Struct
var users []CreateUsers
// SELECT * FROM `create_users` WHERE ((name = 'RandySun') OR `create_users`.`name` = 'RandySun02') AND `create_users`.`deleted_at` IS NULL
db.Debug().Where("name = 'RandySun'").Or(CreateUsers{Name: "RandySun02"}).Find(&users)

for _, u :=range users{
    fmt.Println(u.ID, u.Name)
}

$E:\Lenrn_notes\21go\2.0笔记整理\image\image-20211207084224585.png$

// Map
var users []CreateUsers
// SELECT * FROM `create_users` WHERE ((name = 'RandySun') OR `name` = 'RandySun02') AND `create_users`.`deleted_at` IS NULL
db.Debug().Where("name = 'RandySun'").Or(map[string]interface{}{"name": "RandySun02"}).Find(&users)
for _, u :=range users{
    fmt.Println(u.ID, u.Name)
}

$E:\Lenrn_notes\21go\2.0笔记整理\image\image-20211207084429640.png$

九、Order

指定从数据库检索记录时的排序方式

// 多个 排序
var users []CreateUsers
// SELECT * FROM `create_users` WHERE `create_users`.`deleted_at` IS NULL ORDER BY age desc,name
db.Debug().Order("age desc").Order("name").Find(&users)

for _, u :=range users{
    fmt.Println(u.ID, u.Name)
}

$E:\Lenrn_notes\21go\2.0笔记整理\image\image-20211207085912823.png$

// 多个 order
var users []CreateUsers
// SELECT * FROM `create_users` WHERE `create_users`.`deleted_at` IS NULL ORDER BY age desc,name
db.Debug().Order("age desc").Order("name").Find(&users)
for _, u :=range users{
    fmt.Println(u.ID, u.Name)
}

$E:\Lenrn_notes\21go\2.0笔记整理\image\image-20211207090140915.png$

// 自定义排序ORDER BY FIELD()
var users []CreateUsers
db.Debug().Clauses(clause.OrderBy{
    Expression: clause.Expr{SQL: "FIELD(id,?)", Vars: []interface{}{[]int{1, 2, 3}}, WithoutParentheses: true},
}).Find(&users)

for _, u := range users {
    fmt.Println(u.ID, u.Name)
}

$E:\Lenrn_notes\21go\2.0笔记整理\image\image-20211207213617316.png$

十、Limit & Offset

Limit 指定获取记录的最大数量 Offset 指定在开始返回记录之前要跳过的记录数量

var users []CreateUsers
//  SELECT * FROM `create_users` WHERE `create_users`.`deleted_at` IS NULL LIMIT 3
db.Debug().Limit(3).Find(&users)

for _, u :=range users{
    fmt.Println(u.ID, u.Name)
}

$E:\Lenrn_notes\21go\2.0笔记整理\image\image-20211207213952899.png$

// 通过 -1 消除 Limit 条件
var users1 []CreateUsers
var users2 []CreateUsers

//  SELECT * FROM `create_users` WHERE `create_users`.`deleted_at` IS NULL LIMIT 10
//  SELECT * FROM `create_users` WHERE `create_users`.`deleted_at` IS NULL
db.Debug().Limit(10).Find(&users1).Limit(-1).Find(&users2)

//  SELECT * FROM `create_users` WHERE `create_users`.`deleted_at` IS NULL LIMIT 10
for _, u :=range users1{
    fmt.Println(u.ID, u.Name)
}
fmt.Println("*********************************************************")
//  SELECT * FROM `create_users` WHERE `create_users`.`deleted_at` IS NULL
for _, u :=range users2{
    fmt.Println(u.ID, u.Name)
}

$E:\Lenrn_notes\21go\2.0笔记整理\image\image-20211207214423502.png$

db.Offset(3).Find(&users)
// SELECT * FROM users OFFSET 3;

var users []CreateUsers
// SELECT * FROM `create_users` WHERE `create_users`.`deleted_at` IS NULL LIMIT 10 OFFSET 5
db.Debug().Limit(10).Offset(5).Find(&users)

for _, u :=range users{
    fmt.Println(u.ID, u.Name)
}

$E:\Lenrn_notes\21go\2.0笔记整理\image\image-20211207215438474.png$

// 通过 -1 消除 Offset 条件
db.Offset(10).Find(&users1).Offset(-1).Find(&users2)
// SELECT * FROM users OFFSET 10; (users1)
// SELECT * FROM users; (users2)

查看 Pagination 学习如何写一个分页器

十一、Group By & Having

group by

type Result struct {
    Date  time.Time
    Total int
    name string
}

var result Result
// SELECT name, sum(age) as total FROM `create_users` WHERE name LIKE 'Randy%' AND `create_users`.`deleted_at` IS NULL GROUP BY `name` ORDER BY `create_users`.`id` LIMIT 1
db.Debug().Model(&CreateUsers{}).Select("name, sum(age) as total").Where("name LIKE ?", "Randy%").Group("name").First(&result)

fmt.Println(result.Date, result.Total, result.name)

$E:\Lenrn_notes\21go\2.0笔记整理\image\image-20211207221005173.png$

$E:\Lenrn_notes\21go\2.0笔记整理\image\image-20211207221029658.png$

Group By & Having


type Result struct {
    Date  time.Time
    Total int
    name string
}

var result []Result
//  SELECT name, sum(age) as total FROM `create_users` WHERE `create_users`.`deleted_at` IS NULL GROUP BY `name` HAVING name = 'RandySun'

db.Debug().Model(&CreateUsers{}).Select("name, sum(age) as total").Group("name").Having("name = ?", "RandySun").Find(&result)
for _, u :=range result{
    fmt.Println(u.name, u.Total, u.Date)
}

$E:\Lenrn_notes\21go\2.0笔记整理\image\image-20211207221605011.png$

$E:\Lenrn_notes\21go\2.0笔记整理\image\image-20211207221624416.png$

type Result struct {
Date  *time.Time
Total int
name string
}

// SELECT name , sum(age) as total FROM `create_users` GROUP BY `name`
rows, err := db.Debug().Table("create_users").Select("name , sum(age) as total").Group("name").Rows()
for rows.Next() {
var r Result
err := rows.Scan(&r.name, &r.Total)
if err != nil {
fmt.Printf("scan failed, err:%v\n", err)
return
}
fmt.Printf("name: %s, Total: %d\n", r.name, r.Total)
}

$E:\Lenrn_notes\21go\2.0笔记整理\image\image-20211207223802832.png$

$E:\Lenrn_notes\21go\2.0笔记整理\image\image-20211207223812994.png$

type Result struct {
    Date  *time.Time
    Total int
    name string
}
rows, err := db.Debug().Table("create_users").Select("name, sum(age) as total").Group("name").Having("sum(age) > ?", 10).Rows()

for rows.Next() {
    var r Result
    err := rows.Scan(&r.name, &r.Total)
    if err != nil {
        fmt.Printf("scan failed, err:%v\n", err)
        return
    }
    fmt.Printf("name: %s, Total: %d\n", r.name, r.Total)
}

$E:\Lenrn_notes\21go\2.0笔记整理\image\image-20211207224233341.png$

$E:\Lenrn_notes\21go\2.0笔记整理\image\image-20211207224252920.png$

type Result struct {
    names string
    Total int
    Date  *time.Time
}
var r []Result

// SELECT name, sum(age) as total FROM `create_users` GROUP BY `name` HAVING sum(age) > 10
db.Debug().Table("create_users").Select("name, sum(age) as total").Group("name").Having("sum(age) > ?", 10).Scan(&r)
for _, r := range r{
    fmt.Println(r.names, r.Total)
}

$E:\Lenrn_notes\21go\2.0笔记整理\image\image-20211207225140648.png$

$E:\Lenrn_notes\21go\2.0笔记整理\image\image-20211207225115151.png$

十二、Distinct

从模型中选择不相同的值

db.Distinct("name", "age").Order("name, age desc").Find(&results)

Distinct 也可以配合 Pluck, Count 使用

十三、Joins

指定 Joins 条件

type result struct {
  Name  string
  Email string
}

db.Model(&User{}).Select("users.name, emails.email").Joins("left join emails on emails.user_id = users.id").Scan(&result{})
// SELECT users.name, emails.email FROM `users` left join emails on emails.user_id = users.id

rows, err := db.Table("users").Select("users.name, emails.email").Joins("left join emails on emails.user_id = users.id").Rows()
for rows.Next() {
  ...
}

db.Table("users").Select("users.name, emails.email").Joins("left join emails on emails.user_id = users.id").Scan(&results)

// 带参数的多表连接
db.Joins("JOIN emails ON emails.user_id = users.id AND emails.email = ?", "jinzhu@example.org").Joins("JOIN credit_cards ON credit_cards.user_id = users.id").Where("credit_cards.number = ?", "411111111111").Find(&user)

十四、Joins 预加载

您可以使用 Joins 实现单条 SQL 预加载关联记录，例如：

db.Joins("Company").Find(&users)
// SELECT `users`.`id`,`users`.`name`,`users`.`age`,`Company`.`id` AS `Company__id`,`Company`.`name` AS `Company__name` FROM `users` LEFT JOIN `companies` AS `Company` ON `users`.`company_id` = `Company`.`id`;

Join with conditions

db.Joins("Company", DB.Where(&Company{Alive: true})).Find(&users)
// SELECT `users`.`id`,`users`.`name`,`users`.`age`,`Company`.`id` AS `Company__id`,`Company`.`name` AS `Company__name` FROM `users` LEFT JOIN `companies` AS `Company` ON `users`.`company_id` = `Company`.`id` AND `Company`.`alive` = true;

For more details, please refer to Preloading (Eager Loading).

十五、Scan

Scanning results into a struct works similarly to the way we use Find

type Result struct {
  Name string
  Age  int
}

var result Result
db.Table("users").Select("name", "age").Where("name = ?", "Antonio").Scan(&result)

// Raw SQL
db.Raw("SELECT name, age FROM users WHERE name = ?", "Antonio").Scan(&result)

十六、选择特定字段

Select 允许您指定从数据库中检索哪些字段，默认情况下，GORM 会检索所有字段。

db.Select("name", "age").Find(&users)
// SELECT name, age FROM users;

db.Select([]string{"name", "age"}).Find(&users)
// SELECT name, age FROM users;

db.Table("users").Select("COALESCE(age,?)", 42).Rows()
// SELECT COALESCE(age,'42') FROM users;

还可以看一看智能选择字段

十七、额外查询选项

// 为查询 SQL 添加额外的 SQL 操作
db.Set("gorm:query_option", "FOR UPDATE").First(&user, 10)
//// SELECT * FROM users WHERE id = 10 FOR UPDATE;

十八、FirstOrInitSelect

获取匹配的第一条记录，否则根据给定的条件初始化一个新的对象 (仅支持 struct 和 map 条件)

// 未找到
db.FirstOrInit(&user, User{Name: "non_existing"})
//// user -> User{Name: "non_existing"}

// 找到
db.Where(User{Name: "Jinzhu"}).FirstOrInit(&user)
//// user -> User{Id: 111, Name: "Jinzhu", Age: 20}
db.FirstOrInit(&user, map[string]interface{}{"name": "jinzhu"})
//// user -> User{Id: 111, Name: "Jinzhu", Age: 20}

十九、Attrs

如果记录未找到，将使用参数初始化 struct.

// 未找到
db.Where(User{Name: "non_existing"}).Attrs(User{Age: 20}).FirstOrInit(&user)
//// SELECT * FROM USERS WHERE name = 'non_existing';
//// user -> User{Name: "non_existing", Age: 20}

db.Where(User{Name: "non_existing"}).Attrs("age", 20).FirstOrInit(&user)
//// SELECT * FROM USERS WHERE name = 'non_existing';
//// user -> User{Name: "non_existing", Age: 20}

// 找到
db.Where(User{Name: "Jinzhu"}).Attrs(User{Age: 30}).FirstOrInit(&user)
//// SELECT * FROM USERS WHERE name = jinzhu';
//// user -> User{Id: 111, Name: "Jinzhu", Age: 20}

二十、Assign

不管记录是否找到，都将参数赋值给 struct.

// 未找到
db.Where(User{Name: "non_existing"}).Assign(User{Age: 20}).FirstOrInit(&user)
//// user -> User{Name: "non_existing", Age: 20}

// 找到
db.Where(User{Name: "Jinzhu"}).Assign(User{Age: 30}).FirstOrInit(&user)
//// SELECT * FROM USERS WHERE name = jinzhu';
//// user -> User{Id: 111, Name: "Jinzhu", Age: 30}

二十一、FirstOrCreate

获取匹配的第一条记录, 否则根据给定的条件创建一个新的记录 (仅支持 struct 和 map 条件)

// 未找到
db.FirstOrCreate(&user, User{Name: "non_existing"})
//// INSERT INTO "users" (name) VALUES ("non_existing");
//// user -> User{Id: 112, Name: "non_existing"}

// 找到
db.Where(User{Name: "Jinzhu"}).FirstOrCreate(&user)
//// user -> User{Id: 111, Name: "Jinzhu"}

二十二、Attrs

如果记录未找到，将使用参数创建 struct 和记录.

 // 未找到
db.Where(User{Name: "non_existing"}).Attrs(User{Age: 20}).FirstOrCreate(&user)
//// SELECT * FROM users WHERE name = 'non_existing';
//// INSERT INTO "users" (name, age) VALUES ("non_existing", 20);
//// user -> User{Id: 112, Name: "non_existing", Age: 20}

// 找到
db.Where(User{Name: "jinzhu"}).Attrs(User{Age: 30}).FirstOrCreate(&user)
//// SELECT * FROM users WHERE name = 'jinzhu';
//// user -> User{Id: 111, Name: "jinzhu", Age: 20}

二十三、Assign

不管记录是否找到，都将参数赋值给 struct 并保存至数据库.

// 未找到
db.Where(User{Name: "non_existing"}).Assign(User{Age: 20}).FirstOrCreate(&user)
//// SELECT * FROM users WHERE name = 'non_existing';
//// INSERT INTO "users" (name, age) VALUES ("non_existing", 20);
//// user -> User{Id: 112, Name: "non_existing", Age: 20}

// 找到
db.Where(User{Name: "jinzhu"}).Assign(User{Age: 30}).FirstOrCreate(&user)
//// SELECT * FROM users WHERE name = 'jinzhu';
//// UPDATE users SET age=30 WHERE id = 111;
//// user -> User{Id: 111, Name: "jinzhu", Age: 30}

高级查询

子查询

基于 *gorm.expr 的子查询

db.Where("amount > ?", db.Table("orders").Select("AVG(amount)").Where("state = ?", "paid").SubQuery()).Find(&orders)
// SELECT * FROM "orders"  WHERE "orders"."deleted_at" IS NULL AND (amount > (SELECT AVG(amount) FROM "orders"  WHERE (state = 'paid')));

选择字段

Select，指定你想从数据库中检索出的字段，默认会选择全部字段。

db.Select("name, age").Find(&users)
//// SELECT name, age FROM users;

db.Select([]string{"name", "age"}).Find(&users)
//// SELECT name, age FROM users;

db.Table("users").Select("COALESCE(age,?)", 42).Rows()
//// SELECT COALESCE(age,'42') FROM users;

排序

Order，指定从数据库中检索出记录的顺序。设置第二个参数 reorder 为 true ，可以覆盖前面定义的排序条件。

db.Order("age desc, name").Find(&users)
//// SELECT * FROM users ORDER BY age desc, name;

// 多字段排序
db.Order("age desc").Order("name").Find(&users)
//// SELECT * FROM users ORDER BY age desc, name;

// 覆盖排序
db.Order("age desc").Find(&users1).Order("age", true).Find(&users2)
//// SELECT * FROM users ORDER BY age desc; (users1)
//// SELECT * FROM users ORDER BY age; (users2)

数量

Limit，指定从数据库检索出的最大记录数。

db.Limit(3).Find(&users)
//// SELECT * FROM users LIMIT 3;

// -1 取消 Limit 条件
db.Limit(10).Find(&users1).Limit(-1).Find(&users2)
//// SELECT * FROM users LIMIT 10; (users1)
//// SELECT * FROM users; (users2)

偏移

Offset，指定开始返回记录前要跳过的记录数。

db.Offset(3).Find(&users)
//// SELECT * FROM users OFFSET 3;

// -1 取消 Offset 条件
db.Offset(10).Find(&users1).Offset(-1).Find(&users2)
//// SELECT * FROM users OFFSET 10; (users1)
//// SELECT * FROM users; (users2)

总数

Count，该 model 能获取的记录总数。

db.Where("name = ?", "jinzhu").Or("name = ?", "jinzhu 2").Find(&users).Count(&count)
//// SELECT * from USERS WHERE name = 'jinzhu' OR name = 'jinzhu 2'; (users)
//// SELECT count(*) FROM users WHERE name = 'jinzhu' OR name = 'jinzhu 2'; (count)

db.Model(&User{}).Where("name = ?", "jinzhu").Count(&count)
//// SELECT count(*) FROM users WHERE name = 'jinzhu'; (count)

db.Table("deleted_users").Count(&count)
//// SELECT count(*) FROM deleted_users;

db.Table("deleted_users").Select("count(distinct(name))").Count(&count)
//// SELECT count( distinct(name) ) FROM deleted_users; (count)

注意 Count 必须是链式查询的最后一个操作，因为它会覆盖前面的 SELECT，但如果里面使用了 count 时不会覆盖

Group & Having

rows, err := db.Table("orders").Select("date(created_at) as date, sum(amount) as total").Group("date(created_at)").Rows()
for rows.Next() {
  ...
}

// 使用Scan将多条结果扫描进事先准备好的结构体切片中
type Result struct {
	Date time.Time
	Total int
}
var rets []Result
db.Table("users").Select("date(created_at) as date, sum(age) as total").Group("date(created_at)").Scan(&rets)

rows, err := db.Table("orders").Select("date(created_at) as date, sum(amount) as total").Group("date(created_at)").Having("sum(amount) > ?", 100).Rows()
for rows.Next() {
  ...
}

type Result struct {
  Date  time.Time
  Total int64
}
db.Table("orders").Select("date(created_at) as date, sum(amount) as total").Group("date(created_at)").Having("sum(amount) > ?", 100).Scan(&results)

连接

Joins，指定连接条件

rows, err := db.Table("users").Select("users.name, emails.email").Joins("left join emails on emails.user_id = users.id").Rows()
for rows.Next() {
  ...
}

db.Table("users").Select("users.name, emails.email").Joins("left join emails on emails.user_id = users.id").Scan(&results)

// 多连接及参数
db.Joins("JOIN emails ON emails.user_id = users.id AND emails.email = ?", "jinzhu@example.org").Joins("JOIN credit_cards ON credit_cards.user_id = users.id").Where("credit_cards.number = ?", "411111111111").Find(&user)

Pluck

Pluck，查询 model 中的一个列作为切片，如果您想要查询多个列，您应该使用 Scan

var ages []int64
db.Find(&users).Pluck("age", &ages)

var names []string
db.Model(&User{}).Pluck("name", &names)

db.Table("deleted_users").Pluck("name", &names)

// 想查询多个字段？ 这样做：
db.Select("name, age").Find(&users)

扫描

Scan，扫描结果至一个 struct.

type Result struct {
  Name string
  Age  int
}

var result Result
db.Table("users").Select("name, age").Where("name = ?", "Antonio").Scan(&result)

var results []Result
db.Table("users").Select("name, age").Where("id > ?", 0).Scan(&results)

// 原生 SQL
db.Raw("SELECT name, age FROM users WHERE name = ?", "Antonio").Scan(&result)

链式操作相关

链式操作

Method Chaining，Gorm 实现了链式操作接口，所以你可以把代码写成这样：

// 创建一个查询
tx := db.Where("name = ?", "jinzhu")

// 添加更多条件
if someCondition {
  tx = tx.Where("age = ?", 20)
} else {
  tx = tx.Where("age = ?", 30)
}

if yetAnotherCondition {
  tx = tx.Where("active = ?", 1)
}

在调用立即执行方法前不会生成Query语句，借助这个特性你可以创建一个函数来处理一些通用逻辑。

立即执行方法

Immediate methods ，立即执行方法是指那些会立即生成SQL语句并发送到数据库的方法, 他们一般是CRUD方法，比如：

Create, First, Find, Take, Save, UpdateXXX, Delete, Scan, Row, Rows…

这有一个基于上面链式方法代码的立即执行方法的例子：

tx.Find(&user)

生成的SQL语句如下：

SELECT * FROM users where name = 'jinzhu' AND age = 30 AND active = 1;

范围

Scopes，Scope是建立在链式操作的基础之上的。

基于它，你可以抽取一些通用逻辑，写出更多可重用的函数库。

func AmountGreaterThan1000(db *gorm.DB) *gorm.DB {
  return db.Where("amount > ?", 1000)
}

func PaidWithCreditCard(db *gorm.DB) *gorm.DB {
  return db.Where("pay_mode_sign = ?", "C")
}

func PaidWithCod(db *gorm.DB) *gorm.DB {
  return db.Where("pay_mode_sign = ?", "C")
}

func OrderStatus(status []string) func (db *gorm.DB) *gorm.DB {
  return func (db *gorm.DB) *gorm.DB {
    return db.Scopes(AmountGreaterThan1000).Where("status IN (?)", status)
  }
}

db.Scopes(AmountGreaterThan1000, PaidWithCreditCard).Find(&orders)
// 查找所有金额大于 1000 的信用卡订单

db.Scopes(AmountGreaterThan1000, PaidWithCod).Find(&orders)
// 查找所有金额大于 1000 的 COD 订单

db.Scopes(AmountGreaterThan1000, OrderStatus([]string{"paid", "shipped"})).Find(&orders)
// 查找所有金额大于 1000 且已付款或者已发货的订单

多个立即执行方法

Multiple Immediate Methods，在 GORM 中使用多个立即执行方法时，后一个立即执行方法会复用前一个立即执行方法的条件 (不包括内联条件) 。

db.Where("name LIKE ?", "jinzhu%").Find(&users, "id IN (?)", []int{1, 2, 3}).Count(&count)

生成的 Sql

SELECT * FROM users WHERE name LIKE 'jinzhu%' AND id IN (1, 2, 3)

SELECT count(*) FROM users WHERE name LIKE 'jinzhu%'

// 使用 Scanner/Valuer
type Users struct {
	gorm.Model
	//Name *string `gorm:"type:varchar(100);default:RandySun;comment:姓名"`
	Name sql.NullString `gorm:"type:varchar(100);default:RandySun;comment:姓名"`
	Age  int64
	// 设置默认值
}

db.Debug().Create(&Users{Name: sql.NullString{String: "", Valid: true}, Age: 19}) // 添加记录name默认为Null

$E:\Lenrn_notes\21go\2.0笔记整理\image\image-20211120220904299.png$

$E:\Lenrn_notes\21go\2.0笔记整理\image\image-20211120220830798.png$

$E:\Lenrn_notes\21go\2.0笔记整理\image\image-20211120220348541.png$

扩展创建选项

例如PostgreSQL数据库中可以使用下面的方式实现合并插入, 有则更新, 无则插入。

// 为Instert语句添加扩展SQL选项
db.Set("gorm:insert_option", "ON CONFLICT").Create(&product)
// INSERT INTO products (name, code) VALUES ("name", "code") ON CONFLICT;

posted @ 2021-11-30 22:50 RandySun 阅读(280) 评论(0) 编辑收藏举报

会员力量，点亮园子希望

刷新页面返回顶部

RandySun

有角度，才会有方向 在当下的阶段，必将由程序员来主导，甚至比以往更甚

GORM模型查询

GORM模型查询

model

一、检索单个对象

1.1 First

1.2 Take

1.3 Last

1.4 返回结果

二、用主键检索

2.1 First

2.2 First 字符串

2.3 Find

2.4 检索全部对象

三、Where String 条件

3.1 条件查询First

3.2 条件查询First

3.3 全部查询

3.4 IN查询

3.5 LIKE

3.6 AND

3.7 Time

3.8 BETWEEN

四 、Struct & Map查询

4.1 Struct 查询

4.2 Map 查询

4.3 切片条件

五、指定结构体查询字段

六、内联条件

七、Not 条件

八、Or条件

九、Order

十、Limit & Offset

十一、Group By & Having

group by

Group By & Having

十二、Distinct

十三、Joins

十四、Joins 预加载

十五、Scan

十六、选择特定字段

十七、额外查询选项

十八、FirstOrInitSelect

十九、Attrs

二十、Assign

二十一、FirstOrCreate

二十二、Attrs

二十三、Assign

高级查询

子查询

选择字段

排序

数量

偏移

总数

Group & Having

连接

Pluck

扫描

链式操作相关

链式操作

立即执行方法

范围

多个立即执行方法

扩展创建选项

公告

有角度，才会有方向在当下的阶段，必将由程序员来主导，甚至比以往更甚

四、Struct & Map查询