CRUD Interface - Advanced Query - 《GORM v1.23.5 Documentation》

Smart Select Fields
Locking (FOR UPDATE)
SubQuery
- From SubQuery
Group Conditions
IN with multiple columns
Named Argument
Find To Map
FirstOrInit
FirstOrCreate
Optimizer/Index Hints
Iteration
FindInBatches
Query Hooks
Pluck
Scopes
Count

Smart Select Fields

GORM allows select specific fields with Select, if you often use this in your application, maybe you want to define a smaller struct for API usage which can select specific fields automatically, for example:

1. type User struct {
2. ID     uint
3. Name   string
4. Age    int
5. Gender string
6. // hundreds of fields
7. }

9. type APIUser struct {
10. ID   uint
11. Name string
12. }

14. // Select `id`, `name` automatically when querying
15. db.Model(&User{}).Limit(10).Find(&APIUser{})
16. // SELECT `id`, `name` FROM `users` LIMIT 10

NOTE QueryFields mode will select by all fields’ name for current model

1. db, err := gorm.Open(sqlite.Open("gorm.db"), &gorm.Config{
2. QueryFields: true,
3. })

5. db.Find(&user)
6. // SELECT `users`.`name`, `users`.`age`, ... FROM `users` // with this option

8. // Session Mode
9. db.Session(&gorm.Session{QueryFields: true}).Find(&user)
10. // SELECT `users`.`name`, `users`.`age`, ... FROM `users`

Locking (FOR UPDATE)

GORM supports different types of locks, for example:

1. db.Clauses(clause.Locking{Strength: "UPDATE"}).Find(&users)
2. // SELECT * FROM `users` FOR UPDATE

4. db.Clauses(clause.Locking{
5. Strength: "SHARE",
6. Table: clause.Table{Name: clause.CurrentTable},
7. }).Find(&users)
8. // SELECT * FROM `users` FOR SHARE OF `users`

10. db.Clauses(clause.Locking{
11. Strength: "UPDATE",
12. Options: "NOWAIT",
13. }).Find(&users)
14. // SELECT * FROM `users` FOR UPDATE NOWAIT

Refer Raw SQL and SQL Builder for more detail

SubQuery

A subquery can be nested within a query, GORM can generate subquery when using a *gorm.DB object as param

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

4. subQuery := db.Select("AVG(age)").Where("name LIKE ?", "name%").Table("users")
5. db.Select("AVG(age) as avgage").Group("name").Having("AVG(age) > (?)", subQuery).Find(&results)
6. // SELECT AVG(age) as avgage FROM `users` GROUP BY `name` HAVING AVG(age) > (SELECT AVG(age) FROM `users` WHERE name LIKE "name%")

From SubQuery

GORM allows you using subquery in FROM clause with method Table, for example:

1. db.Table("(?) as u", db.Model(&User{}).Select("name", "age")).Where("age = ?", 18}).Find(&User{})
2. // SELECT * FROM (SELECT `name`,`age` FROM `users`) as u WHERE `age` = 18

4. subQuery1 := db.Model(&User{}).Select("name")
5. subQuery2 := db.Model(&Pet{}).Select("name")
6. db.Table("(?) as u, (?) as p", subQuery1, subQuery2).Find(&User{})
7. // SELECT * FROM (SELECT `name` FROM `users`) as u, (SELECT `name` FROM `pets`) as p

Group Conditions

Easier to write complicated SQL query with Group Conditions

1. db.Where(
2. db.Where("pizza = ?", "pepperoni").Where(db.Where("size = ?", "small").Or("size = ?", "medium")),
3. ).Or(
4. db.Where("pizza = ?", "hawaiian").Where("size = ?", "xlarge"),
5. ).Find(&Pizza{}).Statement

7. // SELECT * FROM `pizzas` WHERE (pizza = "pepperoni" AND (size = "small" OR size = "medium")) OR (pizza = "hawaiian" AND size = "xlarge")

IN with multiple columns

Selecting IN with multiple columns

1. db.Where("(name, age, role) IN ?", [][]interface{}{{"jinzhu", 18, "admin"}, {"jinzhu2", 19, "user"}}).Find(&users)
2. // SELECT * FROM users WHERE (name, age, role) IN (("jinzhu", 18, "admin"), ("jinzhu 2", 19, "user"));

Named Argument

GORM supports named arguments with sql.NamedArg or map[string]interface{}{}, for example:

1. db.Where("name1 = @name OR name2 = @name", sql.Named("name", "jinzhu")).Find(&user)
2. // SELECT * FROM `users` WHERE name1 = "jinzhu" OR name2 = "jinzhu"

4. db.Where("name1 = @name OR name2 = @name", map[string]interface{}{"name": "jinzhu"}).First(&user)
5. // SELECT * FROM `users` WHERE name1 = "jinzhu" OR name2 = "jinzhu" ORDER BY `users`.`id` LIMIT 1

