Sorting data by foreign keys using Go with Gorm

php Xiaobian Xigua today will share with you a method of using Go language and Gorm library to sort data by foreign keys. In most databases, we often need to sort data based on fields associated with foreign keys. By using the Gorm library, we can easily implement this functionality. This article will teach you how to use Gorm's Preload method and Order method to implement foreign key sorting, making your data query more flexible and efficient. Let’s take a look at the specific steps!

Question content

I don’t know, I’ve been staying here... So I need to sort the data based on foreign keys.

I've been trying some code (see below) but it doesn't work at all.

This is my structural data:

type User struct {
    ID          string          `gorm:"primarykey" json:"id"`
    Name        string          `gorm:"not null" json:"name"`
    Email       string          `gorm:"unique" json:"email"`
    Password    string          `gorm:"not null" json:"password"`
    Phone       string          `json:"phone"`
    AccountType string          `json:"account_type"`
    Key         string          `json:"key"`
    RoleID      string          `gorm:"not null" json:"role_id"`
    Role        role.Role       `gorm:"foreignKey:RoleID" json:"role"`
    CreatedAt   time.Time       `json:"-"`
    UpdatedAt   time.Time       `json:"-"`
    DeletedAt   gorm.DeletedAt  `gorm:"index" json:"-"`

}

type Role struct {
  ID          string                  `gorm:"primarykey" json:"id"`
  Name        string                  `gorm:"not null" json:"name"`
  TierLevel   uint                    `gorm:"not null" json:"tier_level"`
  CreatedAt   time.Time               `gorm:"not null;default:current_timestamp" json:"-"`
  UpdatedAt   time.Time               `gorm:"not null;default:current_timestamp" json:"-"`
  DeletedAt   gorm.DeletedAt          `gorm:"index" json:"-"`
}

I wanted to sort the data based on role, so I wrote code like this

# my first trying #
result = query.Preload("Role", func(db *gorm.DB) *gorm.DB {
                return db.Order(orderString)
            }).Limit(limit).Offset(offset).Find(&users)

# my second trying #
result = query.Preload("Role").Limit(limit).Offset(offset).Find(&users)
            roles := []roleModel.Role{} --> this roleModel had been importing in this file 
            for i := range roles {
                result.Model(&roles[i]).Order("roles.name ASC")
            }

Neither works, have you experienced this before?

Really need your advice...Thanks

Solution

So, after browsing so many references, I figured this out. Here's my answer in case everyone faces the same problem in the future:

parts := strings.Split(sortBy, ".") --> this sortBy was kind of like "role.name"
field := strings.TrimSpace(parts[1])
orderString = fmt.Sprintf("roles.%s %s", field, sortOrder)
result = query.Limit(limit).Offset(offset).Joins("JOIN roles ON users.role_id = roles.id").Order(orderString).Find(&users)

I use the join method and I can order the data based on the fields joined from the role model

The above is the detailed content of Sorting data by foreign keys using Go with Gorm. For more information, please follow other related articles on the PHP Chinese website!