gorm 查询——迹忆客-ag捕鱼王app官网

检索单个对象

gorm 提供了 first、take、last 方法，以便从数据库中检索单个对象。当查询数据库时它添加了 limit 1 条件，且没有找到记录时，它会返回 errrecordnotfound 错误

// 获取第一条记录（主键升序）
db.first(&user)
// select * from users order by id limit 1;
// 获取一条记录，没有指定排序字段
db.take(&user)
// select * from users limit 1;
// 获取最后一条记录（主键降序）
db.last(&user)
// select * from users order by id desc limit 1;
result := db.first(&user)
result.rowsaffected // 返回找到的记录数
result.error        // returns error or nil
// 检查 errrecordnotfound 错误
errors.is(result.error, gorm.errrecordnotfound)

如果你想避免 errrecordnotfound 错误，你可以使用 find，比如 db.limit(1).find(&user)，find 方法可以接受 struct 和 slice 的数据。

first 和 last 会根据主键排序，分别查询第一条和最后一条记录。 只有在目标 struct 是指针或者通过 db.model() 指定 model 时，该方法才有效。 此外，如果相关 model 没有定义主键，那么将按 model 的第一个字段进行排序。 例如:

var user user
var users []user
// works because destination struct is passed in
db.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 work
result := map[string]interface{}{}
db.table("users").first(&result)
// works with take
result := 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 string
  name string
}
db.first(&language{})
// select * from `languages` order by `languages`.`code` limit 1

根据主键检索

如果主键是数字类型，我们可以使用 内联条件 来检索对象。 传入字符串参数时，需要特别注意 sql 注入问题，查看 安全 获取详情.

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);
如果主键是字符串（例如像 uuid），查询将被写成这样：
db.first(&user, "id = ?", "1b74413f-f3b8-409f-ac47-e8c062e3472a")
// select * from users where id = "1b74413f-f3b8-409f-ac47-e8c062e3472a";

当目标对象有一个主要值时，将使用主键构建条件，例如：

var user = user{id: 10}
db.first(&user)
// select * from users where id = 10;
var result user
db.model(user{id: 10}).first(&result)
// select * from users where id = 10;

检索全部对象

// get all records
result := db.find(&users)
// select * from users;
result.rowsaffected // returns found records count, equals `len(users)`
result.error        // returns error

条件

string 条件

// get first matched record
db.where("name = ?", "jinzhu").first(&user)
// select * from users where name = 'jinzhu' order by id limit 1;
// get all matched records
db.where("name <> ?", "jinzhu").find(&users)
// select * from users where name <> 'jinzhu';
// in
db.where("name in ?", []string{"jinzhu", "jinzhu 2"}).find(&users)
// select * from users where name in ('jinzhu','jinzhu 2');
// like
db.where("name like ?", "%jin%").find(&users)
// select * from users where name like '%jin%';
// and
db.where("name = ? and age >= ?", "jinzhu", "22").find(&users)
// select * from users where name = 'jinzhu' and age >= 22;
// time
db.where("updated_at > ?", lastweek).find(&users)
// select * from users where updated_at > '2000-01-01 00:00:00';
// between
db.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';

如果对象设置了主键，条件查询将不会覆盖主键的值，而是用 and 连接条件。 例如：

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 1

这个查询将会给出 record not found 错误 所以，在你想要使用例如 user 这样的变量从数据库中获取新值前，需要将例如 id 这样的主键设置为nil。

struct & map 条件

// struct
db.where(&user{name: "jinzhu", age: 20}).first(&user)
// select * from users where name = "jinzhu" and age = 20 order by id limit 1;
// map
db.where(map[string]interface{}{"name": "jinzhu", "age": 20}).find(&users)
// select * from users where name = "jinzhu" and age = 20;
// slice of primary keys
db.where([]int64{20, 21, 22}).find(&users)
// select * from users where id in (20, 21, 22);

注意 当使用 struct 进行查询时，gorm 只会使用非零值字段进行查询。这意味着如果您的字段值为 0、''、false 或其他 零值，该字段不会被用于构建查询条件，例如：

db.where(&user{name: "jinzhu", age: 0}).find(&users)
// select * from users where name = "jinzhu";

如果想要在查询条件中包含零值，你可以使用 map ，其会包含所有的键值对查询条件，例如：

db.where(map[string]interface{}{"name": "jinzhu", "age": 0}).find(&users)
// select * from users where name = "jinzhu" and age = 0;

了解更多的细节，请阅读 指定结构搜索字段。

指定结构体查询字段

当使用 struct 进行查询时，你可以从 struct 中指定特定的值作为查询条件中相关联的字段名或是 dbname 来传递给 where() ，例如：

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;

内联条件

查询条件可以用和 where 类似的方式被内联到像 first 和 find 这样的方法中

// get by primary key if it were a non-integer type
db.first(&user, "id = ?", "string_primary_key")
// select * from users where id = 'string_primary_key';
// plain sql
db.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;
// struct
db.find(&users, user{age: 20})
// select * from users where age = 20;
// map
db.find(&users, map[string]interface{}{"age": 20})
// select * from users where age = 20;

not 条件

构建 not 条件，用法与 where 类似

db.not("name = ?", "jinzhu").first(&user)
// select * from users where not name = "jinzhu" order by id limit 1;
// not in
db.not(map[string]interface{}{"name": []string{"jinzhu", "jinzhu 2"}}).find(&users)
// select * from users where name not in ("jinzhu", "jinzhu 2");
// struct
db.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 keys
db.not([]int64{1,2,3}).first(&user)
// select * from users where id not in (1,2,3) order by id limit 1;
or 条件
db.where("role = ?", "admin").or("role = ?", "super_admin").find(&users)
// select * from users where role = 'admin' or role = 'super_admin';
// struct
db.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);
// map
db.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);

想要更多复杂的 sql 查询， 请参考 高级查询中的条件组 。

选择特定字段

select 允许我们指定从数据库中取出哪些字段， 此外， gorm 默认选择所有的字段。

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;

我们也可以查看 智能选择字段

排序

从数据库中检索记录时指定顺序

db.order("age desc, name").find(&users)
// select * from users order by age desc, name;
// multiple orders
db.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 指定要检索的最大记录数。 offset 指定在开始返回记录前要跳过的记录数。

db.limit(3).find(&users)
// select * from users limit 3;
// cancel limit condition with -1
db.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 -1
db.offset(10).find(&users1).offset(-1).find(&users2)
// select * from users offset 10; (users1)
// select * from users; (users2)

参考 分页 ，了解如何写一个分页器

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)
// select name, sum(age) as total from `users` where name like "group%" group by `name` limit 1
db.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.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

从 model 中选择去重后的值

db.distinct("name", "age").order("name, age desc").find(&results)

distinct 也可以配合 pluck 和 count 使用

joins

指定连接条件

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{})
// select users.name, emails.email from `users` left join emails on emails.user_id = users.id
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)
// multiple joins with parameter
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 预加载

我们可以使用 joins 实现单条 sql 预加载关联记录，例如：

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`;

带条件的连接

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;

更多细节请参阅 预加载 (eager loading)。

joins 一个衍生表

你也可以使用 joins 来连接一个衍生表

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)
// 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

将结果 scan 至一个 struct 和我们使用 find 的方式相似

type result struct {
  name string
  age  int
}
var result result
db.table("users").select("name", "age").where("name = ?", "antonio").scan(&result)
// raw sql
db.raw("select name, age from users where name = ?", "antonio").scan(&result)