Check out Raw SQL and SQL Builder for more detail

Find To Map

GORM allows scan result to map[string]interface{} or []map[string]interface{}, don’t forget to specify Model or Table, for example:

1. result := map[string]interface{}{}
2. db.Model(&User{}).First(&result, "id = ?", 1)

4. var results []map[string]interface{}
5. db.Table("users").Find(&results)

FirstOrInit

Get first matched record or initialize a new instance with given conditions (only works with struct or map conditions)

1. // User not found, initialize it with give conditions
2. db.FirstOrInit(&user, User{Name: "non_existing"})
3. // user -> User{Name: "non_existing"}

5. // Found user with `name` = `jinzhu`
6. db.Where(User{Name: "jinzhu"}).FirstOrInit(&user)
7. // user -> User{ID: 111, Name: "Jinzhu", Age: 18}

9. // Found user with `name` = `jinzhu`
10. db.FirstOrInit(&user, map[string]interface{}{"name": "jinzhu"})
11. // user -> User{ID: 111, Name: "Jinzhu", Age: 18}

initialize struct with more attributes if record not found, those Attrs won’t be used to build SQL query

1. // User not found, initialize it with give conditions and Attrs
2. db.Where(User{Name: "non_existing"}).Attrs(User{Age: 20}).FirstOrInit(&user)
3. // SELECT * FROM USERS WHERE name = 'non_existing' ORDER BY id LIMIT 1;
4. // user -> User{Name: "non_existing", Age: 20}

6. // User not found, initialize it with give conditions and Attrs
7. db.Where(User{Name: "non_existing"}).Attrs("age", 20).FirstOrInit(&user)
8. // SELECT * FROM USERS WHERE name = 'non_existing' ORDER BY id LIMIT 1;
9. // user -> User{Name: "non_existing", Age: 20}

11. // Found user with `name` = `jinzhu`, attributes will be ignored
12. db.Where(User{Name: "Jinzhu"}).Attrs(User{Age: 20}).FirstOrInit(&user)
13. // SELECT * FROM USERS WHERE name = jinzhu' ORDER BY id LIMIT 1;
14. // user -> User{ID: 111, Name: "Jinzhu", Age: 18}

Assign attributes to struct regardless it is found or not, those attributes won’t be used to build SQL query and the final data won’t be saved into database

1. // User not found, initialize it with give conditions and Assign attributes
2. db.Where(User{Name: "non_existing"}).Assign(User{Age: 20}).FirstOrInit(&user)
3. // user -> User{Name: "non_existing", Age: 20}

5. // Found user with `name` = `jinzhu`, update it with Assign attributes
6. db.Where(User{Name: "Jinzhu"}).Assign(User{Age: 20}).FirstOrInit(&user)
7. // SELECT * FROM USERS WHERE name = jinzhu' ORDER BY id LIMIT 1;
8. // user -> User{ID: 111, Name: "Jinzhu", Age: 20}

FirstOrCreate

Get first matched record or create a new one with given conditions (only works with struct, map conditions), RowsAffected returns created/updated record’s count

1. // User not found, create a new record with give conditions
2. result := db.FirstOrCreate(&user, User{Name: "non_existing"})
3. // INSERT INTO "users" (name) VALUES ("non_existing");
4. // user -> User{ID: 112, Name: "non_existing"}
5. // result.RowsAffected // => 0

7. // Found user with `name` = `jinzhu`
8. result := db.Where(User{Name: "jinzhu"}).FirstOrCreate(&user)
9. // user -> User{ID: 111, Name: "jinzhu", "Age": 18}
10. // result.RowsAffected // => 0

Create struct with more attributes if record not found, those Attrs won’t be used to build SQL query

1. // User not found, create it with give conditions and Attrs
2. db.Where(User{Name: "non_existing"}).Attrs(User{Age: 20}).FirstOrCreate(&user)
3. // SELECT * FROM users WHERE name = 'non_existing' ORDER BY id LIMIT 1;
4. // INSERT INTO "users" (name, age) VALUES ("non_existing", 20);
5. // user -> User{ID: 112, Name: "non_existing", Age: 20}

7. // Found user with `name` = `jinzhu`, attributes will be ignored
8. db.Where(User{Name: "jinzhu"}).Attrs(User{Age: 20}).FirstOrCreate(&user)
9. // SELECT * FROM users WHERE name = 'jinzhu' ORDER BY id LIMIT 1;
10. // user -> User{ID: 111, Name: "jinzhu", Age: 18}

Assign attributes to the record regardless it is found or not and save them back to the database.

1. // User not found, initialize it with give conditions and Assign attributes
2. db.Where(User{Name: "non_existing"}).Assign(User{Age: 20}).FirstOrCreate(&user)
3. // SELECT * FROM users WHERE name = 'non_existing' ORDER BY id LIMIT 1;
4. // INSERT INTO "users" (name, age) VALUES ("non_existing", 20);
5. // user -> User{ID: 112, Name: "non_existing", Age: 20}

