英文:
show json array inside of a json object using gorm
问题
我正在尝试使用gorm进行查询。我有一个名为users的模型,就像这样:
type Users struct {
	gorm.Model
	ID          uint   `gorm:"autoIncrement;unique" json:"id"`
	PhoneNumber string `gorm:"primaryKey" json:"phone_number"`
	Name        string `gorm:"default:dear user" json:"name"`
	Rank        uint   `json:"rank"`
	Score       uint   `json:"score"`
	Image       string `json:"image"`
	Email       string `json:"email"`
	Address     string `json:"address"`
	Birthday    string `json:"birthday"`
	Biography   string `json:"biography"`
}
还有另一个模型,表示用户购买的课程。
type UserCourse struct {
	CourseID        uint   `gorm:"primaryKey" json:"course_id"`
	UserPhoneNumber string `gorm:"primaryKey" json:"user_phone_number"`
	Progress        uint   `json:"progress"`
	CreatedAt       time.Time
	UpdatedAt       time.Time
}
现在,我正在寻找一种方法,根据用户的分数和他们购买的课程返回前100名用户。换句话说,期望的JSON对象如下所示:
{
  "users":[
      {
        "id":1,
        "phoneNumber":"99999999",
        "name":"test",
        "rank":1,
        "score":123456789,
        "image":"http://...",
        "email":"test@test.com",
        "address":"test",
        "birthday":"2021-01-01",
        "biography":"test here",
        "courses": [
          {
            "course_id":1,
            "user_phone_number":"99999999",
            "progress": 53,
            "created_at": "2021-01-01",
            "updated_at": "2021-01-01"
          },
          {
            "course_id":2,
            "user_phone_number":"99999999",
            "progress":100,
            "created_at":"2021-02-01",
            "updated_at":"2021-03-01"
          }
        ]
     }
  ]
}
我知道我需要使用以下查询来获取前100名用户:
database.myDatabase.Order("rank asc").Limit(100).Find(users)
但不幸的是,我不知道如何编写适用于所述输出的gorm查询。
英文:
I'm trying to use gorm for my queries. I have a model called users just like this:
type Users struct {
	gorm.Model
	ID          uint   `gorm:"autoIncrement;unique" json:"id"`
	PhoneNumber string `gorm:"primaryKey" json:"phone_number"`
	Name        string `gorm:"default:dear user" json:"name"`
	Rank        uint   `json:"rank"`
	Score       uint   `json:"score"`
	Image       string `json:"image"`
	Email       string `json:"email"`
	Address     string `json:"address"`
	Birthday    string `json:"birthday"`
	Biography   string `json:"biography"`
}
and another model which represents the courses that the user has purchased.
type UserCourse struct {
	CourseID        uint   `gorm:"primaryKey" json:"course_id"`
	UserPhoneNumber string `gorm:"primaryKey" json:"user_phone_number"`
	Progress        uint   `json:"progress"`
	CreatedAt       time.Time
	UpdatedAt       time.Time
}
now I am looking for a way to return top 100 users based on their score with the courses they have purchased. in the other word, the below JSON object is desirable:
{
  "users":[
      {
        "id":1,
        "phoneNumber":"99999999",
        "name":"test",
        "rank":1,
        "score":123456789,
        "image":"http://...",
       "email":"test@test.com",
        "address":"test",
        "birthday":"2021-01-01",
        "biography":"test here",
        "courses": [
          {
            	"course_id":1,
    	        "user_phone_number":"99999999",
    	        "progress": 53,
    	        "created_at": "2021-01-01",
    	        "updated_at": "2021-01-01",
          } ,
          {
              "course_id":2,
    	        "user_phone_number":"99999999",
    	        "progress":100,
    	        "created_at":"2021-02-01",
    	        "updated_at":"2021-03-01",
          }
        ]
     }
  ]
}
I know I have to use the below query to get top 100 users:
database.myDatabase.Order("rank asc").Limit(100).Find(users)
but unfortunately, I have no idea how to write the gorm suitable for the mentioned output.
答案1
得分: 1
如果你将Users模型扩展如下:
type Users struct {
    gorm.Model
    ID          uint   `gorm:"autoIncrement;unique" json:"id"`
    PhoneNumber string `gorm:"primaryKey" json:"phone_number"`
    Name        string `gorm:"default:dear user" json:"name"`
    Rank        uint   `json:"rank"`
    Score       uint   `json:"score"`
    Image       string `json:"image"`
    Email       string `json:"email"`
    Address     string `json:"address"`
    Birthday    string `json:"birthday"`
    Biography   string `json:"biography"`
    Courses     []*UserCourse `gorm:"foreignKey:UserPhoneNumber;references:PhoneNumber" json:"courses"`
}
你可以通过以下方式将课程预加载到用户结构中:
database.myDatabase.Preload("Courses").Order("rank asc").Limit(100).Find(users)
英文:
If you extend your Users model as such:
type Users struct {
    gorm.Model
    ID          uint   `gorm:"autoIncrement;unique" json:"id"`
    PhoneNumber string `gorm:"primaryKey" json:"phone_number"`
    Name        string `gorm:"default:dear user" json:"name"`
    Rank        uint   `json:"rank"`
    Score       uint   `json:"score"`
    Image       string `json:"image"`
    Email       string `json:"email"`
    Address     string `json:"address"`
    Birthday    string `json:"birthday"`
    Biography   string `json:"biography"`
    Courses     []*UserCourse `gorm:"foreignKey:UserPhoneNumber;references:PhoneNumber" json:"courses"`
}
you can then preload the courses into the user struct by using:
database.myDatabase.Preload("Courses").Order("rank asc").Limit(100).Find(users)
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库,让每个人都能够通过互相帮助和分享经验来进步。


评论