show json array inside of a json object using gorm

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

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)

huangapple
  • 本文由 发表于 2021年8月18日 13:02:33
  • 转载请务必保留本文链接:https://go.coder-hub.com/68826817.html
匿名

发表评论

匿名网友

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

确定