XORM 的复杂操作

package main

import (
	"fmt"
	"log"

	_ "github.com/go-sql-driver/mysql"
	"github.com/go-xorm/xorm"
)

type ModelBase struct {
	Id   int64
	Name string
}

type Person struct {
	Id   int64
	Name string
}

type Department struct {
	Id   int64
	Name string
}

type DepartmentTotalPrice struct {
	Department
}

type DepartmentPerson struct {
	Id         int64
	Department Department `xorm:"department_id bigint"`
	Person     Person     `xorm:"person_id bigint"`
}

type Order struct {
	Id         int64
	Person     Person `xorm:"person_id bigint"`
	Title      string
	TotalPrice float64
}

type OrderDetail struct {
	Id     int64
	Order  Order `xorm:"order_id bigint"`
	Name   string
	Number int64
	Price  float64
}

var x *xorm.Engine

func init() {
	var err error
	x, err = xorm.NewEngine("mysql", "root:12345678@/test?charset=utf8")
	x.ShowSQL = true
	if err != nil {
		log.Fatalf("Fail to create XORM engine : %v", err)
	}
	if err = x.Sync(
		new(Person),
		new(Department),
		new(DepartmentPerson),
		new(Order), new(OrderDetail)); err != nil {
		log.Fatalf("Fail to sync struct to  table schema : %v", err)
	} else {
		fmt.Println("Succ sync struct to table schema")
	}
}

func createPerson() {
	fmt.Println("++++++++++++++++++++ Create Person")
	persons := []Person{
		Person{Name: "lucy"},
		Person{Name: "maven"},
		Person{Name: "tom"},
		Person{Name: "kim"},
	}
	var (
		num int64
		err error
	)
	if num, err = x.Insert(persons); err != nil {
		log.Fatalf("Fail to Insert Persons : %v", err)
	}
	fmt.Printf("Succ to insert person number : %d\n", num)
}

func createDepartment() {
	fmt.Println("++++++++++++++++++++ Create Department")
	departs := []Department{
		Department{Name: "Basic Processing"},
		Department{Name: "Person Management"},
	}
	var (
		num int64
		err error
	)
	if num, err = x.Insert(departs); err != nil {
		log.Fatalf("Fail to Insert Department : %v", err)
	}
	fmt.Printf("Succ to insert Department number : %d\n", num)
}

func getDeparts() (departs []Department, err error) {
	get department from DB
	departs = make([]Department, 0)

	if err = x.Find(&departs); err != nil {
		return nil, err
	}

	fmt.Printf("Succ to get Department number : %d\n", len(departs))
	for i, d := range departs {
		fmt.Printf("DataIndex : %d        DataContent : %#v\n", i, d)
	}
	return departs, nil
}

func getPersons() (persons []Person, err error) {
	get person from DB
	persons = make([]Person, 0)

	if err = x.Find(&persons); err != nil {
		return nil, err
	}

	fmt.Printf("Succ to get Person number : %d\n", len(persons))
	for i, d := range persons {
		fmt.Printf("DataIndex : %d        DataContent : %#v\n", i, d)
	}
	return persons, nil
}

func createDepartmentPersonRelation() {
	fmt.Println("++++++++++++++++++++ Create Department Person Relation")

	var err error
	var departs []Department
	if departs, err = getDeparts(); err != nil {
		log.Fatalf("Fail to Get Department : %v \n", err)
	}
	var persons []Person
	if persons, err = getPersons(); err != nil {
		log.Fatalf("Fail to Get Person : %v \n", err)
	}

	create relation to DB
	relations := []DepartmentPerson{
		DepartmentPerson{Department: departs[0], Person: persons[0]},
		DepartmentPerson{Department: departs[0], Person: persons[1]},
		DepartmentPerson{Department: departs[1], Person: persons[2]},
		DepartmentPerson{Department: departs[1], Person: persons[3]},
	}
	var num int64
	if num, err = x.Insert(relations); err != nil {
		log.Fatalf("Fail to insert Department Person relation : %#v \n", err)
	}
	fmt.Printf("Succ to insert Department Person relation number : %d\n", num)
}

