How to fill a complex structure in Go with a single query from a mysql database?

Question

go

We have 2 tables:
Table #1
Teacher

id   class       teacher_name
1     1a          Мария Ивановна
2     1б          Евгения Васильевна
3     1в          Анастасия Юрьевна

table number 2
Students

id     class     fio                             age
1       1c        Петров Иван                     7
2       1a        Егоров Василий                  7
3       1a        Анастасия Юрьевна               6

We have structures

type Student type{
    Fio      string     `json:"fio" db:"fio"`
    Age      string     `json:"age" db:"age"`
}

type MyClass type{
    Class                    string           `json:"class" db:"class"`
    TeacherName              string           `json:"teacher_name" db:"teacher_name"`
    Students                 []Student      `json:"students" db:"students"`
}

it should turn out something like:

{
  "class":"1a",
  "teacher_name":"Мария Ивановна",
  "students":[
                      {
                        "fio":"Егоров Василий"
                        "age":7
                      },
                      {
                        "fio":"Анастасия Юрьевна"
                        "age":6
                      }
                  ]
}

Answer 1

Something like this:

const query = `
SELECT 
    A.class, A.fio, A.age, B.teacher_name
FROM 
    students A 
  LEFT JOIN teachers B ON A.class = B.class`

func scan(db *sql.DB) (*MyClass, error) {
  rows, err := db.Query(query)
  if err != nil {
    return nil, err
  }
  class := MyClass{}
  for rows.Next() {
    var a, b, c, d string
    if err := rows.Scan(&a, &b, &c, &d); err != nil {
      return nil, err
    }
    class.Class = a
    class.TeacherName = d
    class.Students = append(class.Students, Student{Age: c, Fio: b})
  }
  return &class, nil
}

There may be errors, I didn’t check the code, I put it on my knee, I think the meaning should be clear