go get -u github.com/folospace/go-mysql-orm

import (
    "database/sql"
    "github.com/folospace/go-mysql-orm/orm"
)

//connect mysql db
var db, _ = orm.OpenMysql("user:password@tcp(127.0.0.1:3306)/mydb?parseTime=true&charset=utf8mb4&loc=Asia%2FShanghai")

//user table model
var UserTable = orm.NewQuery(User{}, db)

type User struct {
    Id   int    `json:"id"`
    Name string `json:"name"`
}

//Table interface: implements two methods below 
func (User) TableName() string {
    return "user"
}
func (User) DatabaseName() string {
    return "mydb"
}

func main() {
    orm.CreateTableFromStruct(UserTable) //create db table, add new columns if table already exist.
    orm.CreateStructFromTable(UserTable) //create struct fields in code
}        

    //get first user (name='join') as struct
    user, query := UserTable.Where(&UserTable.T.Name, "john").Get()
    
    //get users as slice of struct by primary ids
    users, query = UserTable.Gets(1, 2, 3)
    
    //get users count(*)
    count, query := UserTable.GetCount()
    
    //get user names map key by id
    var userNameKeyById map[int]string
    UserTable.Select(&UserTable.T.Id, &UserTable.T.Name).GetTo(&userNameKeyById)
    
    //get users map key by name
    //useful when find has-many relations
    var usersMapkeyByName map[string][]User
    UserTable.Select(&UserTable.T.Name, UserTable.AllCols()).GetTo(&usersMapkeyByName)
    
    //simplify window function
    //select orders (where user_id=1) with rank by order_amount
    OrderTable.Where(&OrderTable.T.UserId, 1).
    Select(OrderTable.AllCols()).
    SelectRank(&OrderTable.T.OrderAmount, "order_amount_rank").GetRows()
    
    //simplify with recursive cte
    //select recursive to find children ...
    FileFolderTable.Where("id", 1).WithChildrenOnColumn("pid").GetRows()
    //select recursive to find parents ...
    FileFolderTable.Where("id", 9).WithParentsOnColumn("pid").GetRows()

    //update user set name="hello" where id in (1,2,3)
    UserTable.WherePrimary(1,2,3).Update(&UserTable.T.Name, "hello")
    
    //query delete
    UserTable.Delete(1, 2, 3)
    
    //query insert
    _ = UserTable.Insert(User{Name: "han"}).LastInsertId //insert one row and get id

    //insert batch on duplicate key update name=values(name)
    _ = UserTable.InsertsIgnore([]User{{Id: 1, Name: "han"}, {Id: 2, Name: "jen"}},
    []orm.UpdateColumn{{Column: &UserTable.T.Name, Val: &UserTable.T.Name}})
    

    //query join 
    UserTable.Join(OrderTable.T, func (query orm.Query[User]) orm.Query[User] {
            return query.Where(&UserTable.T.Id, &OrderTable.T.UserId)
    }).Where(&OrderTable.T.OrderAmount, 100).Select(UserTable.AllCols()).Gets()

    //transaction
    _ = UserTable.Transaction(func (tx *sql.Tx) error {
        newId := UserTable.UseTx(tx).Insert(User{Name: "john"}).LastInsertId //insert
        fmt.Println(newId)
        return errors.New("I want rollback") //rollback
    })

    //subquery
    subquery := UserTable.Where(&UserTable.T.Id, 1).SubQuery()
    
    //where in suquery
    UserTable.Where(&UserTable.T.Id, orm.WhereIn, subquery).Gets()
    
    //insert subquery
    UserTable.InsertSubquery(subquery, nil)
    
    //join subquery
    UserTable.Join(subquery, func (query orm.Query[User]) orm.Query[User] {
        return query.Where(&UserTable.T.Id, orm.Raw("sub.id"))
    }).Gets()

    users, _ := UserTable.Limit(5).Gets()
    var userIds []int
    for _, v := range users {
        userIds = append(userIds, v.Id)
    }
    
    //each user has many orders
    var userOrders map[int][]Order
    OrderTable.Where(&OrderTable.UserId, orm.WhereIn, userIds).
        Select(&OrderTable.UserId, OrderTable.AllCols()).
        GetTo(&userOrders)
    
    //set user has orders
    for k := range users {
        users[k].Orders = userOrders[users[k].Id]
    }

    type User struct {
        Id int `json:"id"`
        Email string `json:"email" orm:"email,varchar(64),null,unique,index_email_and_score" comment:"user email"`
        Score int `json:"score" orm:"score,index,index_email_and_score" comment:"user score"`
        Name string `json:"name" default:"john" comment:"user name"`
        CreatedAt time.Time `json:"created_at"`
        UpdatedAt time.Time `json:"updated_at"`
    }
//create table IF NOT EXISTS `user` (
//`id` int not null auto_increment,
//`email` varchar(64) null comment 'user email',
//`score` int not null default '0' comment 'user score',
//`name` varchar(255) not null default 'john' comment 'user name',
//`created_at` timestamp not null default CURRENT_TIMESTAMP,
//`updated_at` timestamp not null default CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
//primary key (`id`),
//unique key `email` (`email`),
//key `score` (`score`),
//key `index_email_and_score` (`email`,`score`)
//)