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.
// Get the first record ordered by primary key |
If you want to avoid the
ErrRecordNotFound
error, you could useFind
likedb.Limit(1).Find(&user)
, theFind
method accepts both struct and slice data
Using
Find
without a limit for single objectdb.Find(&user)
will query the full table and return only the first object which is not performant and nondeterministic
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 |
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.
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") |
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} |
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 { |
Retrieving all objects
// Get all records |
Conditions
String Conditions
// 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 Conditions
// 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.
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) |
Inline Condition
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 Conditions
Build NOT conditions, works similar to Where
db.Not("name = ?", "jinzhu").First(&user) |
Or Conditions
db.Where("role = ?", "admin").Or("role = ?", "super_admin").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) |
Also check out Smart Select Fields
Order
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 Preloading
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 a Derived Table
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 { |