sql - 过滤和排序 SQL 查询以重新创建嵌套结构

Question

我是 Go 新手，我正在尝试从可以作为 JSON 有效负载发送的 SQL 查询中填充一个名为Reliefworker的结构。

本质上，我有一名救济工作者，他可能被分配到许多社区，社区可以由多个地区组成。

我怀疑除了我想要的之外，还有一种聪明的方法可以做到这一点，这是一种原始解决方案，它将向 SQL（由社区）添加一种排序并创建一个函数来检测所添加的社区是否与前一个不同，在这种情况下，我将创建一个要附加的新社区结构类型对象。

type Reliefworker struct {
    Name     string `json:"name"`
    Communities  []Community `json:"community"`
    Deployment_id    string `json:"deployment_id"`
 }

 type Community struct{
    Name     string `json:"name"`
    community_id     string `json:"community_id"`
    Regions []Region `json:"regions"`
}

type Region struct{
    Name     string `json:"name"`
    Region_id     string `json:"region_id"`
    Reconstruction_grant   string `json:"reconstruction_grant"`
    Currency string `json:"currency"`
}

目前，我创建了一个结构，它反映了我在思考下一步行动时从 SQL 中实际得到的东西。也许这可能是一个很好的垫脚石，而不是尝试即时转型？

type ReliefWorker_community_region struct {
    Deployment_id        string
    Community_title      int
    Region_name          string
    Reconstruction_grant int
}

func GetReliefWorkers(deployment_id string) []Reliefworker {

    fmt.Printf("Confirm I have a deployment id:%v\n", deployment_id)

    rows, err := middleware.Db.Query("select deployment_id, community_title, region_name, reconstruction_grant WHERE Deployment_id=$1", brand_id)

    if err != nil {
        return
    }

    for rows.Next() {
        reliefworker := Reliefworker{}
        err = rows.Scan(&deployment_id, &community_title, &region_name, &reconstruction_grant)
        if err != nil {
            return
        }
    }
    rows.Close()

    return
}

标签： sqljsonsortinggostruct

Answer 1

我认为排序很有意义，原始解决方案可能是最有效的：

func GetReliefWorkers(deployment_id string) []Reliefworker {
    // Added sort to query
    q := "select worker_name, community_title, region_name, reconstruction_grant WHERE deployment_id=? ORDER BY community_title"    
    rows, err := middleware.Db.Query(q, deployment_id)
    if err != nil {
        return
    }
    defer rows.Close() // So rows get closed even on an error
    c := Community{} // To keep track of the current community
    cmatrix := [][]string{[]string{}}  // Matrix of communities and workers
    communities := []Community{} // List of communities
    workers := make(map[string]Reliefworker) // Map of workers
    var ccount int // Index of community in lists
    for rows.Next() {
        w := Reliefworker{Deployment_id: deployment_id}
        r := Region{}
        var ctitle string  // For comparison later
        err = rows.Scan(&w.Name, &ctitle, &r.Name, &r.Reconstruction_grant)
        if err != nil {
            return
        }
        if ctitle != c.Name {
            communities = append(communities, c)
            c = Community{}
            c.Name = ctitle
            ccount++
            cmatrix = append(cmatrix, []string{})
        }
        c.Regions = append(c.Regions, r)
        cmatrix[ccount] = append(cmatrix[ccount], w.Name)
        workers[w.Name] = w
    }
    for i, c := range communities {
        for _, id := range cmatrix[i] {
            w := workers[id] // To avoid error 
            w.Communities = append(w.Communities, c)
            workers[id] = w
        }
    }
    out := []Reliefworker{}
    for _, w := range workers {
        out = append(out, w)
    }
    return out
}

尽管为社区、区域和工人创建单独的表可能更有意义，然后使用以下命令查询它们JOIN：https ://www.w3schools.com/sql/sql_join_inner.asp

更新：既然你只想检索一个Reliefworker，这样的东西会起作用吗？

type ReliefWorker struct {
    Name        string      `json:"name"`
    Communities []Community `json:"community"`
}

type Community struct {
    Name    string   `json:"name"`
    Regions []Region `json:"regions"`
}

type Region struct {
    Name                 string `json:"name"`
    Region_id            string `json:"region_id"`
    Reconstruction_grant int    `json:"reconstruction_grant"`
    Currency             string `json:"currency"`
}

func GetReliefWorkers(deployment_id string) Reliefworker {
    reliefworker := Reliefworker{}
    communities := make(map[string]Community)
    rows, err := middleware.Db.Query("select name, community_title, region_name, region_id, reconstruction_grant WHERE Deployment_id=$1", deployment_id)
    if err != nil {
        if err == sql.ErrNoRows {
            fmt.Printf("No records for ReliefWorker:%v\n", deployment_id)
        }
        panic(err)
    }
    defer rows.Close()
    for rows.Next() {
        c := Community{}
        r := Region{}
        err = rows.Scan(&reliefworker.Name, &c.Name, &r.Name, &r.Region_id, &r.Reconstruction_grant)
        if err != nil {
            panic(err)
        }
        if _, ok := communities[c.Name]; ok {
            c = communities[c.Name]
        }
        c.Regions = append(c.Regions, r)
        communities[c.Name] = c
    }
    for _, c := range commmunities {
        reliefworker.Communities = append(reliefworker.Communities, c)
    }
    return reliefworker
}