如何将m：n关系映射到切片字段？

Question

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?

Answer 1

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.