Golang: Query with JOIN using GO-PG package

Question

I need help with go-pg package and refactoring my query. I have 2 tables

Table definition for Student :

type Student {
   ID int `json:"id"`
   UserID int `json:"user_id"`
   FirstName string `json:"first_name"`
   LastName string `json:"last_name"`
}

Table definition for Teams :

type Teams {
   ID int `json:"id"`
   StudentID int `json:"strudent_id"`
   TeamName string `json:"team_name"`
   CreateDate time.Time `json:"create_date"`
}

I have the following method which i am sure i can improve. I don't know how, but every time make a call to the base with JOIN query is not the best solution I'm totally sure So here is my method

type Student struct{
   ID int `json:"id"`
   UserID int `json:"user_id"`
   FirstName string `json:"first_name"`
   LastName string `json:"last_name"`
}

type Team struct {
   ID int `json:"id"`
   StudentID int `json:"strudent_id"`
   TeamName string `json:"team_name"`
   CreateDate time.Time `json:"create_date"`
}

type StudentInTeam struct {
   UserID int `json:"id"`
   InTeam bool `json:"in_team"`
}


func (p *storage) FindStudentsInTeam (ids []int, teamID int)(map[int]bool, error){
    studentsInTeam := make(map[int]bool, len(ids))
    for _, id := range ids {
       res, err := p.Model((*Team)(nil)).
            Where("create_date <= ?", time.Now().UTC()).
            Where("team_id = teamID").
            Join("JOIN students ON students.id = student_id").
            Where("students.user_id = ?", id).
            Exists()
       if err != nil {
            return nil, err
       }
       studentsInTeam[id] = res
    }
    
    return studentsInTeam, nil
}

Will be appreciate for any help or advise. Have a nice day!

Answer 1

Querying inside a loop is not always a good idea. You can use IN clause to check the ids are exists in the table.

You can use Column method to select some specific columns

func (q *Query) Column(columns ...string) *Query {
    ..... 
}

You can rewrite your method as follows

func (p *storage) FindStudentsInTeam(ids []int, teamID int) (map[int]bool, error) {

    // rest of your codes

    var existedStudentTeams []Team
    err := p.Model(&existedStudentTeams).
        Column("team_id").
        Where("create_date <= ?", time.Now().UTC()).
        Where("team_id = ?", teamID).
        Join("JOIN students ON students.id = student_id").
        Where("students.user_id IN(?)", pg.In(ids)).
        Select()
    if err != nil {
        return nil, err
    }
    
    // rest of your codes
}

See More about

• Defining models
• SQL placeholders
• Writing queries

Answer 2

try this //

   func (p *storage) FindStudentsInTeam(ids []int, teamID int) (map[int]bool, error) {
    studentsInTeam := make(map[int]bool, len(ids))

    // Prepare the query
    query := p.Model((*Team)(nil)).
        Select("students.user_id").
        Where("create_date <= ?", time.Now().UTC()).
        Where("team_id = ?", teamID).
        Where("students.user_id IN (?)", ids)

    // Execute the query
    var result []StudentInTeam
    if err := query.Find(&result).Error; err != nil {
        return nil, err
    }

    // Build the result map
    for _, student := range result {
        studentsInTeam[student.UserID] = true
    }

    return studentsInTeam, nil
}