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

Question

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

基本上我有一个救援人员，他可以被分配到许多社区，社区可以由多个地区组成。

我怀疑有一种聪明的方法可以做到这一点，而不是我想要的，这是一个原始的解决方案，将添加一个排序到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:%vn", 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
}

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:%vn", 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
}

Answer 2

好吧，我的粗略解决方案不包含@Absentbird所展示的一丁点智能，但我是来学习的。

@Absentbird我喜欢你使用地图和多维数组来保存社区和工人的矩阵。我将在周末集中精力使它成为我武器库的一部分。

我可以接受和适应@Absentbird的解决方案，一旦我有一个解决方案，为什么它给出了错误"不能分配给struct字段工作者[id]。mapcompilerUnaddressableFieldAssign"为workers[id].Communities = append(workers[id].Communities, c)

行首先道歉，因为我必须纠正两件事。首先，我只需要返回ReliefWorkers(不是一个ReliefWorkers数组)。其次，ReliefWorker结构不需要包含Deployment_id，因为我已经知道它了。

我是Go的新手，所以我真的很感谢反馈我能做些什么来更好地利用语言和编写更简洁的代码。

我的结构和解决方案目前如下:

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"`
}
type ReliefWorker_community_region struct {
Name    string
Community_title string
Region_name     string
Reconstruction_grant  int
}
func GetReliefWorkers(deployment_id string) Reliefworker {
var reliefworker Reliefworker
var communitiesOnly []string
var name string
var allReliefWorkerData []ReliefWorker_community_region
rows, err := middleware.Db.Query("select name, community_title, region_name, reconstruction_grant WHERE Deployment_id=$1", deployment_id)
for rows.Next() {
reliefWorker_community_region := ReliefWorker_community_region{}
err = rows.Scan(&reliefWorker_community_region.Name, &reliefWorker_community_region.Community_title, &reliefWorker_community_region.Region_name, &reliefWorker_community_region.Reconstruction_grant)
if err != nil {
panic(err)
}
name = reliefWorker_community_region.Name
allReliefWorkerData = append(allReliefWorkerData, reliefWorker_community_region)
communitiesOnly = append(communitiesOnly, reliefWorker_community_region.Community_title)  //All communities go in here, even duplicates, will will create a unique set later
}
rows.Close()
if err != nil {
if err == sql.ErrNoRows {
fmt.Printf("No records for ReliefWorker:%vn", deployment_id)
}
panic(err)
}
var unique []string  //Use this to create a unique index of communities
for _, v := range communitiesOnly {
skip := false
for _, u := range unique {
if v == u {
skip = true
break
}
}
if !skip {
unique = append(unique, v)
}
}
fmt.Println(unique)
reliefworker.Name = name
var community Community
var communities []Community
for _, v := range unique {
community.Name = v
communities = append(communities, community)
}

// Go through each record from the database held within allReliefWorkerData and grab every region belonging to a Community, when done append it to Communities and finally append that to ReliefWorker
for j, u := range communities {
var regions []Region
for i, v := range allReliefWorkerData {
if v.Community_title == u.Name {
var region Region
region.Name = v.Region_name
region.Reconstruction_grant = v.Reconstruction_grant
regions = append(regions, region)
}
}
communities[j].Regions = regions
}
reliefworker.Communities = communities
return reliefworker
}