Am new in Golang, Please assume I have 2 models like below

type Jobs struct {
    JobID                  uint   `json: "jobId" gorm:"primary_key;auto_increment"`
    SourcePath             string `json: "sourcePath"`
    Priority               int64  `json: "priority"`
    InternalPriority       string `json: "internalPriority"`
    ExecutionEnvironmentID string `json: "executionEnvironmentID"`
}

type ExecutionEnvironment struct {
    ExecutionEnvironmentId string    `json: "executionEnvironmentID" gorm:"primary_key;auto_increment"`
    CloudProviderType      string    `json: "cloudProviderType"`
    InfrastructureType     string    `json: "infrastructureType"`
    CloudRegion            string    `json: "cloudRegion"`
    CreatedAt              time.Time `json: "createdAt"`
}

This is my Database connection & get jobs API code

package main

import (
    "encoding/json"
    "fmt"
    "net/http"
    "strconv"
    "time"

    "github.com/gorilla/mux"
    "github.com/jinzhu/gorm"
    _ "github.com/jinzhu/gorm/dialects/mysql"
)

type Jobs struct {
    JobID                  uint   `json: "jobId" gorm:"primary_key;auto_increment"`
    SourcePath             string `json: "sourcePath"`
    Priority               int64  `json: "priority"`
    InternalPriority       string `json: "internalPriority"`
    ExecutionEnvironmentID string `json: "executionEnvironmentID"`
}

type ExecutionEnvironment struct {
    ExecutionEnvironmentId string    `json: "executionEnvironmentID" gorm:"primary_key;auto_increment"`
    CloudProviderType      string    `json: "cloudProviderType"`
    InfrastructureType     string    `json: "infrastructureType"`
    CloudRegion            string    `json: "cloudRegion"`
    CreatedAt              time.Time `json: "createdAt"`
}

var db *gorm.DB

func initDB() {
    var err error
    dataSourceName := "root:@tcp(localhost:3306)/?parseTime=True"
    db, err = gorm.Open("mysql", dataSourceName)

    if err != nil {
        fmt.Println(err)
        panic("failed to connect database")
    }
    //db.Exec("CREATE DATABASE test")
    db.LogMode(true)
    db.Exec("USE test")
    db.AutoMigrate(&Jobs{}, &ExecutionEnvironment{})
}

func GetAllJobs(w http.ResponseWriter, r *http.Request) {
    w.Header().Set("Content-Type", "application/json")
    fmt.Println("Executing Get All Jobs function")
    // var jobs []Jobs
    // db.Preload("jobs").Find(&jobs)
    // json.NewEncoder(w).Encode(jobs)
    var jobs []Jobs
    if err := db.Table("jobs").Select("*").Joins("JOIN execution_environments on execution_environments.execution_environment_id = jobs.execution_environment_id").Find(&jobs).Error; err != nil {
        fmt.Println(err)
    }
    json.NewEncoder(w).Encode(jobs)
}


func main() {
    // router
    router := mux.NewRouter()
    // Access URL
    router.HandleFunc("/GetAllJobs", GetAllJobs).Methods("GET")
    
    // Initialize db connection
    initDB()

    // config port
    fmt.Printf("Starting server at 8000 \n")
    http.ListenAndServe(":8000", router)
}

In GetAllJobs I try to get jobs table data and execution_environments table data's. But my code not fetch execution_environments table data. Please check sample json below:

[
    {
        "JobID": 4,
        "SourcePath": "test1",
        "Priority": 2,
        "InternalPriority": "test",
        "ExecutionEnvironmentID": "103"
    }
]

jobs table

execution_environments table

Please help me to fetch the execution_environments table data's in my json

CodePudding user response：

Is the relation between the Job and ExecutionEnvironment a belongs_to ?

https://gorm.io/docs/belongs_to.html

if yes, simply add the ExecutionEnvironment to your job struct

type Jobs struct {
    JobID                  uint   `json: "jobId" gorm:"primary_key;auto_increment"`
    SourcePath             string `json: "sourcePath"`
    Priority               int64  `json: "priority"`
    InternalPriority       string `json: "internalPriority"`
    ExecutionEnvironmentID string `json: "executionEnvironmentID"`
    ExecutionEnvironment   ExecutionEnvironment `gorm:"foreignKey:ExecutionEnvironmentID"`
}

and you should be able to get the ExecutionEnvironment data

CodePudding user response：

If the relationship between Jobs and ExecutionEnvironment is one-on-one, your Jobs struct might look like this:

type Jobs struct {
    JobID                  uint   `json: "jobId" gorm:"primary_key;auto_increment"`
    SourcePath             string `json: "sourcePath"`
    Priority               int64  `json: "priority"`
    InternalPriority       string `json: "internalPriority"`
    ExecutionEnvironmentID string `json: "executionEnvironmentID"`
    ExecutionEnvironment   ExecutionEnvironment `gorm:"foreignKey:ExecutionEnvironmentID"`
}

Next, to load the ExecutionEnvironment field inside the Jobs model, you need to use the Preload function.

If you already have a defined relationship (foreign key) between the two tables, you can fetch the data like this:

var jobs []Jobs
if err := db.Preload("ExecutionEnvironment").Find(&jobs).Error; err != nil {
        fmt.Println(err)
}

If not, try including the Joins function:

var jobs []Jobs
if err := db.Preload("ExecutionEnvironment").Joins("JOIN execution_environments ee on ee.execution_environment_id = jobs.execution_environment_id").Find(&jobs).Error; err != nil {
        fmt.Println(err)
}