postgresql - 在 Golang 上使用 LEFT JOIN 并映射到结构

Question

我在 Go 上有以下结构：

type Struct1 struct {
    ID          int64    `db:id`
    InternalID  int64    `db:internal_id`
    Structs2    []Struct2
}

type Struct2 struct {
   ID          int64    `db:id`
   InternalID  int64    `db:internal_id`
   SomeText    string   `db:some_text`
}

这两者的关系是：Struct1只能有一个，通过internal_id连接N个Struct2。所以我正在做这个查询：

SELECT*
FROM struct1 st1
LEFT JOIN struct2 st2 
ON
    st1.internal_id = st2.internal_id 
LIMIT 10
OFFSET (1 - 1) * 10;

通过在 Go 上执行此查询，我想知道我是否可以：通过正确映射数组 Struct2 来创建一个 Struct1 数组。如果有可能，我该怎么做？提前致谢。

我正在使用 Postgres 和 sqlx。

标签： postgresqlgo

Answer 1

这完全取决于您用于连接数据库的库/客户端，但是，我从未见过库支持您尝试执行的操作，因此我将提供您可以执行的自定义实现。完全披露，我没有对此进行测试，但我希望它能给你一个大致的想法，任何人都应该随时添加编辑。

package main

type Struct1 struct {
    ID          int64    `db:id`
    InternalID  int64    `db:internal_id`
    Structs2    []Struct2
}

type Struct2 struct {
    ID          int64    `db:id`
    InternalID  int64    `db:internal_id`
    SomeText    string   `db:some_text`
}

type Row struct {
    Struct1
    Struct2
}

func main() {
    var rows []*Row
    // decode the response into the rows variable using whatever SQL client you use

    // We'll group the struct1s into a map, then iterate over all the rows, taking the
    // struct2 contents off of rows we've already added to the map and then appending
    // the struct2 to them. This effectively turns rows into one-to-many relationships
    // between struct1 and struct2.
    mapped := map[int64]*Struct1{}
    for _, r := range rows {
        // The key of the map is going to be the internal ID of struct1 (that's the ONE
        // in the one-to-many relationship)
        if _, ok := mapped[r.Struct1.InternalID]; ok {
            // Make sure to initialize the key if this is the first row with struct1's
            // internal ID.
            mapped[r.Struct1.InternalID] = &r.Struct1
        }
        // Append the struct 2 (the MANY in the one-to-many relationship) to the struct1s
        // array of struct2s.
        mapped[r.Struct1.InternalID].Structs2 = append(mapped[r.Struct1.InternalID].Structs2, r.Struct2)
    }

    // Then convert it to a slice if needed
    results := make([]*Struct1, len(mapped))
    i := 0
    for _, v := range mapped {
        results[i] = v
        i++
    }
}