Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Storing Sql result sets in a nested struct and omit if empty

Tags:

mysql

go

I am trying to store SQL query dataset in a struct and display in JSON. I have almost done it. Now the problem is if the result set of nested structs is empty then I would not like to display it.

Same problem specified here but using a pointer ends in a panic when scanning may be because I am using &user.Profile.Firstname

2015/11/01 16:42:16 Panic recovery -> runtime error: invalid memory address or nil pointer dereference

If I remove the pointer then everything works fine just the empty field remains. I am confused how to achieve this.

package main

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

type User struct {
    Id       int64
    Username string
    Email    string
    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()

    r := gin.Default()
    v1 := r.Group("api/v1/")
    {
        v1.GET("users", GetUsers)
    }
    r.Run(":8080")
}


func GetUsers(c *gin.Context) {
    stmt, err := DB.Query("Select users.id, username , email , firstname , lastname 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() {
        var user User
        err := stmt.Scan(&user.Id, &user.Username, &user.Email, &user.Profile.Firstname, &user.Profile.Lastname)
        if err != nil {
            panic(err.Error())
        }

        users = append(users, user)
    }

    c.JSON(200, &users)
}

Output:

{
  "Id": 1,
  "Username": "test1",
  "Email": "[email protected]",
  "Profile": {
    "Firstname": "John",
    "Lastname": "Doe"
  }
},
{
  "Id": 2,
  "Username": "test2",
  "Email": "[email protected]",
  "Profile": {
  }
},
like image 335
sh0umik Avatar asked Nov 01 '15 11:11

sh0umik


1 Answers

Assuming gin uses encoding/json under the covers, if you want to omit an empty Profile, I believe you need it to be a pointer in User:

type User struct {
    ....
    Profile *Profile `json:",omitempty"`
}

Note the documentation:

The "omitempty" option specifies that the field should be omitted from the encoding if the field has an empty value, defined as false, 0, a nil pointer, a nil interface value, and any empty array, slice, map, or string.

It doesn't say that the zero value of a struct will be detected.

That will leave you with some issues scanning your data from the db. You could declare a Profile, scan into it, compare to the zero profile and assign it to the User if it has data:

var user User
var profile Profile
_ = stmt.Scan(&user.Id, &user.Username, &user.Email, &profile.Firstname, &profile.Lastname)
if profile != Profile{} {
    user.Profile = &profile
}

I suspect that's not the most elegant solution, but I hope it's the right direction.

like image 86
Jeremy Huiskamp Avatar answered Sep 28 '22 23:09

Jeremy Huiskamp