Gorm Using virtual fields on SELECT

Your Question

I have products table which has one-to-many relationship with items and brands tables. brands also have one-to-many relationship with items.

I was trying to query products and group them based on product_id and brand_id and it seems to work fine.

The only problem I have right now is that I can't map these fields brandId and BrandName, They always nil. But querying the raw SQL statement it generates the desired result.

Here are my models. Some fields have been omitted for simplicity.

type Brand struct {
    ID          int        `json:"id" gorm:"primaryKey"`
    Name        string     `json:"name" gorm:"index;not null;type:varchar(50);default:null"`
    ProductID   int        `json:"productId"`
    Product     *Product   `json:"product" gorm:"foreignKey:ProductID;"`
}

type Product struct {
    ID          int         `json:"id" gorm:"primaryKey"`
    Name        string      `json:"name" gorm:"index;not null;type:varchar(50);default:null"`
    StoreID     *int        `json:"storeId"`
    Store       *Store      `json:"store" gorm:"foreignKey:StoreID;constraint:OnUpdate:RESTRICT,OnDelete:RESTRICT;"`
    BrandID     *int        `json:"brandId" gorm:"-"` //SEE THIS. WHEN USE -> it works as expected
    BrandName   *string     `json:"brandName" gorm:"-"` //SEE THIS TOO -> it works as expected
    Brands      []*Brand    `json:"brands" gorm:"constraint:OnUpdate:CASCADE,OnDelete:RESTRICT;"`
}

type Item struct {
    ID            int         `json:"id" gorm:"primaryKey"`
    Quantity      int         `json:"quantity" gorm:"type:integer;not null;unsigned;"`
    ProductID     int         `json:"productId"`
    Product       *Product    `json:"product" gorm:"foreignKey:ProductID;not null;constraint:OnUpdate:RESTRICT,OnDelete:CASCADE;"`
    BrandID       *int        `json:"brandId"`
    Brand         *Brand      `json:"brand" gorm:"foreignKey:BrandID;constraint:OnUpdate:RESTRICT,OnDelete:CASCADE;"`
}



var products []*model.Product
var result *gorm.DB

query := DB.Table("products").
        Where(&model.Product{StoreID: &StoreID}).
        Joins("INNER JOIN items ON items.product_id = products.id").
        Joins("LEFT JOIN brands ON brands.id = items.brand_id").
        Where("items.quantity > 0").
        Group("products.id, brands.id").
        Select("products.*,brands.id AS brand_id, brands.name AS brand_name")

    if err := query.Find(&products).Error; err != nil {
        panic(err)
    }

    //Here results contains no brand_id nor brand_name
    fmt.Printf("result %+v\n", products)

Here is raw SQL generated by gorm. And work as expected outside gorm

SELECT products.*,brands.id AS brand_id, brands.name AS brand_name 
FROM "products" 
INNER JOIN items ON items.product_id = products.id 
LEFT JOIN brands ON brands.id = items.brand_id 
WHERE "products"."store_id" = 2 AND items.quantity > 0 
GROUP BY products.id, brands.id

When using -> instead of - gorm tags everything works as expected. But I don't want to have these fields on database. How can I fix this?

Which work as expected in HeidiSQL(Without GORM)

The document you expected this should be explained

Expected answer

Maping brands.id and brands.name into Product's BrandId and BrandName respectively.

Comment From: a631807682

When using -> instead of - gorm tags everything works as expected. But I don't want to have these fields on database. How can I fix this?

Use tag gorm:"->;-:migration;"