Stephan882
Stephan882

Reputation: 133

How to convert MySQL query into GORM query

I have two tables: users and jobs where each user can have multiple jobs, but one specific job belongs to one user.

type Job struct {
    ID      uint   `gorm:"primarykey" json:"id"`
    Title   string `gorm:"type:varchar(255); not null" json:"title"`
    Content string `gorm:"not null" json:"content"`
    UserID  uint   `json:"-"`
}

type User struct {
    ID         uint      `gorm:"primarykey" json:"-"`
    UUID       uuid.UUID `gorm:"type:char(36) not null" json:"-"`
    Name       string    `gorm:"type:varchar(255); not null" json:"name"`
    Jobs       []Job     `json:"-"`
}

I want to get every single post with its author.

The following query works:

SELECT j.id, j.title, j.content, u.name AS author
FROM jobs AS j
INNER JOIN users AS u
ON j.user_id = u.id

In Gorm:

func (jobRepo repository) FindAll() []entity.Job {
    var jobs []entity.Job
    jobRepo.db.Find(&jobs)

    // how do we handle the rest?

    return jobs
}

I have to return the following JSON response:

job_id: <random_id>
job_title: "Test Title Content",
job_content: "Test Job Content",
author: {
  name: "Test User Name"
}

How can I get author's data for each post?

Upvotes: 0

Views: 387

Answers (1)

Emin Laletovic
Emin Laletovic

Reputation: 4324

Looking at the JSON response you need to generate, you might start by adding the User field to the Job struct like this:

type Job struct {
    ID      uint   `gorm:"primarykey" json:"id"`
    Title   string `gorm:"type:varchar(255); not null" json:"title"`
    Content string `gorm:"not null" json:"content"`
    UserID  uint   `json:"-"`
    User    User   `json:"author"`
}

Also, just a small change is needed in your repo method to load this. I've added error checks because you should have them always.

func (jobRepo repository) FindAll() ([]entity.Job, error) {
    var jobs []entity.Job
    tx := jobRepo.db.Preload("User").Find(&jobs)

    return jobs, tx.Error
}

Upvotes: 1

Related Questions