Advanced Query

Smart Select Fields

In GORM, you can efficiently select specific fields using the Select method. This is particularly useful when dealing with large models but requiring only a subset of fields, especially in API responses.

type User struct {
  ID     uint
  Name   string
  Age    int
  Gender string
  
}

type APIUser struct {
  ID   uint
  Name string
}


db.Model(&User{}).Limit(10).Find(&APIUser{})

NOTE In QueryFields mode, all model fields are selected by their names.

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


db.Find(&user)



db.Session(&gorm.Session{QueryFields: true}).Find(&user)

Locking

GORM supports different types of locks, for example:

db.Clauses(clause.Locking{Strength: "UPDATE"}).Find(&users)

The above statement will lock the selected rows for the duration of the transaction. This can be used in scenarios where you are preparing to update the rows and want to prevent other transactions from modifying them until your transaction is complete.

The Strength can be also set to SHARE which locks the rows in a way that allows other transactions to read the locked rows but not to update or delete them.

db.Clauses(clause.Locking{
  Strength: "SHARE",
  Table: clause.Table{Name: clause.CurrentTable},
}).Find(&users)

The Table option can be used to specify the table to lock. This is useful when you are joining multiple tables and want to lock only one of them.

Options can be provided like NOWAIT which tries to acquire a lock and fails immediately with an error if the lock is not available. It prevents the transaction from waiting for other transactions to release their locks.

db.Clauses(clause.Locking{
  Strength: "UPDATE",
  Options: "NOWAIT",
}).Find(&users)

Another option can be SKIP LOCKED which skips over any rows that are already locked by other transactions. This is useful in high concurrency situations where you want to process rows that are not currently locked by other transactions.

For more advanced locking strategies, refer to Raw SQL and SQL Builder.

SubQuery

Subqueries are a powerful feature in SQL, allowing nested queries. GORM can generate subqueries automatically when using a *gorm.DB object as a parameter.

db.Where("amount > (?)", db.Table("orders").Select("AVG(amount)")).Find(&orders)



subQuery := db.Select("AVG(age)").Where("name LIKE ?", "name%").Table("users")
db.Select("AVG(age) as avgage").Group("name").Having("AVG(age) > (?)", subQuery).Find(&results)

From SubQuery

GORM allows the use of subqueries in the FROM clause, enabling complex queries and data organization.

db.Table("(?) as u", db.Model(&User{}).Select("name", "age")).Where("age = ?", 18).Find(&User{})



subQuery1 := db.Model(&User{}).Select("name")
subQuery2 := db.Model(&Pet{}).Select("name")
db.Table("(?) as u, (?) as p", subQuery1, subQuery2).Find(&User{})

Group Conditions

Group Conditions in GORM provide a more readable and maintainable way to write complex SQL queries involving multiple conditions.

db.Where(
  db.Where("pizza = ?", "pepperoni").Where(db.Where("size = ?", "small").Or("size = ?", "medium")),
).Or(
  db.Where("pizza = ?", "hawaiian").Where("size = ?", "xlarge"),
).Find(&Pizza{})

IN with multiple columns

GORM supports the IN clause with multiple columns, allowing you to filter data based on multiple field values in a single query.

db.Where("(name, age, role) IN ?", [][]interface{}{{"jinzhu", 18, "admin"}, {"jinzhu2", 19, "user"}}).Find(&users)

Named Argument

GORM enhances the readability and maintainability of SQL queries by supporting named arguments. This feature allows for clearer and more organized query construction, especially in complex queries with multiple parameters. Named arguments can be utilized using either sql.NamedArg or map[string]interface{}{}, providing flexibility in how you structure your queries.

db.Where("name1 = @name OR name2 = @name", sql.Named("name", "jinzhu")).Find(&user)



db.Where("name1 = @name OR name2 = @name", map[string]interface{}{"name": "jinzhu"}).First(&user)

