Gorm Query Customized Join Extra Column

Question

I am trying to get extra columns from a many2many relationships on Gorm. Example

1. Part

type Part struct {
    Id unit
    Name string
 }

2. Diagram

type Diagram struct {
    Id unit
    Name string
    Parts []Part `gorm:"many2many:diagram_parts;"`
 }

3. DiagramPart

type DiagramPart struct{
    DiagramId         uint `gorm:"primaryKey"`
    PartId            uint `gorm:"primaryKey"`
    PartDiagramNumber int
    PartNumber        string
    PartDescription   string
}

This is what I have done trying to retrieve PartNumber and PartDescription in Parts.

diagram := &Diagram{}
db := s.db.Where("id = ?", 1).
        Preload("Parts", func(db *gorm.DB) *gorm.DB {
            return db.Select("parts.*, diagram_parts.part_number, diagram_parts.part_description").
                Joins("left join diagram_parts on diagram_parts.part_id = parts.id")
        }).
        First(diagram)

Unfortunately, I am not able to retrieve part_number, part_description. How should I go about it?

Answer 1

You can add field PartNumber and PartDescription on struct Part OR Diagram, then add tag gorm:"-:migration;->" on than fields to ignore migration and to readonly mode. But on your situation, you can add it in struct Part because you already preload it.

source: https://gorm.io/docs/models.html#Field-Level-Permission

here's the example:

package main

import (
    "fmt"

    "gorm.io/driver/sqlite"
    "gorm.io/gorm"
)

type Part struct {
    Id              uint `gorm:"primaryKey"`
    Name            string
    PartNumber      string `gorm:"-:migration;->"`
    PartDescription string `gorm:"-:migration;->"`
}

type Diagram struct {
    Id              uint `gorm:"primaryKey"`
    Name            string
    Parts           []Part `gorm:"many2many:diagram_parts;"`
    // PartNumber      string `gorm:"-:migration;->"`
    // PartDescription string `gorm:"-:migration;->"`
}

type DiagramPart struct {
    DiagramId         uint `gorm:"primaryKey"`
    PartId            uint `gorm:"primaryKey"`
    PartDiagramNumber int
    PartNumber        string
    PartDescription   string
}

func main() {
    db, err := gorm.Open(sqlite.Open("test.db"), &gorm.Config{})
    if err != nil {
        panic("failed to connect database")
    }
    db.AutoMigrate(&Diagram{}, &Part{}, &DiagramPart{})
    diagram := &Diagram{}
    err = db.Debug().Where("id = ?", 1).
        // Select("diagrams.*, diagram_parts.part_number, diagram_parts.part_description").
        Preload("Parts", func(db *gorm.DB) *gorm.DB {
            return db.Select("parts.*, diagram_parts.part_number, diagram_parts.part_description").
                Joins("left join diagram_parts on diagram_parts.part_id = parts.id")
        }).
        // Joins("left join diagram_parts on diagram_parts.diagram_id = diagrams.id").
        First(diagram).Error
    if err != nil {
        panic(err)
    }

    fmt.Printf("diagram: %v\n", diagram)
    fmt.Println("part number:", diagram.Parts[0].PartNumber)
}