GORM select certain column with preload

Issue

Let’s say I have these struct that represent my models

type QuestionHeader struct {
  QuestionHeaderId int    `gorm:"primaryKey;column:question_id" json:"question_id"`
  LevelId          int    `gorm:"column:level_id" json:"level_id"`
  SubjectId        int    `gorm:"column:subject_id" json:"subject_id"`
  QuestionBody     []QuestionBody
  QuestionSolution []QuestionSolution
  OtherColumn1A    string
  OtherColumn2A    string
}


type QuestionBody struct {
  QuestionBody  int    `gorm:"primaryKey;column:question_id" json:"question_id"`
  Value         string `gorm:"column:value" json:"value"`
  Type          string `gorm:"column:type" json:"type"`
  OtherColumn1B string
  OtherColumn2B string
}

type QuestionSolution struct {
  QuestionSolutionId int    `gorm:"primaryKey;column:question_id" json:"question_id"`
  Value              string `gorm:"column:value" json:"value"`
  Type               string `gorm:"column:type" json:"type"`
  OtherColumn1C      string
  OtherColumn2c      string
}

Then I want to do a query with preload like this

qs.db().
  Preload("QuestionHeader.QuestionBody", func(db *gorm.DB) *gorm.DB {
      return db.Where("question_body.status = ?", 1) 
       // I only want to select certain column here
  }).
  Preload("QuestionHeader.QuestionSolution", func(db *gorm.DB) *gorm.DB {
      return db.Where("question_solution.status = ?", 1) 
       // I only want to select certain column here
  }).
  Where("level_id = ?", 2).
  Find(&questionHeaders)  // I only want to select certain column here

But the catch with that code is, I will select all the columns based on my model property

What if I want to select only a certain column with preload?
For example, I want to select only type for QuestionBody

I know that you can create another struct for your select statement as described in the docs, but are there any other ways? I don’t want to create a struct for every select statement

In Laravel Eloquent, you can do something like this

Solution

There are few issue that i would like to point out

  • You don’t have the many2many association defined in QuestionHeader for QuestionBody or QuestionSolution
  • In query you are using status column but I don’t seem them defined in QuestionBody or QuestionSolution
  • Rename QuestionBody id column to QuestionBodyId in QuestionBody

As per the points changes would be as below:

type QuestionHeader struct {
  QuestionHeaderId int                `gorm:"primaryKey;column:question_id" json:"question_id"`
  LevelId          int                `gorm:"column:level_id" json:"level_id"`
  SubjectId        int                `gorm:"column:subject_id" json:"subject_id"`
  QuestionBody     []QuestionBody     `gorm:"many2many:question_header_question_bodies;"`
  QuestionSolution []QuestionSolution `gorm:"many2many:question_header_question_solutions;"`
  OtherColumn1A    string
  OtherColumn2A    string
}

type QuestionBody struct {
  QuestionBodyId  int   `gorm:"primaryKey;column:question_id" json:"question_id"`
  Value         string  `gorm:"column:value" json:"value"`
  Type          string  `gorm:"column:type" json:"type"`
  Status        uint8   `gorm:"column:status;default:0" json:"status"`
  OtherColumn1B string
  OtherColumn2B string
}

type QuestionSolution struct {
  QuestionSolutionId int    `gorm:"primaryKey;column:question_id" json:"question_id"`
  Value              string `gorm:"column:value" json:"value"`
  Type               string `gorm:"column:type" json:"type"`
  Status             uint8  `gorm:"column:status;default:0" json:"status"`
  OtherColumn1C      string
  OtherColumn2c      string
}
var questionHeaders QuestionHeader
db.
  Preload("QuestionBody", func(db *gorm.DB) *gorm.DB {
    return db.Where("Status = ?", 1).Select("QuestionBodyId", "Value")
  }).
  Preload("QuestionSolution", func(db *gorm.DB) *gorm.DB {
    return db.Where("Status = ?", 1).Select("QuestionSolutionId", "Value")
  }).
  Where("level_id = ?", 2).
  Select("SubjectId").
  Find(&questionHeaders)
}

In Preload Select we would have to include the unique primary key for gorm to uniquely identify the associated slice of structs

Answered By – Chandan

Answer Checked By – Pedro (GoLangFix Volunteer)

Leave a Reply

Your email address will not be published.