英文:
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?
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库,让每个人都能够通过互相帮助和分享经验来进步。
评论