package main

import (
   "fmt"
   "github.com/jinzhu/gorm"
   _ "github.com/jinzhu/gorm/dialects/mysql"
   "time"
)

type Model struct {
   ID   uint `gorm:"primary_key"`
   CreatedAt time.Time
   UpdatedAt time.Time
   DeletedAt *time.Time
}


type User struct {
gorm.Model
Name string
}

func main() {
   //db, _ := gorm.Open("mysql", "root:123456@gorm?192.168.73.103:3306/gormtest?charset=utf8")
   db, _ := gorm.Open("mysql", "root:123456@tcp(192.168.73.103:3306)/gormtest?charset=utf8mb4&parseTime=True&loc=Local")

   defer db.Close()

   //创建表名users
   db.CreateTable(&User{})

   //更新表，有则更新，无则创建
   db.AutoMigrate(&User{})

   //插入更新数据，有则更新，无则插入
   user := User{Name: "aa"}
   db.Save(&user)

   //插入数据，有则报错
   user := User{Name: "bb"}
   db.Create(&user)
   println(user.ID)

   //NewRecord方法用于判断某个对象是否可以作为新纪录插入
   if(db.NewRecord(&user)){
      db.Create(&user)
   }

   //更改数据
   db.Model(&user).Updates(map[string]interface{}{"name": "hello", "age": 18, "actived": false})

   // 使用组合条件批量更新单个属性
   db.Model(&user).Where("name= ?", "aa").Update("name", "hello")
   //UPDATE users SET name='hello', updated_at='2013-11-17 21:34:10' WHERE id=111 AND name='aa';

   // 使用`struct`更新多个属性，只会更新这些更改的和非空白字段
   db.Model(&user).Updates(User{Name: "hello", Age: 18})
   //UPDATE users SET name='hello', age=18, updated_at = '2013-11-17 21:34:10' WHERE id = 111;

   // 对于下面的更新，什么都不会更新为""，0，false是其类型的空白值
   db.Model(&user).Updates(User{Name: "", Age: 0, Actived: false})

   //删除数据
   db.Delete(&user)
   //UPDATE users SET deleted_at="2013-10-29 10:23" WHERE id = 111;

   // 批量删除
   db.Where("name = ?", "aa").Delete(&User{})
   //UPDATE users SET deleted_at="2013-10-29 10:23" WHERE name = 'aa';

   // 软删除的记录将在查询时被忽略
   db.Where("name = 'aa'").Find(&user)
   //SELECT * FROM users WHERE name = 'aa' AND deleted_at IS NULL;

   // 使用Unscoped查找软删除的记录
   db.Unscoped().Where("name = 'aa'").Find(&users)
   //SELECT * FROM users WHERE name = 'aa';

   // 使用Unscoped永久删除记录
   db.Unscoped().Delete(&order)
   //DELETE FROM orders WHERE id=10;

   // 获取第一条记录，按主键排序
   //First 方法，将查询结果的第一条记录回显到传入形参的结构体对象
   db.First(&user)
   //SELECT * FROM users ORDER BY id LIMIT 1;

   // 获取最后一条记录，按主键排序
   //Last 方法，将查询结果的最后一条记录回显到传入形参的结构体对象
   db.Last(&user)
   //SELECT * FROM users ORDER BY id DESC LIMIT 1;

   // 获取所有记录
   //Find 方法，将全部查询结果加入传入的形参slice
   db.Find(&users)
   //SELECT * FROM users;

   // 按主键获取
   db.First(&user, 23)
   //SELECT * FROM users WHERE id = 23 LIMIT 1;

   // 简单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;

   //Modal方法，在单表查询中，仅为了设定当前查询的表，传入的结构体对象仅用于设定查询表
   db.Modal(&User{}).Find(&users)

   // 获取第一个匹配记录
   db.Where("name = ?", "jinzhu").First(&user)
   //SELECT * FROM users WHERE name = 'jinzhu' limit 1;

   // 获取所有匹配记录
   db.Where("name = ?", "jinzhu").Find(&users)
   //SELECT * FROM users WHERE name = 'jinzhu';

   //获取不是 jinzhu 的所有记录
   db.Where("name <> ?", "jinzhu").Find(&users)
   //select * from users where name <> 'jinzhu';

   // IN
   //查找 name 在 jinzhu,jinzhu 2 里面的数据
   db.Where("name in (?)", []string{"jinzhu", "jinzhu 2"}).Find(&users)
   // select * from users where name in ("jinzhu", "jinzhu 2")

   // LIKE
   //查找 name 匹配到 jin 的所有数据
   db.Where("name LIKE ?", "%jin%").Find(&users)
   //select * from users where name like "%jin%";

   // AND
   //联合查询，查找 name='jinzhu' 且 age>='22' 的所有数据
   db.Where("name = ? AND age >= ?", "jinzhu", "22").Find(&users)
   //select * from users where name='jinzhu' and age>='22';

   // Time
   //查找 updated_at 时间在一周以前的所有数据
   db.Where("updated_at > ?", lastWeek).Find(&users)
   //select * from users where datediff(week,updaed_at,getdate())>0;

   //查找最近一周的数据
   db.Where("created_at BETWEEN ? AND ?", lastWeek, today).Find(&users)
   //select * from info users where datediff(week,created_at,getdate())=0

   // Struct
   //使用struct查询时，GORM将只查询那些具有值的字段
   db.Where(&User{Name: "jinzhu", Age: 20}).First(&user)
   //SELECT * FROM users WHERE name = "jinzhu" AND age = 20 LIMIT 1;

   // Map
   db.Where(map[string]interface{}{"name": "jinzhu", "age": 20}).Find(&users)
   //SELECT * FROM users WHERE name = "jinzhu" AND age = 20;

   // 主键的Slice
   db.Where([]int64{20, 21, 22}).Find(&users)
   //SELECT * FROM users WHERE id IN (20, 21, 22);



   //Not查询
   //通过Not方法构建取非的查询
   db.Not("name", "jinzhu").First(&user)
   //SELECT * FROM users WHERE name <> "jinzhu" LIMIT 1;

   // Not In
   db.Not("name", []string{"jinzhu", "jinzhu 2"}).Find(&users)
   //SELECT * FROM users WHERE name NOT IN ("jinzhu", "jinzhu 2");

   // Not In slice of primary keys
   db.Not([]int64{1,2,3}).First(&user)
   //SELECT * FROM users WHERE id NOT IN (1,2,3) limit 1;

   db.Not([]int64{}).First(&user)
   //SELECT * FROM users limit 1;

   // Plain SQL
   db.Not("name = ?", "jinzhu").First(&user)
   //SELECT * FROM users WHERE NOT(name = "jinzhu") limit 1;

   // Struct
   db.Not(User{Name: "jinzhu"}).First(&user)
   //SELECT * FROM users WHERE name <> "jinzhu" limit 1;


   //Or查询
   //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"}).Find(&users)
   //SELECT * FROM users WHERE name = 'jinzhu' OR name = 'jinzhu 2';

   // Map
   db.Where("name = 'jinzhu'").Or(map[string]interface{}{"name": "jinzhu 2"}).Find(&users)
   //SELECT * FROM users WHERE name = 'jinzhu' OR name = 'jinzhu 2';

   //查询链
   //多个查询条件可以直接拼接构建复合条件
   db.Where("name <> ?","jinzhu").Where("age >= ? and role <> ?",20,"admin").Find(&users)
   //SELECT * FROM users WHERE name <> 'jinzhu' AND age >= 20 AND role <> 'admin';

   db.Where("role = ?", "admin").Or("role = ?", "super_admin").Not("name = ?", "jinzhu").Find(&users)
   //select * from users where role="admin" or role="super_admin" and name<>"jinzhu"

   //Select字段
   //通过Select方法进行部分字段的查询
   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;

   //Order排序
   //通过Order方法对返回结果进行排序
   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;

   // ReOrder
   db.Order("age desc").Find(&users1).Order("age", true).Find(&users2)
   //SELECT * FROM users ORDER BY age desc; (users1)
   //SELECT * FROM users ORDER BY age; (users2)

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

   //Offset
   db.Offset(3).Find(&users)
   //SELECT * FROM users OFFSET 3;

   // Cancel offset condition with -1
   db.Offset(10).Find(&users1).Offset(-1).Find(&users2)
   //SELECT * FROM users OFFSET 10; (users1)
   //SELECT * FROM users; (users2)

   //Count
   //count方法返回结果条数
   db.Model(&User{}).Where("name = ?", "jinzhu").Count(&count)
   //SELECT count(*) FROM users WHERE name = 'jinzhu'; (count)

   db.Table("deleted_users").Count(&count)
   //SELECT count(*) FROM deleted_users;

   //Join
   //通过Join方法进行多表查询
   db.Table("users").Select("users.name, emails.email").Joins("left join emails on emails.user_id = users.id")



   //因为多表查询结果与实体类不对应，所以数据回显要通过Scan方法回显到任意定义结构体
   //Scan
   //Scan方法将结果扫描到另一个结构中。比如
   type User struct{}
   type Email struct{}
   type result struct{
      User
      Email
   }

   user := User{}
   db.Modal(&User{}).Where("1 = 1").Scan(&user)

   res := make([]Result,1)
   db.Table("users").Select("users.name, emails.email").Joins("left join emails on emails.user_id = users.id").Scan(&res)


   //Scopes
   //通过Scopes可以将Where语句封装为方法来使用，动态添加参数
   func OrderStatus(status []string) func (db *gorm.DB) *gorm.DB {
      return func (db *gorm.DB) *gorm.DB {
         return db.Scopes(AmountGreaterThan1000).Where("status in (?)", status)
      }
   }
   db.Scopes(OrderStatus([]string{"paid", "shipped"})).Find(&orders)
   // 查找所有付费，发货订单

   //关联结构
   //一对一
   //默认使用主键作为外键，外键默认命名为 (关联结构体类型名称+关联结构体主键属性名称)
   // `User`属于`Profile`, `ProfileID`为外键
   type User struct {
      gorm.Model
      Profile   Profile
      ProfileID int
   }

   type Profile struct {
      gorm.Model
      Name string
   }

   db.Model(&user).Related(&profile)
   //SELECT * FROM profiles WHERE id = 111; // 111是user的外键ProfileID

   //通过配置ForeignKey指定该关联属性对应在本结构体的外键
   //通过配置AssociationForeignKey指定该关联属性在其关联结构体的外键属性
   type Profile struct {
      gorm.Model
      Refer string
      Name  string
   }

   type User struct {
      gorm.Model
      Profile   Profile `gorm:"ForeignKey:ProfileID;AssociationForeignKey:Refer"`
      ProfileID int
   }

   //一对多
   //与一对一写法类似
   // User 包含多个 emails, UserID 为外键
   type User struct {
      gorm.Model
      Emails   []Email
   }

   type Email struct {
      gorm.Model
      Email   string
      UserID  uint
   }

   db.Model(&user).Related(&emails)
   //SELECT * FROM emails WHERE user_id = 111; // 111 是 user 的主键

   //多对多关系必须由中间表维护，通过many2many属性配置中间表名称
   type CustomizePerson struct {
      IdPerson string             `gorm:"primary_key:true"`
      Accounts []CustomizeAccount `gorm:"many2many:PersonAccount;ForeignKey:IdPerson;AssociationForeignKey:IdAccount"`
   }

   type CustomizeAccount struct {
      IdAccount string `gorm:"primary_key:true"`
      Name      string
   }


   //多态关联
   //比较特殊的还支持多个结构与某一个结构的同一属性进行关联
   //多态属性和多对多显式不支持，并且会抛出错误。
   type Cat struct {
      Id    int
      Name  string
      Toy   Toy `gorm:"polymorphic:Owner;"`
   }

   type Dog struct {
      Id   int
      Name string
      Toy  Toy `gorm:"polymorphic:Owner;"`
   }

   type Toy struct {
      Id        int
      Name      string
      OwnerId   int
      OwnerType string
   }

   //关联更新
   //当保存的实体类包含关联对象时，则会save该关联对象，比如下面代码，从数据库查出user对象，在保存car时，因为car的关联对象owners有值，关联关系和对应的user对象都被修改，名称更新为yyt
   type User struct {
      gorm.Model
      Name string
      A    int
      Cars []Car `gorm:"many2many:car_user;ForeignKey:ID;AssociationForeignKey:ID"`
   }
   type Car struct {
      gorm.Model
      Num    string
      Owners []User `gorm:"many2many:car_user;ForeignKey:ID;AssociationForeignKey:ID"`
   }

   test := User{}
   db.Model(&User{}).Where("1 = 1").First(&test)
   println(test.ID)
   test.Name = "yyt"
   db.Save(&Car{
      Num:    "sssfs",
      Owners: []User{test},
   })


   //如果不想开启该关联更新有两个方式，如下是实时设定当前操作不进行关联更新的
   db.Set("gorm:save_associations", false).Create(&user)

   //另一种方式是在定义结构体的tag里设定save_associations:false来指定不进行关联更新
   type User struct {
      gorm.Model
      Name      string
      CompanyID uint
      Company   Company `gorm:"save_associations:false"`
   }

   type Company struct {
      gorm.Model
      Name string
   }


   //关联查询
   //在关联查询中，db.Modal接收的参数不再只是为了确定表，其必须是一个设定了主键的对象，否则会报错
   //关联查询查询该主键关联的其他表的数据

   //Relative
   //Relative提供关联关系的查询功能
   // User 包含多个 emails, UserID 为外键
   type User struct {
      gorm.Model
      Emails   []Email
      Car Car
   }

   type Email struct {
      gorm.Model
      Email   string
      UserID  uint
   }

   type Car struct {
      gorm.Model
      UserID  uint
   }

   user : = User{}
   user.ID = 111
   emails := make([]Email,1)
   db.Model(&user).Related(&emails)
   //SELECT * FROM emails WHERE user_id = 111; // 111 是 user 的主键
   car := Car{}
   db.Model(&user).Related(&car)
   //SELECT * FROM cars WHERE user_id = 111; // 111 是 user 的主键


   //Association
   //Association提供关联关系的各种查询、维护功能
   // 开始关联模式
   var user User
   user.ID = 1
   db.Model(&user).Association("Languages")
   // `user`是源，它需要是一个有效的记录（包含主键）
   // `Languages`是关系中源的字段名。
   // 如果这些条件不匹配，将返回一个错误，检查它：
   // db.Model(&user).Association("Languages").Error


   // Query - 查找所有相关关联
   db.Model(&user).Association("Languages").Find(&languages)


   // Append - 添加新的many2many, has_many关联, 会替换掉当前 has_one, belongs_to关联
   db.Model(&user).Association("Languages").Append([]Language{languageZH, languageEN})
   db.Model(&user).Association("Languages").Append(Language{Name: "DE"})


   // Delete - 删除源和传递的参数之间的关系，不会删除这些参数
   db.Model(&user).Association("Languages").Delete([]Language{languageZH, languageEN})
   db.Model(&user).Association("Languages").Delete(languageZH, languageEN)


   // Replace - 使用新的关联替换当前关联
   db.Model(&user).Association("Languages").Replace([]Language{languageZH, languageEN})
   db.Model(&user).Association("Languages").Replace(Language{Name: "DE"}, languageEN)


   // Count - 返回当前关联的计数
   db.Model(&user).Association("Languages").Count()


   // Clear - 删除源和当前关联之间的关系，不会删除这些关联
   db.Model(&user).Association("Languages").Clear()


}