英文:
How to retrive the rows with not empty associations in GORM
问题
作为一个例子,我在数据库中有两个具有一对多关系的表。
考虑下面的代码片段(类型直接来自文档,稍作修改):
package main
import (
"github.com/davecgh/go-spew/spew"
"github.com/gofrs/uuid"
"gorm.io/driver/mysql"
"gorm.io/gorm"
"gorm.io/gorm/clause"
)
type Email struct {
ID uuid.UUID `gorm:"primary_key;type:char(36);"`
Address string `gorm:"unique"`
Uid uuid.UUID
}
type User struct {
ID uuid.UUID `gorm:"primary_key;type:char(36);"`
Name string `gorm:"unique"`
Emails []Email `gorm:"ForeignKey:Uid"`
}
func main() {
var err error
var db *gorm.DB
db, err = gorm.Open(mysql.New(mysql.Config{
DSN: "root:AsdfgH123@tcp(localhost:3306)/maindb?charset=utf8&parseTime=True&loc=Local",
}), &gorm.Config{})
if err != nil {
panic(err)
}
if err != nil {
panic(err)
}
err = db.AutoMigrate(
&User{},
&Email{})
if err != nil {
panic(err)
}
uid1, _ := uuid.NewV7()
uid2, _ := uuid.NewV7()
eid1, _ := uuid.NewV7()
eid2, _ := uuid.NewV7()
users := []User{
{Name: "user_1", ID: uid1, Emails: []Email{{ID: eid1, Address: "user_1@example.com"}}},
{Name: "user_2", ID: uid2, Emails: []Email{{ID: eid2, Address: "user_2@example.com"}}},
}
db.Clauses(clause.Insert{Modifier: "IGNORE"}).Create(&users)
dbUsers := []User{}
db.Preload("Emails", "address like 'user_1@%'").Find(&dbUsers)
spew.Dump(dbUsers)
}
结果如下:
([]main.User) (len=2 cap=20) {
(main.User) {
ID: (uuid.UUID) (len=16 cap=16) 01833180-a4f4-7663-a73e-798b110724ed,
Name: (string) (len=6) "user_1",
Emails: ([]main.Email) (len=1 cap=10) {
(main.Email) {
ID: (uuid.UUID) (len=16 cap=16) 01833180-a4f4-70e6-ad25-c5726212a97c,
Address: (string) (len=18) "user_1@example.com",
Uid: (uuid.UUID) (len=16 cap=16) 01833180-a4f4-7663-a73e-798b110724ed
}
}
},
(main.User) {
ID: (uuid.UUID) (len=16 cap=16) 01833180-a4f4-738f-ad46-1c7bb36cf79c,
Name: (string) (len=6) "user_2",
Emails: ([]main.Email) (cap=10) {
}
}
}
我可以根据 dbUsers
中 user.Emails
不是空列表的事实来过滤结果。但是是否可能纯粹使用 GORM 来实现这一点?让 GORM 仅返回在 Preload
中请求的电子邮件的用户?
英文:
As an example I have two tables in the DB with One2Many relations.
Consider next piece of code (types are straight from the documentation with little changes):
package main
import (
"github.com/davecgh/go-spew/spew"
"github.com/gofrs/uuid"
"gorm.io/driver/mysql"
"gorm.io/gorm"
"gorm.io/gorm/clause"
)
type Email struct {
ID uuid.UUID `gorm:"primary_key;type:char(36);"`
Address string `gorm:"unique"`
Uid uuid.UUID
}
type User struct {
ID uuid.UUID `gorm:"primary_key;type:char(36);"`
Name string `gorm:"unique"`
Emails []Email `gorm:"ForeignKey:Uid"`
}
func main() {
var err error
var db *gorm.DB
db, err = gorm.Open(mysql.New(mysql.Config{
DSN: "root:AsdfgH123@tcp(localhost:3306)/maindb?charset=utf8&parseTime=True&loc=Local",
}), &gorm.Config{})
if err != nil {
panic(err)
}
if err != nil {
panic(err)
}
err = db.AutoMigrate(
&User{},
&Email{})
if err != nil {
panic(err)
}
uid1, _ := uuid.NewV7()
uid2, _ := uuid.NewV7()
eid1, _ := uuid.NewV7()
eid2, _ := uuid.NewV7()
users := []User{
{Name: "user_1", ID: uid1, Emails: []Email{{ID: eid1, Address: "user_1@example.com"}}},
{Name: "user_2", ID: uid2, Emails: []Email{{ID: eid2, Address: "user_2@example.com"}}},
}
db.Clauses(clause.Insert{Modifier: "IGNORE"}).Create(&users)
dbUsers := []User{}
db.Preload("Emails", "address like 'user_1@%'").Find(&dbUsers)
spew.Dump(dbUsers)
}
And as a result I'm getting this:
([]main.User) (len=2 cap=20) {
(main.User) {
ID: (uuid.UUID) (len=16 cap=16) 01833180-a4f4-7663-a73e-798b110724ed,
Name: (string) (len=6) "user_1",
Emails: ([]main.Email) (len=1 cap=10) {
(main.Email) {
ID: (uuid.UUID) (len=16 cap=16) 01833180-a4f4-70e6-ad25-c5726212a97c,
Address: (string) (len=18) "user_1@example.com",
Uid: (uuid.UUID) (len=16 cap=16) 01833180-a4f4-7663-a73e-798b110724ed
}
}
},
(main.User) {
ID: (uuid.UUID) (len=16 cap=16) 01833180-a4f4-738f-ad46-1c7bb36cf79c,
Name: (string) (len=6) "user_2",
Emails: ([]main.Email) (cap=10) {
}
}
}
I can filter the results in dbUsers
based on the fact that user.Emails
is not an empty list. But is it possible to do that purely with GORM? To GORM to only return the users with the email requested in Preload
?
答案1
得分: 0
我认为你可以使用子查询来解决这个问题。抱歉,我无法在MySQL数据库上运行它,所以它没有经过测试,但我猜你能理解:
subQuery := db.Select("uid").Where("address like 'user_1@%'").Table("emails")
db.Where("id IN (?)", subQuery).Find(&dbUsers)
英文:
I think you should be able to solve this using subqueries. Sorry, I cannot run it against a mysql database, so it is untested, but I guess you'll get the gist:
subQuery := db.Select("uid").Where("address like 'user_1@%'").Table("emails")
db.Where("id IN (?)", subQuery).Find(&dbUsers)
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库,让每个人都能够通过互相帮助和分享经验来进步。
评论