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 keydb.First(&user)// SELECT * FROM users ORDER BY id LIMIT 1;// Get one record, no specified orderdb.Take(&user)// SELECT * FROM users LIMIT 1;// Get last record, ordered by primary key descdb.Last(&user)// SELECT * FROM users ORDER BY id DESC LIMIT 1;result := db.First(&user)result.RowsAffected // returns count of records foundresult.Error // returns error or nil// check error ErrRecordNotFounderrors.Is(result.Error, gorm.ErrRecordNotFound)
If you want to avoid the
ErrRecordNotFounderror, you could useFindlikedb.Limit(1).Find(&user), theFindmethod accepts both struct and slice data
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 Uservar users []User// works because destination struct is passed indb.First(&user)// SELECT * FROM `users` ORDER BY `users`.`id` LIMIT 1// works because model is specified using `db.Model()`result := map[string]interface{}{}db.Model(&User{}).First(&result)// SELECT * FROM `users` ORDER BY `users`.`id` LIMIT 1// doesn't workresult := map[string]interface{}{}db.Table("users").First(&result)// works with Takeresult := map[string]interface{}{}db.Table("users").Take(&result)// no primary key defined, results will be ordered by first field (i.e., `Code`)type Language struct {Code stringName string}db.First(&Language{})// SELECT * FROM `languages` ORDER BY `languages`.`code` LIMIT 1
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)// SELECT * FROM users WHERE id = 10;db.First(&user, "10")// SELECT * FROM users WHERE id = 10;db.Find(&users, []int{1,2,3})// SELECT * FROM users WHERE id IN (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")// SELECT * FROM users WHERE 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)// SELECT * FROM users WHERE id = 10;var result Userdb.Model(User{ID: 10}).First(&result)// SELECT * FROM users WHERE id = 10;
Retrieving all objects
// Get all recordsresult := db.Find(&users)// SELECT * FROM users;result.RowsAffected // returns found records count, equals `len(users)`result.Error // returns error
Conditions
String Conditions
// Get first matched recorddb.Where("name = ?", "jinzhu").First(&user)// SELECT * FROM users WHERE name = 'jinzhu' ORDER BY id LIMIT 1;// Get all matched recordsdb.Where("name <> ?", "jinzhu").Find(&users)// SELECT * FROM users WHERE name <> 'jinzhu';// INdb.Where("name IN ?", []string{"jinzhu", "jinzhu 2"}).Find(&users)// SELECT * FROM users WHERE name IN ('jinzhu','jinzhu 2');// LIKEdb.Where("name LIKE ?", "%jin%").Find(&users)// SELECT * FROM users WHERE name LIKE '%jin%';// ANDdb.Where("name = ? AND age >= ?", "jinzhu", "22").Find(&users)// SELECT * FROM users WHERE name = 'jinzhu' AND age >= 22;// Timedb.Where("updated_at > ?", lastWeek).Find(&users)// SELECT * FROM users WHERE updated_at > '2000-01-01 00:00:00';// BETWEENdb.Where("created_at BETWEEN ? AND ?", lastWeek, today).Find(&users)// SELECT * FROM users WHERE created_at BETWEEN '2000-01-01 00:00:00' AND '2000-01-08 00:00:00';
Struct & Map Conditions
// Structdb.Where(&User{Name: "jinzhu", Age: 20}).First(&user)// SELECT * FROM users WHERE name = "jinzhu" AND age = 20 ORDER BY id LIMIT 1;// Mapdb.Where(map[string]interface{}{"name": "jinzhu", "age": 20}).Find(&users)// SELECT * FROM users WHERE name = "jinzhu" AND age = 20;// Slice of primary keysdb.Where([]int64{20, 21, 22}).Find(&users)// SELECT * FROM users WHERE id IN (20, 21, 22);
NOTE When querying with struct, GORM will only query with non-zero fields, that means if your field’s value is
0,'',falseor other zero values, it won’t be used to build query conditions, for example:
db.Where(&User{Name: "jinzhu", Age: 0}).Find(&users)// SELECT * FROM users WHERE name = "jinzhu";
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)// SELECT * FROM users WHERE name = "jinzhu" AND age = 0;
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)// SELECT * FROM users WHERE name = "jinzhu" AND age = 0;db.Where(&User{Name: "jinzhu"}, "Age").Find(&users)// SELECT * FROM users WHERE age = 0;
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 typedb.First(&user, "id = ?", "string_primary_key")// SELECT * FROM users WHERE id = 'string_primary_key';// Plain SQLdb.Find(&user, "name = ?", "jinzhu")// SELECT * FROM users WHERE name = "jinzhu";db.Find(&users, "name <> ? AND age > ?", "jinzhu", 20)// SELECT * FROM users WHERE name <> "jinzhu" AND age > 20;// Structdb.Find(&users, User{Age: 20})// SELECT * FROM users WHERE age = 20;// Mapdb.Find(&users, map[string]interface{}{"age": 20})// SELECT * FROM users WHERE age = 20;
Not Conditions
Build NOT conditions, works similar to Where
db.Not("name = ?", "jinzhu").First(&user)// SELECT * FROM users WHERE NOT name = "jinzhu" ORDER BY id LIMIT 1;// Not Indb.Not(map[string]interface{}{"name": []string{"jinzhu", "jinzhu 2"}}).Find(&users)// SELECT * FROM users WHERE name NOT IN ("jinzhu", "jinzhu 2");// Structdb.Not(User{Name: "jinzhu", Age: 18}).First(&user)// SELECT * FROM users WHERE name <> "jinzhu" AND age <> 18 ORDER BY id LIMIT 1;// Not In slice of primary keysdb.Not([]int64{1,2,3}).First(&user)// SELECT * FROM users WHERE id NOT IN (1,2,3) ORDER BY id LIMIT 1;
Or Conditions
db.Where("role = ?", "admin").Or("role = ?", "super_admin").Find(&users)// SELECT * FROM users WHERE role = 'admin' OR role = 'super_admin';// Structdb.Where("name = 'jinzhu'").Or(User{Name: "jinzhu 2", Age: 18}).Find(&users)// SELECT * FROM users WHERE name = 'jinzhu' OR (name = 'jinzhu 2' AND age = 18);// Mapdb.Where("name = 'jinzhu'").Or(map[string]interface{}{"name": "jinzhu 2", "age": 18}).Find(&users)// SELECT * FROM users WHERE name = 'jinzhu' OR (name = 'jinzhu 2' AND age = 18);
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)// SELECT name, age FROM users;db.Select([]string{"name", "age"}).Find(&users)// SELECT name, age FROM users;db.Table("users").Select("COALESCE(age,?)", 42).Rows()// SELECT COALESCE(age,'42') FROM users;
Also check out Smart Select Fields
Order
Specify order when retrieving records from the database
db.Order("age desc, name").Find(&users)// SELECT * FROM users ORDER BY age desc, name;// Multiple ordersdb.Order("age desc").Order("name").Find(&users)// SELECT * FROM users ORDER BY age desc, name;db.Clauses(clause.OrderBy{Expression: clause.Expr{SQL: "FIELD(id,?)", Vars: []interface{}{[]int{1, 2, 3}}, WithoutParentheses: true},}).Find(&User{})// SELECT * FROM users ORDER BY FIELD(id,1,2,3)
Limit & Offset
Limit specify the max number of records to retrieveOffset specify the number of records to skip before starting to return the records
db.Limit(3).Find(&users)// SELECT * FROM users LIMIT 3;// Cancel limit condition with -1db.Limit(10).Find(&users1).Limit(-1).Find(&users2)// SELECT * FROM users LIMIT 10; (users1)// SELECT * FROM users; (users2)db.Offset(3).Find(&users)// SELECT * FROM users OFFSET 3;db.Limit(10).Offset(5).Find(&users)// SELECT * FROM users OFFSET 5 LIMIT 10;// Cancel offset condition with -1db.Offset(10).Find(&users1).Offset(-1).Find(&users2)// SELECT * FROM users OFFSET 10; (users1)// SELECT * FROM users; (users2)
Refer to Pagination for details on how to make a paginator
Group By & Having
type result struct {Date time.TimeTotal int}db.Model(&User{}).Select("name, sum(age) as total").Where("name LIKE ?", "group%").Group("name").First(&result)// SELECT name, sum(age) as total FROM `users` WHERE name LIKE "group%" GROUP BY `name` LIMIT 1db.Model(&User{}).Select("name, sum(age) as total").Group("name").Having("name = ?", "group").Find(&result)// SELECT name, sum(age) as total FROM `users` GROUP BY `name` HAVING name = "group"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.TimeTotal 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
Specify Joins conditions
type result struct {Name stringEmail string}db.Model(&User{}).Select("users.name, emails.email").Joins("left join emails on emails.user_id = users.id").Scan(&result{})// SELECT users.name, emails.email FROM `users` left join emails on emails.user_id = users.idrows, 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)// multiple joins with parameterdb.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)// SELECT `users`.`id`,`users`.`name`,`users`.`age`,`Company`.`id` AS `Company__id`,`Company`.`name` AS `Company__name` FROM `users` LEFT JOIN `companies` AS `Company` ON `users`.`company_id` = `Company`.`id`;
Join with conditions
db.Joins("Company", DB.Where(&Company{Alive: true})).Find(&users)// SELECT `users`.`id`,`users`.`name`,`users`.`age`,`Company`.`id` AS `Company__id`,`Company`.`name` AS `Company__name` FROM `users` LEFT JOIN `companies` AS `Company` ON `users`.`company_id` = `Company`.`id` AND `Company`.`alive` = true;
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 intAge int}type Order struct {UserId intFinishedAt *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)// SELECT `order`.`user_id`,`order`.`finished_at` FROM `order` join (SELECT MAX(order.finished_at) as latest FROM `order` left join user user on order.user_id = user.id WHERE user.age > 18 GROUP BY `order`.`user_id`) q on order.finished_at = q.latest
Scan
Scanning results into a struct works similarly to the way we use Find
type Result struct {Name stringAge int}var result Resultdb.Table("users").Select("name", "age").Where("name = ?", "Antonio").Scan(&result)// Raw SQLdb.Raw("SELECT name, age FROM users WHERE name = ?", "Antonio").Scan(&result)