Filtering and sorting an SQL query to recreate a nested struct

I think a sort makes a lot of sense, primitive solutions can be the most efficient:

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
}

Though it might make even more sense to create seperate tables for communities, regions, and workers, then query them all with a JOIN: https://www.w3schools.com/sql/sql_join_inner.asp

UPDATE: Since you only want to retrieve one Reliefworker, would something like this work?

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
}

Ok, my crude solution doesn't contain a shred of the intelligence @Absentbird demonstrates but I'm here to learn.

@Absentbird I love your use of maps and multidimensional arrays to hold a matrix of communities and workers. I will focus on making this part of my arsenal over the weekend.

I can accept and adapt @Absentbird's solution once I have a solution to why it gives the error "cannot assign to struct field workers[id].Communities in mapcompilerUnaddressableFieldAssign" for the line workers[id].Communities = append(workers[id].Communities, c)

Firstly apologies as I had to correct two things. Firstly I only needed to return ReliefWorkers (not an array of ReliefWorkers). Secondly ReliefWorker struct did not need to contain the Deployment_id since I already knew it.

I am new to Go so I'd really appreciate feedback on what I can do to better leverage the language and write more concise code.

My structs and solution is currently as follows:

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:%v\n", 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
}