Query

Retrieving a single object

GORM provides First, Take, Last methods to retrieve a single object from the database, it adds LIMIT 1 condition when querying the database, and it will return the error ErrRecordNotFound if no record is found.

Generics API

ctx := context.Background()


user, err := gorm.G[User](db).First(ctx)



user, err := gorm.G[User](db).Take(ctx)



user, err := gorm.G[User](db).Last(ctx)



errors.Is(err, gorm.ErrRecordNotFound)

Traditional API


db.First(&user)



db.Take(&user)



db.Last(&user)


result := db.First(&user)
result.RowsAffected 
result.Error        


errors.Is(result.Error, gorm.ErrRecordNotFound)

If you want to avoid the ErrRecordNotFound error, you could use Find like db.Limit(1).Find(&user), the Find method accepts both struct and slice data

Using Find without a limit for single object db.Find(&user) will query the full table and return only the first object which is non-deterministic and not performant

The First and Last methods will find the first and last record (respectively) as ordered by primary key. They only work when a pointer to the destination struct is passed to the methods as argument or when the model is specified using db.Model(). Additionally, if no primary key is defined for relevant model, then the model will be ordered by the first field. For example:

var user User
var users []User


db.First(&user)



result := map[string]interface{}{}
db.Model(&User{}).First(&result)



result := map[string]interface{}{}
db.Table("users").First(&result)


result := map[string]interface{}{}
db.Table("users").Take(&result)



type Result struct {
  ID   int
  Name string
}
var joinResult Result
db.Table("user_languages ul").
  Select("l.id, l.name").
  Joins("JOIN languages l ON l.code = ul.language_code").
  Order("l.name DESC").
  First(&joinResult)



db.Table("user_languages ul").
  Select("l.id, l.name").
  Joins("JOIN languages l ON l.code = ul.language_code").
  Order("l.name DESC").
  Take(&joinResult)



type Language struct {
  Code string
  Name string
}
db.First(&Language{})

Retrieving objects with primary key

Objects can be retrieved using primary key by using Inline Conditions if the primary key is a number. When working with strings, extra care needs to be taken to avoid SQL Injection; check out Security section for details.

Generics API

ctx := context.Background()


user, err := gorm.G[User](db).Where("id = ?", 10).First(ctx)



user, err := gorm.G[User](db).Where("id = ?", "10").First(ctx)



users, err := gorm.G[User](db).Where("id IN ?", []int{1,2,3}).Find(ctx)



user, err := gorm.G[User](db).Where("id = ?", "1b74413f-f3b8-409f-ac47-e8c062e3472a").First(ctx)

Traditional API

db.First(&user, 10)


db.First(&user, "10")


db.Find(&users, []int{1,2,3})

If the primary key is a string (for example, like a uuid), the query will be written as follows:

db.First(&user, "id = ?", "1b74413f-f3b8-409f-ac47-e8c062e3472a")

When the destination object has a primary value, the primary key will be used to build the condition, for example:

var user = User{ID: 10}
db.First(&user)


var result User
db.Model(User{ID: 10}).First(&result)

NOTE: If you use gorm’s specific field types like gorm.DeletedAt, it will run a different query for retrieving object/s.

type User struct {
  ID           string `gorm:"primarykey;size:16"`
  Name         string `gorm:"size:24"`
  DeletedAt    gorm.DeletedAt `gorm:"index"`
}

var user = User{ID: 15}
db.First(&user)

Retrieving all objects


result := db.Find(&users)


result.RowsAffected 
result.Error

Conditions

String Conditions


db.Where("name = ?", "jinzhu").First(&user)



db.Where("name <> ?", "jinzhu").Find(&users)



db.Where("name IN ?", []string{"jinzhu", "jinzhu 2"}).Find(&users)



db.Where("name LIKE ?", "%jin%").Find(&users)



db.Where("name = ? AND age >= ?", "jinzhu", "22").Find(&users)



db.Where("updated_at > ?", lastWeek).Find(&users)



db.Where("created_at BETWEEN ? AND ?", lastWeek, today).Find(&users)

If the object’s primary key has been set, then condition query wouldn’t cover the value of primary key but use it as a ‘and’ condition. For example:
var user = User{ID: 10}
db.Where("id = ?", 20).First(&user)


This query would give `record not found` Error. So set the primary key attribute such as `id` to nil before you want to use the variable such as `user` to get new value from database.

Struct & Map Conditions


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



db.Where(map[string]interface{}{"name": "jinzhu", "age": 20}).Find(&users)



db.Where([]int64{20, 21, 22}).Find(&users)

NOTE When querying with struct, GORM will only query with non-zero fields, that means if your field’s value is 0, '', false or other zero values, it won’t be used to build query conditions, for example:

db.Where(&User{Name: "jinzhu", Age: 0}).Find(&users)

To include zero values in the query conditions, you can use a map, which will include all key-values as query conditions, for example:

db.Where(map[string]interface{}{"Name": "jinzhu", "Age": 0}).Find(&users)

For more details, see Specify Struct search fields.

Specify Struct search fields

When searching with struct, you can specify which particular values from the struct to use in the query conditions by passing in the relevant field name or the dbname to Where(), for example:

db.Where(&User{Name: "jinzhu"}, "name", "Age").Find(&users)


