查询语句

根据ID查询

user := User{}
err := db.Find(&user, id)

查询全部

users := []User{}
err := db.All(&users)
err = db.Where("id in (?)", 1, 2, 3).All(&users)

查询最后创建的

// Last() orders by created_at
user := models.User{}
err := tx.Last(&user)

where查询

users := []models.User{}
query := db.Where("id = 1").Where("name = 'Mark'")
err := query.All(&users)

err = tx.Where("id in (?)", 1, 2, 3).All(&users)

in查询

err = db.Where("id in (?)", 1, 2, 3).All(&users)
err = db.Where("id in (?)", 1, 2, 3).Where("foo = ?", "bar").All(&users)

and查询要分开

// does not work:
err = db.Where("id in (?) and foo = ?", 1, 2, 3, "bar").All(&users)
// works:
err = db.Where("id in (?)", 1, 2, 3).Where("foo = ?", "bar").All(&users)

选择列

err = db.Select("name").All(&users)
// SELECT name FROM users

err = db.Select("max(age)").All(&users)
// SELECT max(age) FROM users

err = db.Select("age", "name").All(&users)
// SELECT age, name FROM users

join查询

// page: page number
// perpage: limit
roles := []models.UserRole{}
query := models.DB.LeftJoin("roles", "roles.id=user_roles.role_id").
  LeftJoin("users u", "u.id=user_roles.user_id").
  Where(`roles.name like ?`, name).Paginate(page, perpage)

count, _ := query.Count(models.UserRole{})
count, _ := query.CountByField(models.UserRole{}, "*")
sql, args := query.ToSQL(&pop.Model{Value: models.UserRole{}}, "user_roles.*",
  "roles.name as role_name", "u.first_name", "u.last_name")
err := models.DB.RawQuery(sql, args...).All(&roles)