GORM如何同时使用Joins预加载和用户过滤器?

huangapple go评论83阅读模式
英文:

GORM how to Joins Preloading and user filter as well

问题

我是你的中文翻译助手,以下是翻译好的内容:

我是golang和Gorm的新手,这是我的结构体:

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
}

在原始的SQL中,我想要的是:

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

我想要连接并过滤父亲的信息。

在Gorm中,我正在做的是:

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)

我发现它将父亲和祖父两次左连接了。

第一个连接是为了获取父亲的列。

这是我自定义的左连接。

我想知道如何只连接一次"Father"并使用它的列进行过滤。

英文:

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

答案1

得分: 4

假设您想继续使用这些结构名称,有几件事情需要做。

首先,根据约定,GORM从结构名称确定表名。如果您想使用不同的名称,您需要为每个模型实现Tabler接口。类似这样:

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

完成后,您可以像这样编写查询语句:

var sons []SonModel

name = fmt.Sprintf("%%%s%%", name) //例如,生成的查询中应该是 %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
英文:

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

答案2

得分: 0

我尝试了这段代码:

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)

结果是:

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

这个能解决你的问题吗?

英文:

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?

huangapple
  • 本文由 发表于 2021年12月3日 09:33:54
  • 转载请务必保留本文链接:https://go.coder-hub.com/70208498.html
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定