For more examples and details, see Raw SQL and SQL Builder

Find To Map

GORM provides flexibility in querying data by allowing results to be scanned into a map[string]interface{} or []map[string]interface{}, which can be useful for dynamic data structures.

When using Find To Map, it’s crucial to include Model or Table in your query to explicitly specify the table name. This ensures that GORM understands which table to query against.

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



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

FirstOrInit

GORM’s FirstOrInit method is utilized to fetch the first record that matches given conditions, or initialize a new instance if no matching record is found. This method is compatible with both struct and map conditions and allows additional flexibility with the Attrs and Assign methods.

var user User
db.FirstOrInit(&user, User{Name: "non_existing"})



db.Where(User{Name: "jinzhu"}).FirstOrInit(&user)



db.FirstOrInit(&user, map[string]interface{}{"name": "jinzhu"})

Using Attrs for Initialization

When no record is found, you can use Attrs to initialize a struct with additional attributes. These attributes are included in the new struct but are not used in the SQL query.

db.Where(User{Name: "non_existing"}).Attrs(User{Age: 20}).FirstOrInit(&user)




db.Where(User{Name: "Jinzhu"}).Attrs(User{Age: 20}).FirstOrInit(&user)

Using Assign for Attributes

The Assign method allows you to set attributes on the struct regardless of whether the record is found or not. These attributes are set on the struct but are not used to build the SQL query and the final data won’t be saved into the database.

db.Where(User{Name: "non_existing"}).Assign(User{Age: 20}).FirstOrInit(&user)



db.Where(User{Name: "Jinzhu"}).Assign(User{Age: 20}).FirstOrInit(&user)

FirstOrInit, along with Attrs and Assign, provides a powerful and flexible way to ensure a record exists and is initialized or updated with specific attributes in a single step.

FirstOrCreate

FirstOrCreate in GORM is used to fetch the first record that matches given conditions or create a new one if no matching record is found. This method is effective with both struct and map conditions. The RowsAffected property is useful to determine the number of records created or updated.

result := db.FirstOrCreate(&user, User{Name: "non_existing"})





result = db.Where(User{Name: "jinzhu"}).FirstOrCreate(&user)

Using Attrs with FirstOrCreate

Attrs can be used to specify additional attributes for the new record if it is not found. These attributes are used for creation but not in the initial search query.

db.Where(User{Name: "non_existing"}).Attrs(User{Age: 20}).FirstOrCreate(&user)





db.Where(User{Name: "jinzhu"}).Attrs(User{Age: 20}).FirstOrCreate(&user)

Using Assign with FirstOrCreate

The Assign method sets attributes on the record regardless of whether it is found or not, and these attributes are saved back to the database.

db.Where(User{Name: "non_existing"}).Assign(User{Age: 20}).FirstOrCreate(&user)





db.Where(User{Name: "jinzhu"}).Assign(User{Age: 20}).FirstOrCreate(&user)

Optimizer/Index Hints

GORM includes support for optimizer and index hints, allowing you to influence the query optimizer’s execution plan. This can be particularly useful in optimizing query performance or when dealing with complex queries.

Optimizer hints are directives that suggest how a database’s query optimizer should execute a query. GORM facilitates the use of optimizer hints through the gorm.io/hints package.

import "gorm.io/hints"


db.Clauses(hints.New("MAX_EXECUTION_TIME(10000)")).Find(&User{})

Index Hints

Index hints provide guidance to the database about which indexes to use. They can be beneficial if the query planner is not selecting the most efficient indexes for a query.

import "gorm.io/hints"


db.Clauses(hints.UseIndex("idx_user_name")).Find(&User{})



db.Clauses(hints.ForceIndex("idx_user_name", "idx_user_id").ForJoin()).Find(&User{})

These hints can significantly impact query performance and behavior, especially in large databases or complex data models. For more detailed information and additional examples, refer to Optimizer Hints/Index/Comment in the GORM documentation.

