检索单个对象
GORM 提供了 First
、Take
、Last
方法,以便从数据库中检索单个对象。当查询数据库时它添加了 LIMIT 1
条件,且没有找到记录时,它会返回 ErrRecordNotFound
错误
// 获取第一条记录(主键升序) |
如果你想避免
ErrRecordNotFound
错误,你可以使用Find
,比如db.Limit(1).Find(&user)
,Find
方法可以接受struct和slice的数据。
对单个对象使用
Find
而不带limit,db.Find(&user)
将会查询整个表并且只返回第一个对象,这是性能不高并且不确定的。
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 |
根据主键检索
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.
db.First(&user, 10) |
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") |
当目标对象有一个主键值时,将使用主键构建查询条件,例如:
var user = User{ID: 10} |
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 { |
检索全部对象
// Get all records |
条件
String 条件
// Get first matched record |
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)
// SELECT * FROM users WHERE id = 10 and id = 20 ORDER BY id ASC LIMIT 1This query would give
record not found
Error. So set the primary key attribute such asid
to nil before you want to use the variable such asuser
to get new value from database.
Struct & Map 条件
// Struct |
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.
指定结构体查询字段
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) |
内联条件
Query conditions can be inlined into methods like First
and Find
in a similar way to Where
.
// Get by primary key if it were a non-integer type |
Not 条件
Build NOT conditions, works similar to Where
db.Not("name = ?", "jinzhu").First(&user) |
Or 条件
db.Where("role = ?", "admin").Or("role = ?", "super_admin").Find(&users) |
For more complicated SQL queries. please also refer to Group Conditions in Advanced Query.
选择特定字段
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) |
Also check out Smart Select Fields
排序
Specify order when retrieving records from the database
db.Order("age desc, name").Find(&users) |
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) |
Refer to Pagination for details on how to make a paginator
Group By & Having
type result struct { |
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
Specify Joins conditions
type result struct { |
Joins 预加载
You can use Joins
eager loading associations with a single SQL, for example:
db.Joins("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 一个衍生表
You can also use Joins
to join a derived table.
type User struct { |
Scan
Scanning results into a struct works similarly to the way we use Find
type Result struct { |