Scan DB results into nested struct arrays

Question

I am looking to scan results from a DB connection into a struct array which also includes a nested struct array. However, when the results are scanned and the values in the slice of the nested array are all zero...the slice does not omit empty. Let's say the slice in the second position of the array is full of zero values. I still have an output of {} for that slice. I have tried creating an empty slice of the top level struct Users but then get an error thrown: index[0] out of range and I find myself going in loops. Not seeing anything related to this specific question, i have seen questions regarding nested stucts, but not nested struct arrays. any help to get the slice to omitempty??

And yes I do have omitempty in my struct tags.

package main

import (
    "database/sql"
    _ "github.com/go-sql-driver/mysql"
    "log"
)

type User struct {
    Id       int64 `json:",omitempty"`
    Username string `json:",omitempty"`
    Email    string `json:",omitempty"`
    Profile  []*Profile `json:",omitempty"`
}

type Profile struct {
    Id        int64  `json:",omitempty"`
    UserId    int64  `json:",omitempty"`
    Firstname *string `json:",omitempty"`
    Lastname  *string `json:",omitempty"`
}

var DB *sql.DB

func checkErr(err error, msg string) {
    if err != nil {
        log.Fatal(msg, err)
    }
}

func main() {
    DB, _ = sql.Open("mysql", "username:secrect@/database")
    defer DB.Close()

}


func GetUsers() {
    stmt, err := DB.Query("Select users.id, username , email , firstname AS firstName1 , lastname AS lastName1, firstname AS firstName2 , lastname AS lastName2 from users left join profiles on users.id = profiles.user_id ")
    if err != nil {
        panic(err.Error())
    }
    defer stmt.Close()

    users := []User{}

    for stmt.Next() {
        user := User{Profile: Profile{{}}}
        err := stmt.Scan(&user.Id, &user.Username, &user.Email, &user.Profile[0].Firstname, &user.Profile[0].Lastname, &user.Profile[1].Firstname, &user.Profile[1].Lastname)
        if err != nil {
            panic(err.Error())
        }

        users = append(users, user)
    }
}

json tags have no affect with MySQL. They're for JSON.

Jonathan Hall
– Jonathan Hall

2020-06-10 18:18:57 +00:00
Commented Jun 10, 2020 at 18:18 — Jonathan Hall
– Jonathan Hall, Commented Jun 10, 2020 at 18:18

Burak Serdar · Accepted Answer · 2020-06-10 15:24:50Z

You are trying to load the resultset of a SQL query, which is row-based, into a multi-level data structure, which is not row-based. You can either process the result set of the join and fill out the structure, or perform two queries, one to fetch the User and another to fetch the Profiles associated with it.

Your result set will contain multiple rows for each user, so you have to process the results and construct the structs based on the results you fetch. Something like this should work:

for stmt.Next() {
      var userId, userName, userEmail, firstName, lastName string
         err := stmt.Scan(&userId, &userName, &userEmail, &firstName,&lastName)
        if err != nil {
            panic(err.Error())
        }
        user:=findUser(users,userId)
        if user==nil {
           // Add new user
           user:=&User{Id:userId,Name:userName,Email:userEmail}
           // Add user to users
        }
        user.Profiles=append(user.Profiles,&Profile{/* Fill profile fields here*/}))

    }

The json tags you mention will only work for json marshaling/unmarshaling. They have nothing to do with DB operations.

Collectives™ on Stack Overflow

Scan DB results into nested struct arrays

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related