func createOrder() {
	fmt.Println("++++++++++++++++++++ Create Person Order")
	//get persons
	var (
		err     error
		num     int64
		persons []Person
	)

	if persons, err = getPersons(); err != nil {
		log.Fatalf("Fail to Get Person : %v \n", err)
	}

	//create order and details
	for _, p := range persons {

		//create order to DB
		for i := int64(1); i < 3; i++ {
			//using transction
			s := x.NewSession()
			err = s.Begin()
			o := Order{
				Person: p,
				Title:  fmt.Sprintf("order %d for person %s", i, p.Name),
			}

			if num, err = s.Insert(&o); err != nil {
				s.Rollback()
				log.Fatalf("Fail to  %s  save ", o.Title)
			}
			fmt.Printf("Succ to  %s  save . order id is : %d \n", o.Title, o.Id)
			
			details := []OrderDetail{
				OrderDetail{
					Order:  o,
					Name:   fmt.Sprintf("detail %d _ %s", i, o.Title),
					Number: i,
					Price:  11.0,
				},
				OrderDetail{
					Order:  o,
					Name:   fmt.Sprintf("detail %d _ %s", i+1, o.Title),
					Number: i + 1,
					Price:  12.0,
				},
			}

			if num, err = s.InsertMulti(&details); err != nil {
				s.Rollback()
				log.Fatalf("Fail to  detail for  %s  save ", o.Title)
			}

			s.Commit()
			fmt.Printf("Succ to  detail for  %s  save number : %d\n", o.Title, num)
		}
	}
}
func getOneDepartmentPersonRelationByID() {
	var (
		err error
		has bool
	)
	//good run
	relation1 := DepartmentPerson{Id: 5}
	if has, err = x.Get(&relation1); err != nil {
		log.Fatalf("Find Department Person Relation Error : %v", err)
	}
	fmt.Printf("find one : %d    data content : %#v\n", has, relation1)
	//
	//good run
	var relation2 DepartmentPerson
	if has, err = x.Id(5).Get(&relation2); err != nil {
		log.Fatalf("Find Department Person Relation Error : %v", err)
	}
	fmt.Printf("find one : %d    data content : %#v\n", has, relation2)

}
func getBasicProcessingDepartmentPersonMavenOrderDetails() {
	var (
		err     error
		details []OrderDetail
	)
	fmt.Println("++++++++++++++++ get details by order title")
	//get details by order title
	if err = x.Join("INNER", "order", "order.id=order_detail.order_id").
		Where("order.title=?", "order 1 for person maven").
		Find(&details); err != nil {
		log.Fatalf("Find order detail by order title Error : %v", err)
	}
	fmt.Printf("find order detail number : %d\n", len(details))
	for i, d := range details {
		fmt.Printf("index : %d        data content : %#v\n", i, d)
	}
	//
	fmt.Println("++++++++++++++++ get details for person and department condition")
	//get details for person and department condition
	if err = x.Join("INNER", "order", "order.id=order_detail.order_id").
		Join("INNER", "person", "person.id=order.person_id").
		Join("LEFT", "department_person", "department_person.person_id=order.person_id").
		Join("LEFT", "department", "department.id=department_person.department_id").
		Where("department.name=?", "Basic Processing").
		And("person.name=?", "maven").
		Find(&details); err != nil {
		log.Fatalf("Find order detail by order title Error : %v", err)
	}
	fmt.Printf("find order detail number : %d\n", len(details))
	for i, d := range details {
		fmt.Printf("index : %d        data content : %#v\n", i, d)
	}
}

func getOrderTotalPrice() {
	fmt.Println("++++++++++++++++++++ get order total price using Iterate method")
	var (
		err                 error
		orders_UsingIterate []*Order
	)

	if err = x.Iterate(new(Order), func(i int, bean interface{}) error {
		o := bean.(*Order)
		var details []OrderDetail

		if err := x.Where("order_id=?", o.Id).Find(&details); err == nil {
			var totalPrice float64 = 0
			for _, d := range details {
				totalPrice += (float64(d.Number) * d.Price)
			}
			o.TotalPrice = totalPrice
			orders_UsingIterate = append(orders_UsingIterate, o)
		}
		return nil
	}); err != nil {
		log.Fatalf("Find order total price Error using iterate method : %v", err)
	}

	fmt.Printf("order count : %d\n", len(orders_UsingIterate))
	for i, o := range orders_UsingIterate {
		fmt.Printf("index : %d        data content : %#v\n", i, o)
	}
	/
	fmt.Println("++++++++++++++++++++ get order total price using rows method")
	var rows *xorm.Rows
	if rows, err = x.Rows(new(Order)); err != nil {
		log.Fatalf("Find order total price Error using rows method : %v", err)
	}
	defer rows.Close()

	var o Order
	var orders_UsingRows []Order
	for rows.Next() {
		if err = rows.Scan(&o); err != nil {
			fmt.Printf("rows scan in order error : %v\n", err)
		}
		var details []OrderDetail
		if err := x.Where("order_id=?", o.Id).Find(&details); err == nil {
			var totalPrice float64 = 0
			for _, d := range details {
				totalPrice += (float64(d.Number) * d.Price)
			}
			o.TotalPrice = totalPrice
			orders_UsingRows = append(orders_UsingRows, o)
		}
	}
	fmt.Printf("order count : %d\n", len(orders_UsingRows))
	for i, o := range orders_UsingRows {
		fmt.Printf("index : %d        data content : %#v\n", i, o)
	}

}

func main() {
	createPerson()
	createDepartment()
	createDepartmentPersonRelation()
	createOrder()
	getOneDepartmentPersonRelationByID()
	getBasicProcessingDepartmentPersonMavenOrderDetails()
	getOrderTotalPrice()

}