如何使用GORM执行连接操作并迭代结果的行?

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

How do I Perform Join With GORM and iterate over rows of result?

问题

使用gorm,我为usersproduct_prices表创建了以下模型

// `users`表的模型
type User struct {
	Id            uint64 `json:"id" gorm:"primaryKey"`
	Email         string `json:"email" gorm:"unique"`
	Password      []byte `json:"-"`
	CreatedOn     time.Time `json:"created_on" gorm:"<-:create"`
	UpdatedOn     time.Time `json:"updated_on"`
}

// `product_prices`表的模型
type ProductPrice struct {
	Id           uint64 `json:"id" gorm:"primaryKey"`
	Price        float64 `json:"price"`
	Direction    string  `json:"direction"`
	NotificationMethod string  `json:"notification_method"`
	CreatedOn    time.Time  `json:"created_on,omitempty" gorm:"<-:create"`
	UpdatedOn    time.Time  `json:"updated_on,omitempty"`
	LastNotified time.Time  `json:"last_notified,omitempty"`
	UserId       uint64  `json:"user_id"`
	User         User    `json:"-" gorm:"foreignKey:UserId"`
}

我想要执行如下所示的product_pricesusers表之间的SQL连接操作

select product_prices.id, product_prices.price, product_prices.created_on, users.email as user_email, users.created_on as user_created_on from product_prices join users on product_prices.user_id=users.id;

这是我尝试过的其中一种方法,但是由于文档页面https://gorm.io/docs/query.html#Joins 上的缺乏清晰度,我仍然没有成功。我还想遍历行。

根据这里的文档https://gorm.io/docs/query.html#Joins,我尝试了以下代码

// 用于连接查询结果的结构体
type ProductPriceUser struct {
	Id                     uint64 `json:"id"`
	Price                  float64 `json:"price"`
	CreatedOn              time.Time  `json:"created_on,omitempty"`
	UserEmail              User `json:"user_email"`
	UserCreatedOn          User `json:"user_created_on"`
}

var productPrice ProductPrice
var productPriceUser []ProductPriceUser

database.DB.Model(&productPrice).Select("product_prices.id, product_prices.price, product_prices.created_on, users.email as user_email, users.created_on as user_created_on").Joins("join users on product_prices.user_id = users.id").Scan(&productPriceUser)

for _, row := range productPriceUser {
	fmt.Println("values:", row.Id, row.Price, row.CreatedOn, row.UserEmail, row.UserCreatedOn, "\n")
}

但是我遇到了各种错误,我做错了什么?如何按照上述描述获得我想要的结果?

谢谢!

英文:

Using gorm, i created the following models for users and product_prices tables

// model for table `users`
type User struct {
	Id            uint64 `json:&quot;id&quot; gorm:&quot;primaryKey&quot;`
	Email         string `json:&quot;email&quot; gorm:&quot;unique&quot;`
	Password      []byte `json:&quot;-&quot;`
	CreatedOn     time.Time `json:&quot;created_on&quot; gorm:&quot;&lt;-:create&quot;`
	UpdatedOn     time.Time `json:&quot;updated_on&quot;`
}

// model for table `product_prices`
type ProductPrice struct {
	Id           uint64 `json:&quot;id&quot; gorm:&quot;primaryKey&quot;`
	Price        float64 `json:&quot;price&quot;`
	Direction    string  `json:&quot;direction&quot;`
	NotificationMethod string  `json:&quot;notification_method&quot;`
	CreatedOn    time.Time  `json:&quot;created_on,omitempty&quot; gorm:&quot;&lt;-:create&quot;`
	UpdatedOn    time.Time  `json:&quot;updated_on,omitempty&quot;`
	LastNotified time.Time  `json:&quot;last_notified,omitempty&quot;`
	UserId       uint64  `json:&quot;user_id&quot;`
	User         User    `json:&quot;-&quot; gorm:&quot;foreignKey:UserId&quot;`
}

I will like to do something like to perform the following sql join between product_prices and users tables

select product_prices.id, product_prices.price, product_prices.created_on, users.email as user_email, users.created_on as user_created_on from product_prices join users on product_prices.user_id=users.id;

Here is one of many things i have tried but still out of luck, thanks to the lack of clarity on the documentation page https://gorm.io/docs/query.html#Joins
I will also like to iterate over the rows

following the docs here https://gorm.io/docs/query.html#Joins i tried the following

// struct for result of join query
type ProductPriceUser struct {
	Id                     uint64 `json:&quot;id&quot;`
	Price                  float64 `json:&quot;price&quot;`
	CreatedOn              time.Time  `json:&quot;created_on,omitempty&quot;`
	UserEmail              User `json:&quot;user_email&quot;`
	UserCreatedOn          User `json:&quot;user_created_on&quot;`
}

var productPrice ProductPrice

database.DB.Model(&amp;productPrice{}).Select(&quot;productPrices.id, productPrices.price, productPrices.created_on, users.email as users_email, users.created_on as users_created_on&quot;).Joins(&quot;join user on productPrices.user_id = users.id&quot;).Scan(&amp;ProductPriceUser{})

for _, row := range ProductPriceUser {
	fmt.Println(&quot;values: &quot;, row.Id, row.Price, row.CreatedOn, row.UserEmail, row.UserCreatedOn, &quot;\n&quot;)
}

but am getting all sorts of erors, what am i doing wrong and how do I get what i want as descibed above?

Thanks!

答案1

得分: 1

一个可能的解决方案是这样做(假设你的数据库表名为 product_pricesusers):

list := []ProductPriceUser{}

err := database.DB.Table("product_prices").
            Join("JOIN users ON users.id=product_prices.user_id").
            Select("product_prices.id, product_prices.price, product_prices.created_on, users.email as user_email, users.created_on as user_created_on").
            Find(&list).Error
if err != nil {
  // 处理错误
}

然后,你可以遍历 list 来获取每条记录。

英文:

One possible solution would be to do something like this (assuming your DB tables are named product_prices and users):

list := []ProductPriceUser{}

err := database.DB.Table(&quot;product_prices&quot;).
            Join(&quot;JOIN users ON users.id=product_prices.user_id&quot;).
            Select(&quot;product_prices.id, product_prices.price, product_prices.created_on, users.email as user_email, users.created_on as user_created_on&quot;).
            Find(&amp;list).Error
if err != nil {
  // handle error
}

Then, you can iterate over the list to get every record.

huangapple
  • 本文由 发表于 2022年1月28日 05:28:52
  • 转载请务必保留本文链接:https://go.coder-hub.com/70885983.html
匿名

发表评论

匿名网友

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

确定