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


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.price, product_prices.created_on, as user_email, users.created_on as user_created_on from product_prices join users on;

Here is one of many things i have tried but still out of luck, thanks to the lack of clarity on the documentation page
I will also like to iterate over the rows

following the docs here 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.price, productPrices.created_on, as users_email, users.created_on as users_created_on").Joins("join user on productPrices.user_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?



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").
            Select(", product_prices.price, product_prices.created_on, as user_email, users.created_on as user_created_on").
if err != nil {
  // handle error

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

Answered By – Emin Laletovic

Answer Checked By – Mildred Charles (GoLangFix Admin)

Leave a Reply

Your email address will not be published.