GORM how to Joins Preloading and user filter as well-CodePudding

I'm new in golang and Gorm
Here is my struct

type SonModel struct {
 ID int64
 Age int
 Name string
 FatherID int64
 Father FaterModel `gorm:"foreignKey:ID;references:FatherID"`
}

type FaterModel struct {
 ID int64
 Name string
 GrandID int64
 Grand GrandModel  `gorm:"foreignKey:ID;references:GrandID"`
}

type GrandModel struct {
 ID int64
 Name string
}

in raw sql what i want is
select son.id,son.name,to_json(father.*) father from son join father on father.id = son.father_id where (son.name like '%name%' or father.name like '%name%') and son.age = 15
i want join and filter with father

in gorm what i'm doing is

db = db.Joins("Father").Preload("Father.Grand")
db = db.Joins("left join father on father.id = son.id left join grand on grand.id = father.grand_id")
db = db.Where("age = ?",15)
db = db.Where("father.name like ? or son.name like ? or grand.name like ?",name)

and i found it left join father and grand twice
first join Father as Father to get father's column
send is my custom left join
how can i Joins("Father") only one time and use its column to filter

CodePudding user response：

Assuming you want to stick with these struct names, there are a couple of things that need to be done.

First, by convention, GORM determines a table name from the struct name. If you want to use different names than that, you need to implement the Tabler interface for each of your models. Something like this:

func (SonModel) Table() string {
   return "son"
}
func (FaterModel) Table() string {
   return "father"
}
func (GrandModel) Table() string {
   return "grand"
}

After this is done, you can write your query like this:

var sons []SonModel

name = fmt.Sprintf("%%%s%%", name) //for example, output in the resulting query should be %John%
err := db.Preload("Father.Grand").
          Joins("left join father on father.id = son.father_id").
          Joins("left join grand on grand.id = father.grand_id").
          Where("sone.age = ?", 15).
          Where("son.name like ? or father.name like ? or grand.name like ?", name, name, name).
          Find(&sons).Error

CodePudding user response：

I try this code

sql := db.ToSQL(func(tx *gorm.DB) *gorm.DB {
        return tx.Model(&SonModel{}).Select("son.id, son.name, father.*").Joins("left join father on father.id = son.id").Where("son.name LIKE ?", "%name%").Where("father.name LIKE ?", "%name%").Where("age = ?", 15).Scan(&SonModel{})
    })
fmt.Println(sql)

And the result

SELECT son.id, son.name, father.* FROM "son_models" left join father on father.id = son.id WHERE son.name LIKE '%name%' AND father.name LIKE '%name%' AND age = 15

Is this solve your problem?