英文:
How do I Perform Join With GORM and iterate over rows of result?
问题
使用gorm,我为users
和product_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_prices
和users
表之间的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:"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"`
}
// model for table `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"`
}
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:"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
database.DB.Model(&productPrice{}).Select("productPrices.id, productPrices.price, productPrices.created_on, users.email as users_email, users.created_on as users_created_on").Joins("join user on productPrices.user_id = users.id").Scan(&ProductPriceUser{})
for _, row := range ProductPriceUser {
fmt.Println("values: ", row.Id, row.Price, row.CreatedOn, row.UserEmail, row.UserCreatedOn, "\n")
}
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_prices
和 users
):
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("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 {
// handle error
}
Then, you can iterate over the list
to get every record.
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库,让每个人都能够通过互相帮助和分享经验来进步。
评论