show json array inside of a json object using gorm

Question

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":"[email protected]",
        "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.

Is UserCourse is a column in Users or is that a separate table and foreign key relationship? What would be your normal SQL query to figure out 100 top users? Can you also add your database schema? — Shailesh Suryawanshi
– Shailesh Suryawanshi, Commented Aug 18, 2021 at 5:35
@ShaileshSuryawanshi thanks for your attention. yes, the UserCourse is quite another table that contains the courses that each user has. the normal SQL for getting top users is simple: select * from users order by rank asc limit 100 — Mohammad Derakhshan
– Mohammad Derakhshan, Commented Aug 18, 2021 at 5:41
I believe that there is one to many relationship between user and Usercourses. To fetch the results user with UserCourses you might have needed to write a complex SQL query/join? I do not see foreign key constraint in User model for UserCourse thus the question. To get your expected response of Json you might need to Marshal the struct from database, thus the question. — Shailesh Suryawanshi
– Shailesh Suryawanshi, Commented Aug 18, 2021 at 6:01

Anni · Accepted Answer · 2021-08-18 06:58:35Z

0

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)

answered Aug 18, 2021 at 6:58

Anni

1621 gold badge2 silver badges8 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

show json array inside of a json object using gorm

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related