7. // Found user with `name` = `jinzhu`, update it with Assign attributes
8. db.Where(User{Name: "jinzhu"}).Assign(User{Age: 20}).FirstOrCreate(&user)
9. // SELECT * FROM users WHERE name = 'jinzhu' ORDER BY id LIMIT 1;
10. // UPDATE users SET age=20 WHERE id = 111;
11. // user -> User{ID: 111, Name: "jinzhu", Age: 20}

Optimizer/Index Hints

Optimizer hints allow to control the query optimizer to choose a certain query execution plan, GORM supports it with gorm.io/hints, e.g:

1. import "gorm.io/hints"

3. db.Clauses(hints.New("MAX_EXECUTION_TIME(10000)")).Find(&User{})
4. // SELECT * /*+ MAX_EXECUTION_TIME(10000) */ FROM `users`

Index hints allow passing index hints to the database in case the query planner gets confused.

1. import "gorm.io/hints"

3. db.Clauses(hints.UseIndex("idx_user_name")).Find(&User{})
4. // SELECT * FROM `users` USE INDEX (`idx_user_name`)

6. db.Clauses(hints.ForceIndex("idx_user_name", "idx_user_id").ForJoin()).Find(&User{})
7. // SELECT * FROM `users` FORCE INDEX FOR JOIN (`idx_user_name`,`idx_user_id`)"

Refer Optimizer Hints/Index/Comment for more details

Iteration

GORM supports iterating through Rows

1. rows, err := db.Model(&User{}).Where("name = ?", "jinzhu").Rows()
2. defer rows.Close()

4. for rows.Next() {
5. var user User
6. // ScanRows is a method of `gorm.DB`, it can be used to scan a row into a struct
7. db.ScanRows(rows, &user)

9. // do something
10. }

FindInBatches

Query and process records in batch

1. // batch size 100
2. result := db.Where("processed = ?", false).FindInBatches(&results, 100, func(tx *gorm.DB, batch int) error {
3. for _, result := range results {
4. // batch processing found records
5. }

7. tx.Save(&results)

9. tx.RowsAffected // number of records in this batch

11. batch // Batch 1, 2, 3

13. // returns error will stop future batches
14. return nil
15. })

17. result.Error // returned error
18. result.RowsAffected // processed records count in all batches

Query Hooks

GORM allows hooks AfterFind for a query, it will be called when querying a record, refer Hooks for details

1. func (u *User) AfterFind(tx *gorm.DB) (err error) {
2. if u.Role == "" {
3. u.Role = "user"
4. }
5. return
6. }

Pluck

Query single column from database and scan into a slice, if you want to query multiple columns, use Select with Scan instead

1. var ages []int64
2. db.Model(&users).Pluck("age", &ages)

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

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

9. // Distinct Pluck
10. db.Model(&User{}).Distinct().Pluck("Name", &names)
11. // SELECT DISTINCT `name` FROM `users`

13. // Requesting more than one column, use `Scan` or `Find` like this:
14. db.Select("name", "age").Scan(&users)
15. db.Select("name", "age").Find(&users)

Scopes

Scopes allows you to specify commonly-used queries which can be referenced as method calls

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

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

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

13. func OrderStatus(status []string) func (db *gorm.DB) *gorm.DB {
14. return func (db *gorm.DB) *gorm.DB {
15. return db.Where("status IN (?)", status)
16. }
17. }

19. db.Scopes(AmountGreaterThan1000, PaidWithCreditCard).Find(&orders)
20. // Find all credit card orders and amount greater than 1000

22. db.Scopes(AmountGreaterThan1000, PaidWithCod).Find(&orders)
23. // Find all COD orders and amount greater than 1000

25. db.Scopes(AmountGreaterThan1000, OrderStatus([]string{"paid", "shipped"})).Find(&orders)
26. // Find all paid, shipped orders that amount greater than 1000

Checkout Scopes for details

Count

Get matched records count

1. var count int64
2. db.Model(&User{}).Where("name = ?", "jinzhu").Or("name = ?", "jinzhu 2").Count(&count)
3. // SELECT count(1) FROM users WHERE name = 'jinzhu' OR name = 'jinzhu 2'

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

8. db.Table("deleted_users").Count(&count)
9. // SELECT count(1) FROM deleted_users;

11. // Count with Distinct
12. db.Model(&User{}).Distinct("name").Count(&count)
13. // SELECT COUNT(DISTINCT(`name`)) FROM `users`

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

18. // Count with Group
19. users := []User{
20. {Name: "name1"},
21. {Name: "name2"},
22. {Name: "name3"},
23. {Name: "name3"},
24. }

26. db.Model(&User{}).Group("name").Count(&count)
27. count // => 3