Golang Gorm Many to Many with Selective Column

Question

I'm trying to fetch books data that have many authors. The relationship type between them is simple Many to Many with a pivot table only consist both ID. This is the models,

package model

import "gorm.io/gorm"

type Author struct {
    gorm.Model
    ID   uint    `gorm:"primaryKey; autoIncrement" json:"id"`
    Name string  `gorm:"Not Null" json:"name"`
    Book []*Book `gorm:"many2many:trx_book_author;" json:"books"`
}

func (author *Author) TableName() string {
    return "tbl_authors"
}

package model

import "gorm.io/gorm"

type Book struct {
    gorm.Model
    ID          uint      `gorm:"primaryKey; autoIncrement" json:"id"`
    Title       string    `gorm:"Not Null" json:"title"`
    Description string    `json:"description"`
    PageNumber  int       `gorm:"Not Null" json:"page_number"`
    Author      []*Author `gorm:"many2many:trx_book_author;" json:"authors"`
}

func (book *Book) TableName() string {
    return "tbl_books"
}

And this is the way I fetch the data,

func (bookRepository *BookRepository) GetAll() []model.Book {
    var books []model.Book

    bookRepository.db.Preload("Author").Find(&books)

    return books
}

Whenever I hit the URL, I got bunch of data that I wanted but I want to select only 'name' column on author. I've tried with this solution but it's not working

func (bookRepository *BookRepository) GetAll() []model.Book {
    var books []model.Book

    bookRepository.db.Preload("Author", func(tx *gorm.DB) *gorm.DB {
        return tx.Select("name")
    }).Find(&books)

    return books
}

any idea ?

EDIT 1: This is the error produced when using tx.Select("name") or ("Name")

failed to assign association &model.Author{Model:gorm.Model{ID:0x0, CreatedAt:time.Date(1, time.January, 1, 0, 0, 0, 0, time.UTC), UpdatedAt:time.Date(1, time.January, 1, 0, 0, 0, 0, time.UTC), DeletedAt:gorm.DeletedAt{Time:time.Date(1, time.January, 1, 0, 0, 0, 0, time.UTC), Valid:false}}, ID:0x0, Name:"dowjqdiq", Book:[]*model.Book(nil)}, make sure foreign fields exists; failed to assign association &model.Author{Model:gorm.Model{ID:0x0, CreatedAt:time.Date(1, time.January, 1, 0, 0, 0, 0, time.UTC), UpdatedAt:time.Date(1, time.January, 1, 0, 0, 0, 0, time.UTC), DeletedAt:gorm.DeletedAt{Time:time.Date(1, time.January, 1, 0, 0, 0, 0, time.UTC), Valid:false}}, ID:0x0, Name:"ddqw", Book:[]*model.Book(nil)}, make sure foreign fields exists; failed to assign association &model.Author{Model:gorm.Model{ID:0x0, CreatedAt:time.Date(1, time.January, 1, 0, 0, 0, 0, time.UTC), UpdatedAt:time.Date(1, time.January, 1, 0, 0, 0, 0, time.UTC), DeletedAt:gorm.DeletedAt{Time:time.Date(1, time.January, 1, 0, 0, 0, 0, time.UTC), Valid:false}}, ID:0x0, Name:"vevebe", Book:[]*model.Book(nil)}, make sure foreign fields exists
[24.020ms] [rows:5] SELECT * FROM `tbl_books` WHERE `tbl_books`.`deleted_at` IS NULL

@EminLaletovic not working, the error is the same as using "name", i'll add the error — Dryluigi, Jan 07 '22 at 17:26
create new struct to hold it, because as long as you use model.Book with preload you still get that info with null value. — Night Owl, Jan 07 '22 at 17:41

score 0 · Answer 1 · answered Jan 17 '22 at 08:47

Select a specific field is possible in Preload using Custom Preloading SQL.

Create a custom struct with specific fields which you want to fetch from DB.

type TempBook struct{
  Field_Name Data_Type
  .
  .
  .

}

db.Model(&Book{}).Preload("Author", func(tx *gorm.DB) *gorm.DB {
return tx.Select("Name")
}).Find(&TempBook{})

If you want to get rid off with error :

In such case for "has_many" association you should specify both Foreign Key and Association Foreign Key.

Intially add the ForeignKey. If it still doesn't work then add the AssociationForeignKey.

Make sure you should specify the ForeignKey inside custom struct like this : gorm:"foreignKey:ID"

For Better understanding refer this link : https://github.com/go-gorm/gorm/issues/4015 and Golang gorm preloading

Query single column is also possible with Pluck() but sure if it supported by Preload or not.

For Pluck you can refer this link : https://gorm.io/docs/advanced_query.html#Pluck

score 0 · Answer 2 · answered Aug 29 '22 at 17:45

The issue is in the relationship you are trying to create. HasMany in GORM will preload the entity in One To Many relationship.

You are looking for Many To Many and a custom preload to fetch the author's name from the third table.

I had this issue and this is how I managed to solve it:

    query.
    Preload("Ingredients", func(db *gorm.DB) *gorm.DB {
        return db.Joins("LEFT JOIN ingredients ON ingredients.id=meal_ingredients.ingredient_id").Select("name, macronutrient, measure, amount, meal_id")
    }).
    Find(&model)

This means preloading the Join table and fetching the Authors name information from another table to preload it in your base model will view the Preload as HasMany and fix the issue for you.

Golang Gorm Many to Many with Selective Column

2 Answers2