英文:
How to execute nested query in golang gorm
问题
我想在Golang的gorm中执行以下查询：
select * from MY_TABLE where MY_FIELD in (select max(MY_FIELD) as MY_FIELD from MY_TABLE group by ANOTHER_FIELD)
除了使用原始查询（raw query），gorm还有其他的方法吗？
英文:
I want to execute a query like below in golang gorm:
select * from MY_TABLE where MY_FIELD in (select max(MY_FIELD) as MY_FIELD from MY_TABLE group by ANOTHER_FIELD)
Is there a way besides raw query in gorm?
答案1
得分: 4
你可以通过构建查询来实现这一点。看看这个例子：
func FindProspects(categories *string, cities *string, hasEmail *bool, hasContactperson *bool, limit int64, offset int64) []*gModels.Prospect {
var prospects []*gModels.Prospect
query := env.DB().Table("prospects").Select("prospects.id, prospects.name")
if categories != nil {
query = query.Joins("JOIN prospect_categories ON prospect_categories.prospect_id = prospects.id").Where("prospect_categories.category_name IN (?)", strings.Split(*categories, ","))
}
if cities != nil {
query = query.Where("prospects.city IN (?)", strings.Split(*cities, ","))
}
if hasEmail != nil && *hasEmail == true {
query = query.Where("prospects.id IN (SELECT emails.prospect_id FROM emails WHERE emails.prospect_id = prospects.id)")
}
if hasContactperson != nil && *hasContactperson == true {
query = query.Where("prospects.id IN (SELECT contact_people.prospect_id FROM contact_people WHERE contact_people.prospect_id = prospects.id)")
}
query = query.Order("prospects.name ASC")
query.Limit(int(limit)).Offset(int(offset)).Find(&prospects)
return prospects
}
希望对你有所帮助。
英文:
You can do this by building up your query. Check out this example:
func FindProspects(categories *string, cities *string, hasEmail *bool, hasContactperson *bool, limit int64, offset int64) []*gModels.Prospect {
var prospects []*gModels.Prospect
query := env.DB().Table("prospects").Select("prospects.id, prospects.name")
if categories != nil {
query = query.Joins("JOIN prospect_categories ON prospect_categories.prospect_id = prospects.id").Where("prospect_categories.category_name IN (?)", strings.Split(*categories, ","))
}
if cities != nil {
query = query.Where("prospects.city IN (?)", strings.Split(*cities, ","))
}
if hasEmail != nil && *hasEmail == true {
query = query.Where("prospects.id IN (SELECT emails.prospect_id FROM emails WHERE emails.prospect_id = prospects.id)")
}
if hasContactperson != nil && *hasContactperson == true {
query = query.Where("prospects.id IN (SELECT contact_people.prospect_id FROM contact_people WHERE contact_people.prospect_id = prospects.id)")
}
query = query.Order("prospects.name ASC")
query.Limit(int(limit)).Offset(int(offset)).Find(&prospects)
return prospects
}
Hope this helps.
答案2
得分: 0
你可以构建原始查询：
subQuery := db.Model(Table_Name).Select(field).Group(field).QueryExpr()
err := db.Model(TableName).Scan(outPutStructArray).Where("myfiled IN (?)", subQuery).Error
并适当处理错误。
英文:
you can build raw queries
subQuery := db.Model(Table_Name).Select(field).Group(field).QueryExpr()
err := db.Model(TableName).Scan(outPutStructArray).Where("myfiled IN (?)", subQuery).Error
and handle error appropriately
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论