如何使用子结构数组将JOIN映射到结构数组

Question

I have two tables in my database, tags and record_tag:

tags
----
id
name

and

record_tag
----------
id
record_id
tag_id
...
tag_owner (user_id)

I have these two structs:

type Tag struct {
    Id          int    `json:"id" db:"id"`
    Tag_owner   string `json:"tag_owner" db:"tag_owner"`
    Tag_id      int    `json:"tag_id" db:"tag_id"`
    Record_id   string `json:"record_id" db:"record_id"`
    Record_type string `json:"record_type" db:"record_type"`
    Record_kind string `json:"record_kind" db:"record_kind"`
    Text        string `json:"text" db:"name"`
    Meta        string `json:"meta" db:"meta"`
}

type TaggedRecord struct {
    Record_id string `json:"record_id" db:"record_id"`
    Tags      []Tag  `json:"tags" db:"tag_id"`
}

And currently, I have this loop that performs n sqlx calls:

taggedRecords := []TaggedRecord{}
for _, record := range body.Records {
    tags := []Tag{}
    _ = db.Select(&tags, "SELECT r.id,r.tag_owner,r.record_id,r.tag_id,r.record_type,r.meta,r.record_kind,t.name FROM record_tag r, tags t WHERE r.tag_owner = $1 AND r.record_id = $2 AND r.tag_id = t.id", body.User, record)
    taggedRecord := TaggedRecord{Record_id: record, Tags: tags}
    taggedRecords = append(taggedRecords, taggedRecord)
}

When I marshall taggedRecords into JSON, I get this desired output:

[
  {
    "record_id": "dfwref3f",
    "tags": []
  },
  {
    "record_id": "3lkgekrljdkjfkjekjkjker",
    "tags": [
      {
        "id": 6,
        "tag_owner": "p0stman",
        "tag_id": 2,
        "record_id": "3lkgekrljdkjfkjekjkjker",
        "record_type": "candidate",
        "record_kind": "candidate",
        "text": "lol",
        "meta": "{\"name\": \"exxon mobile\"}"
      },
      {
        "id": 7,
        "tag_owner": "p0stman",
        "tag_id": 3,
        "record_id": "3lkgekrljdkjfkjekjkjker",
        "record_type": "candidate",
        "record_kind": "candidate",
        "text": "df",
        "meta": "{\"name\": \"exxon mobile\"}"
      }
    ]
  }
]

Obviously, this is dummy data. The above implementation works, but I don't like this it because usually n will be 10, and that's 10 separate network calls to database. I've poked around a little and tried a few things.. I feel like it should be possible to make this a single query. I've tried implementing this solution, but it does not work for me since my root struct embeds an array of child structs, not just a single child struct.

I have tried this:

taggedRecords := []TaggedRecord{}
var s string
for _, record := range body.Records {
    s = s + "r.record_id = '" + record + "' OR "
}
s = s[:len(s)-4]

_ := db.Select(&taggedRecords, "SELECT r.id,r.tag_owner,r.record_id,r.tag_id,r.record_type,r.meta,r.record_kind,t.name,t.id FROM record_tag r, tags t WHERE r.tag_owner = $1 AND "+s+" AND r.tag_id = t.id", body.User)

Select spits out an error saying can't find destination name id, which is I assume because there are two id fields coming at it, it can't map joins onto two structs correctly (the db syntax does not seem to support dots to denote table names or nicknames), or a mix of both.

Is it possible / what is the best way to implement this SQL call and marshal into the above output format while reducing database calls?

Answer 1

You can use the IN expression to select a bunch of tags whole sale then assign them to their respective owners in Go.

  WHERE some_id IN(1,2,3,4)

you can iterate through an array of ids to build it

Or you can avoid ambiguous queries by using SQL aliases

 SELECT t.id , r.id as r_id 

And discriminates these columns in Go , in multiple ways( add a column to a struct, use sqlx struct tags ...)