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!
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