How to map m:n relation to slice field?

I have a struct Person:

type Person struct {
    Id     int64
    Name   string
    Colors []string
}

which should get its data from a person table:

id | name
---------
1  | Joe
2  | Moe

and a person_color table:

person_id | color
-----------------
1         | black
1         | blue
2         | green

Via SELECT p.id, p.name, pc.color FROM person AS p INNER JOIN person_color AS pc ON pc.person_id = p.id I merge both tables to:

id | name | color
-----------------
1  | Joe  | black
1  | Joe  | blue
2  | Moe  | green

At the moment the only thing I could think of would be to manually map colors while iterating over rows.Next() (NOTE: just dummy code):

ps := make([]People, 0)

rows, err := db.Query("SELECT ...")

for rows.Next() {
    var p Person

    err := rows.Scan(&p.Id, &p.Name, &p.Color[0])

    exists := false

    for _, ps := range ps {
        if ps.Id == p {
            exists = true

            ps.Color = append(ps.Color, p.Color)
        }
    }

    if !exists {
        ps = append(ps, p)
    }
}

Though this would work this is quite annoying as mapping to a slice field is a common operation.

Is there any way to make the above generic on all slice fields with sql or sqlx?

I'd almost certainly approach this from the SQL side. In PostgreSQL you can use the array_agg to get array types back, which given the proper Scanner implementation should be resistant to weird data values:

SELECT p.id, p.name, pc.color FROM 
       person AS p INNER JOIN 
       array_agg(person_color) AS pc 
    ON
       pc.person_id = p.id
    GROUP BY p.id;

This would return:

 id | name |  array_agg
----+------+--------------
  1 | Joe  | {black,blue}
  2 | Moe  | {green}

It's up to you to create a Go type like type pgarraystring []string and implement Scanner, though it's possible I will add some of these types for PostgreSQL someday soon in the github.com/jmoiron/sqlx/types package.

In MySQL or SQLite, you're going to lack array types, but you can use GROUP_CONCAT[1] to achieve similar results. In other databases, there should be a similar concat aggregate which works with the text representation.

There are a few reasons for going this route. You're using an SQL database for a reason; it should be able to return you the data you want in the desired format; unless it's really going to be a problem and you've measured it, fall back on it, that's its strength as a datastore. It also reduces the amount of data being sent back over the wire and the number of fetches being done by the cursor, so in general it should behave better.

[1] Sorry, I can't post a link to GROUP_CONCAT because I don't have any StackOverflow reputation, but you should be able to google it.

I was expecting the code to look more like this.

// Assuming the context is in a function that can return _error_.
ps := make(map[int64]Person)

rows, err := db.Query("SELECT ...")

for rows.Next() {
    var id int64
    var name string
    var color string
    err := rows.Scan(&id, &name, &color)
    if err != nil {
        return err
    }
    p, ok := ps[id]
    if !ok {
        p.Id = id
        p.Name = name
    }
    p.Colors = append(p.Colors, color)
    ps[id] = p
}

The code you have now is potentially expensive because the original code is iterating across all the people, for each row in the joined table. Rather than do the manual scan, you can you can quickly jump to the right entry by means of the mapping.

Also, the original code fails to save the modified person back into the slice if it already exists. Remember, you're working with Person, not *Person. If you do work with *Person, the code above can be refined to:

// Assuming the context is in a function that can return _error_.
ps := make(map[int64]*Person)

rows, err := db.Query("SELECT ...")

for rows.Next() {
    var id int64
    var name string
    var color string
    err := rows.Scan(&id, &name, &color)
    if err != nil {
        return err
    }
    p, ok := ps[id]
    if !ok {
        p := &Person{id, name, []string{color}}
        ps[id] = p
        continue
    }
    p.Colors = append(p.Colors, color)
}