Iteration

GORM supports the iteration over query results using the Rows method. This feature is particularly useful when you need to process large datasets or perform operations on each record individually.

You can iterate through rows returned by a query, scanning each row into a struct. This method provides granular control over how each record is handled.

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

for rows.Next() {
  var user User
  
  db.ScanRows(rows, &user)

  
}

This approach is ideal for complex data processing that cannot be easily achieved with standard query methods.

FindInBatches

FindInBatches allows querying and processing records in batches. This is especially useful for handling large datasets efficiently, reducing memory usage and improving performance.

With FindInBatches, GORM processes records in specified batch sizes. Inside the batch processing function, you can apply operations to each batch of records.

result := db.Where("processed = ?", false).FindInBatches(&results, 100, func(tx *gorm.DB, batch int) error {
  for _, result := range results {
    
  }

  
  tx.Save(&results)

  
  

  
  return nil
})

FindInBatches is an effective tool for processing large volumes of data in manageable chunks, optimizing resource usage and performance.

Query Hooks

GORM offers the ability to use hooks, such as AfterFind, which are triggered during the lifecycle of a query. These hooks allow for custom logic to be executed at specific points, such as after a record has been retrieved from the database.

This hook is useful for post-query data manipulation or default value settings. For more detailed information and additional hook types, refer to Hooks in the GORM documentation.

func (u *User) AfterFind(tx *gorm.DB) (err error) {
  
  if u.Role == "" {
    u.Role = "user" 
  }
  return
}

Pluck

The Pluck method in GORM is used to query a single column from the database and scan the result into a slice. This method is ideal for when you need to retrieve specific fields from a model.

If you need to query more than one column, you can use Select with Scan or Find instead.

var ages []int64
db.Model(&User{}).Pluck("age", &ages)


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


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


db.Model(&User{}).Distinct().Pluck("Name", &names)



db.Select("name", "age").Scan(&users)
db.Select("name", "age").Find(&users)

Scopes

Scopes in GORM are a powerful feature that allows you to define commonly-used query conditions as reusable methods. These scopes can be easily referenced in your queries, making your code more modular and readable.

Defining Scopes

Scopes are defined as functions that modify and return a gorm.DB instance. You can define a variety of conditions as scopes based on your application’s requirements.

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 = ?", "COD")
}


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

Applying Scopes in Queries

You can apply one or more scopes to a query by using the Scopes method. This allows you to chain multiple conditions dynamically.

db.Scopes(AmountGreaterThan1000, PaidWithCreditCard).Find(&orders)


db.Scopes(AmountGreaterThan1000, PaidWithCod).Find(&orders)


db.Scopes(AmountGreaterThan1000, OrderStatus([]string{"paid", "shipped"})).Find(&orders)

Scopes are a clean and efficient way to encapsulate common query logic, enhancing the maintainability and readability of your code. For more detailed examples and usage, refer to Scopes in the GORM documentation.

Count

The Count method in GORM is used to retrieve the number of records that match a given query. It’s a useful feature for understanding the size of a dataset, particularly in scenarios involving conditional queries or data analysis.

Getting the Count of Matched Records

You can use Count to determine the number of records that meet specific criteria in your queries.

var count int64


db.Model(&User{}).Where("name = ?", "jinzhu").Or("name = ?", "jinzhu 2").Count(&count)



db.Model(&User{}).Where("name = ?", "jinzhu").Count(&count)



db.Table("deleted_users").Count(&count)

Count with Distinct and Group

GORM also allows counting distinct values and grouping results.

db.Model(&User{}).Distinct("name").Count(&count)



db.Table("deleted_users").Select("count(distinct(name))").Count(&count)



users := []User{
  {Name: "name1"},
  {Name: "name2"},
  {Name: "name3"},
  {Name: "name3"},
}

db.Model(&User{}).Group("name").Count(&count)