golang create tree from mysql

golang web develop always using tree menu in our project.then that can be Reference resources.this article teach you how to get tree menu in web project.

1.show table of menu in mysql database

```
mysql> desc menu;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int(11)     | YES  |     | NULL    |       |
| name  | varchar(50) | YES  |     | NULL    |       |
| pid   | int(11)     | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
3 rows in set (0.00 sec)

```
mysql> select * from menu;
+------+------+------+
| id   | name | pid  |
+------+------+------+
|    1 | aa   |    0 |
|    2 | bb   |    1 |
|    3 | cc   |    1 |
|    4 | dd   |    2 |
|    5 | ee   |    3 |
|    6 | ee   |    4 |
+------+------+------+
6 rows in set (0.00 sec)
```

2.base operation in golang,that include tree files thats call db.go/menu.go/main.go

db.go

package model

import (
    "database/sql"
    "fmt"
    _ "github.com/go-sql-driver/mysql"
)

var (
    dbURL = "root:root@tcp(127.0.0.1:3306)/test?charset=utf8"
)

func Tx(v_sql string, args ...interface{}) (*sql.Rows, error) {
    db, err := sql.Open("mysql", dbURL)
    defer db.Close()
    stmt, err := db.Prepare(v_sql)
    if err != nil {
        fmt.Println("Tx err:", err)
        return nil, err
    }
    return stmt.Query(args...)
}

menu.go

package model

import (
    "database/sql"
    "fmt"
)

const (
    QuerySQL = "select id,name,pid from menu where pid =?"
)

type Menu struct {
    CurId    int64   `json:"curid"`
    Name     string  `json:"name"`
    ParentId int64   `json:"parentid"`
    Child    []*Menu `json:"child,omitempty"`
}

func MenuAppend(m *Menu) {
    fmt.Println(QuerySQL)
    child, err := MenuInit(QuerySQL, m.CurId)
    if err != nil {
        fmt.Println("MenuAppend err:", err)
        return
    }
    m.Child = child
    for _, v := range m.Child {
        MenuAppend(v)
    }
}
func LoadMenu() (*Menu, error) {
    ms, err := MenuInit(QuerySQL, 0)
    if err != nil {
        fmt.Println("LoadMenu err:", err)
        return nil, err
    }
    root := ms[0]
    MenuAppend(root)
    return root, nil
}
func MenuInit(sql0 string, id int64) ([]*Menu, error) {
    rows, err := Tx(sql0, id)
    if err != nil {
        fmt.Println("MeneInit err:", err)
        return nil, err
    }
    ms := make([]*Menu, 0)
    for rows.Next() {
        m := &Menu{}
        var id sql.NullInt64
        var name sql.NullString
        var pid sql.NullInt64
        if err = rows.Scan(&id, &name, &pid); err != nil {
            fmt.Println("MenuInit error:", err)
            return nil, err
        }
        if id.Valid {
            m.CurId = id.Int64
        }
        if name.Valid {
            m.Name = name.String
        }
        if pid.Valid {
            m.ParentId = pid.Int64
        }
        ms = append(ms, m)
    }
    fmt.Println("current data:", ms)
    return ms, nil
}

main.go

package main

import (
    "encoding/json"
    "fmt"
    _ "github.com/go-sql-driver/mysql"
    "model"
)

func main() {
    root, err := model.LoadMenu()
    if err != nil {
        fmt.Println("err:", err)
    }
    b, err := json.Marshal(root)
    fmt.Println(string(b))
}

3.run and get tree data from json

{
  "curid": 1, 
  "name": "aa", 
  "parentid": 0, 
  "child": [
    {
      "curid": 2, 
      "name": "bb", 
      "parentid": 1, 
      "child": [
        {
          "curid": 4, 
          "name": "dd", 
          "parentid": 2, 
          "child": [
            {
              "curid": 6, 
              "name": "ee", 
              "parentid": 4
            }
          ]
        }
      ]
    }, 
    {
      "curid": 3, 
      "name": "cc", 
      "parentid": 1, 
      "child": [
        {
          "curid": 5, 
          "name": "ee", 
          "parentid": 3
        }
      ]
    }
  ]
}