using Preload and Join in gorm

Question

let's say I have these 3 struct

type Question struct {
    gorm.Model
    Id            *uint64        `json:"id" gorm:"column=id;primaryKey;autoIncrement"`
    Name          string         `json:"name" gorm:"unique"`
    SkillId       *uint64        `json:"skill_id"`
    TestQuestions []TestQuestion `json:"test_questions"`

}
type Skill struct {
    gorm.Model
    SkillName     string `json:"skill_name"`
    Question []Question
}
type TestQuestion struct {
    gorm.Model
    QuestionId uint64 `json:"question_id"`
    TestId     uint64 `json:"test_id"`
}

I want to select all questions and for each question i want to select the skill name for that question instead of skill id and i want to preload the TestQuestion i tried to make this struct to store my result

type struct QuestionResponse(
    SkillName    string
    Name         string    `json:"name"`
    TestQuestions TestQuestion `json:"test_questions"`
}

i tried this query

db.Table("questions").Preload("TestQuestions").
                Joins("inner join skills on questions.skill_id = skills.id").
        Select("questions.name,skills.skill_name, questions.difficulty, questions.max_points, questions.type, questions.expected_time, questions.question_text,questions.file_read_me").
        Find(&question)

but i get this error "invalid field found for struct github.com...../models.QuestionResponse's field TestQuestions: define a valid foreign key for relations or implement the Valuer/Scanner interface" any solution ?

In the relational database， column can‘t be arrays but in object database（mongo、elasticsaerch）； The table skills should like this ： type Skill struct { gorm.Model SkillName string `json:"skill_name"` QuestionId int // question ID in table questions } — Miffa Young, Aug 03 '21 at 01:02
a question have one skill and a skill is in many quetions that's why he need to define struct in the way he did — loukik98, Aug 03 '21 at 07:26

score 0 · Accepted Answer · answered Aug 03 '21 at 11:48

First i suggest you use the gorm tags in your struct, and second gorm.Model already give us some basic fields, like ID as primary key, etc (https://gorm.io/docs/models.html#gorm-Model) But here is the struct to save some time :

// gorm.Model definition
type Model struct {
  ID        uint           `gorm:"primaryKey"`
  CreatedAt time.Time
  UpdatedAt time.Time
  DeletedAt gorm.DeletedAt `gorm:"index"`
}

I didn't understand what you want to do with your structs, so i recommend you give a look at this: Gorm relationship error: need to define a valid foreign key for relations or it need to implement the Valuer/Scanner interface , and think more about the relations of your database.

Basic: (thanks to https://stackoverflow.com/users/415628/ezequiel-muns )

foreignKey should name the model-local key field that joins to the foreign entity.
references should name the foreign entity's primary or unique key.

Keep in mind that .Preload("some stuff"), will basic do a select (*), to satisfy the Struct of your struct (eg. Orders from Users) in your database, so sometimes is just better to have the field you want in the embed and have a unique struct for that select, than a "fits all" struct. Gorm have some funcs like Omit() to omit a field in a insert or update.

using Preload and Join in gorm

1 Answers1