Golang:使用GO-PG包进行JOIN查询

huangapple go评论92阅读模式
英文:

Golang: Query with JOIN using GO-PG package

问题

我需要帮助你处理go-pg包和重构你的查询。你有两个表格:

学生表的定义:

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:"student_id"`
   TeamName string `json:"team_name"`
   CreateDate time.Time `json:"create_date"`
}

我有以下方法,我相信我可以改进它。我不知道如何改进,但是每次使用JOIN查询调用基础方法都不是最好的解决方案,我完全确定。所以这是我的方法:

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:"student_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
}

非常感谢任何帮助或建议。祝你有愉快的一天!

英文:

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

Table definition for Student :

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

Table definition for Teams :

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

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:&quot;id&quot;`
   UserID int `json:&quot;user_id&quot;`
   FirstName string `json:&quot;first_name&quot;`
   LastName string `json:&quot;last_name&quot;`
}

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

type StudentInTeam struct {
   UserID int `json:&quot;id&quot;`
   InTeam bool `json:&quot;in_team&quot;`
}


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(&quot;create_date &lt;= ?&quot;, time.Now().UTC()).
		    Where(&quot;team_id = teamID&quot;).
		    Join(&quot;JOIN students ON students.id = student_id&quot;).
		    Where(&quot;students.user_id = ?&quot;, 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!

答案1

得分: 1

在循环内部进行查询并不总是一个好主意。你可以使用IN子句来检查表中是否存在ids

你可以使用Column方法选择特定的列:

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

你可以按照以下方式重写你的方法:

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

    // 你的其他代码

	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
	}
	
    // 你的其他代码
}

了解更多:

英文:

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(&amp;existedStudentTeams).
		Column(&quot;team_id&quot;).
		Where(&quot;create_date &lt;= ?&quot;, time.Now().UTC()).
		Where(&quot;team_id = ?&quot;, teamID).
		Join(&quot;JOIN students ON students.id = student_id&quot;).
		Where(&quot;students.user_id IN(?)&quot;, pg.In(ids)).
		Select()
	if err != nil {
		return nil, err
	}
	
    // rest of your codes
}

See More about

答案2

得分: 0

尝试这个

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

    // 准备查询
    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)

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

    // 构建结果映射
    for _, student := range result {
        studentsInTeam[student.UserID] = true
    }

    return studentsInTeam, nil
}
英文:

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(&quot;students.user_id&quot;).
        Where(&quot;create_date &lt;= ?&quot;, time.Now().UTC()).
        Where(&quot;team_id = ?&quot;, teamID).
        Where(&quot;students.user_id IN (?)&quot;, ids)

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

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

    return studentsInTeam, nil
}

huangapple
  • 本文由 发表于 2023年7月13日 02:03:20
  • 转载请务必保留本文链接:https://go.coder-hub.com/76673332.html
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定