db.Where(&User{Name: "jinzhu"}, "Age").Find(&users)

Inline Condition

Query conditions can be inlined into methods like First and Find in a similar way to Where.


db.First(&user, "id = ?", "string_primary_key")



db.Find(&user, "name = ?", "jinzhu")


db.Find(&users, "name <> ? AND age > ?", "jinzhu", 20)



db.Find(&users, User{Age: 20})



db.Find(&users, map[string]interface{}{"age": 20})

Not Conditions

Build NOT conditions, works similar to Where

db.Not("name = ?", "jinzhu").First(&user)



db.Not(map[string]interface{}{"name": []string{"jinzhu", "jinzhu 2"}}).Find(&users)



db.Not(User{Name: "jinzhu", Age: 18}).First(&user)



db.Not([]int64{1,2,3}).First(&user)

Or Conditions

db.Where("role = ?", "admin").Or("role = ?", "super_admin").Find(&users)



db.Where("name = 'jinzhu'").Or(User{Name: "jinzhu 2", Age: 18}).Find(&users)



db.Where("name = 'jinzhu'").Or(map[string]interface{}{"name": "jinzhu 2", "age": 18}).Find(&users)

For more complicated SQL queries. please also refer to Group Conditions in Advanced Query.

Selecting Specific Fields

Select allows you to specify the fields that you want to retrieve from database. Otherwise, GORM will select all fields by default.

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


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


db.Table("users").Select("COALESCE(age,?)", 42).Rows()

Also check out Smart Select Fields

Order

Specify order when retrieving records from the database

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



db.Order("age desc").Order("name").Find(&users)


db.Clauses(clause.OrderBy{
  Expression: clause.Expr{SQL: "FIELD(id,?)", Vars: []interface{}{[]int{1, 2, 3}}, WithoutParentheses: true},
}).Find(&User{})

Limit & Offset

Limit specify the max number of records to retrieve
Offset specify the number of records to skip before starting to return the records

db.Limit(3).Find(&users)



db.Limit(10).Find(&users1).Limit(-1).Find(&users2)



db.Offset(3).Find(&users)


db.Limit(10).Offset(5).Find(&users)



db.Offset(10).Find(&users1).Offset(-1).Find(&users2)

Refer to Pagination for details on how to make a paginator

Group By & Having

type result struct {
  Date  time.Time
  Total int
}

db.Model(&User{}).Select("name, sum(age) as total").Where("name LIKE ?", "group%").Group("name").First(&result)



db.Model(&User{}).Select("name, sum(age) as total").Group("name").Having("name = ?", "group").Find(&result)


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

rows, err := db.Table("orders").Select("date(created_at) as date, sum(amount) as total").Group("date(created_at)").Having("sum(amount) > ?", 100).Rows()
defer rows.Close()
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)

Distinct

Selecting distinct values from the model

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

Distinct works with Pluck and Count too

Joins

Generics API

The new GORM generics interface brings enhanced support for association queries (Joins), offering more flexible association methods, more expressive query capabilities, and a significantly simplified approach to building complex queries.

Joins: Easily specify different join types (e.g., InnerJoin, LeftJoin) and customize join conditions based on associations, making complex cross-table queries clearer and more intuitive.


users, err := gorm.G[User](db).Joins(clause.Has("Company"), nil).Find(ctx)


user, err = gorm.G[User](db).Joins(clause.LeftJoin.Association("Company"), func(db gorm.JoinBuilder, joinTable clause.Table, curTable clause.Table) error {
    db.Where(map[string]any{"name": company.Name})
    return nil
}).Where(map[string]any{"name": user.Name}).First(ctx)


users, err = gorm.G[User](db).Joins(clause.LeftJoin.AssociationFrom("Company", gorm.G[Company](DB).Select("Name")).As("t"),
    func(db gorm.JoinBuilder, joinTable clause.Table, curTable clause.Table) error {
        db.Where("?.name = ?", joinTable, u.Company.Name)
        return nil
    },
).Find(ctx)

Traditional API

Specify Joins conditions

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{})


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 Preloading

You can use Joins eager loading associations with a single SQL, for example:

db.Joins("Company").Find(&users)



db.InnerJoins("Company").Find(&users)

Join with conditions

db.Joins("Company", db.Where(&Company{Alive: true})).Find(&users)

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

Joins a Derived Table

You can also use Joins to join a derived table.

type User struct {
  Id  int
  Age int
}

type Order struct {
  UserId     int
  FinishedAt *time.Time
}

query := db.Table("order").Select("MAX(order.finished_at) as latest").Joins("left join user user on order.user_id = user.id").Where("user.age > ?", 18).Group("order.user_id")
db.Model(&Order{}).Joins("join (?) q on order.finished_at = q.latest", query).Scan(&results)

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)


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

Retrieving a single object

Generics API

Traditional API

Retrieving objects with primary key

Generics API

Traditional API

Retrieving all objects

Conditions

String Conditions

Struct & Map Conditions

Specify Struct search fields

Inline Condition

Not Conditions

Or Conditions

Selecting Specific Fields

Order

Limit & Offset

Group By & Having

Distinct

Joins

Generics API

Traditional API

Joins Preloading

Joins a Derived Table

Scan

评论