如何在GORM中检索具有非空关联的行

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

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) {
  }
 }
}

我可以根据 dbUsersuser.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)

huangapple
  • 本文由 发表于 2022年9月12日 20:00:01
  • 转载请务必保留本文链接:https://go.coder-hub.com/73688953.html
匿名

发表评论

匿名网友

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

确定