英文:
show json array inside of a json object using gorm
问题
我正在尝试使用gorm进行查询。我有一个名为users的模型，就像这样：
type Users struct {gorm.ModelID 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.TimeUpdatedAt 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.ModelID 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.TimeUpdatedAt 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.ModelID 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.ModelID 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)
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论