【GoLang】记录一些开发过程中的业务SQL及对应的Gorm实现

桃扇骨 2023-10-02 15:03 19阅读 0赞

#### 文章目录 ####

*  记录1
 *   *  背景
     *  需求1：按题目分组查询用户提交数
     *   *  SQL
         *  Gorm写法
     *  需求2：按题目分组查询，并统计count，获取最新的时间
     *   *  SQL
         *  Gorm写法
     *  需求3：在需求2的基础上，按时间倒序排序
     *   *  SQL
         *  Gorm写法
     *  需求4：同时查询多个不同条件count值
     *   *  SQL
         *  Gorm写法

## 记录1 ##

### 背景 ###

表1：problems（习题表，存放习题的基本信息）

表2：submissions（提交记录表，用户对习题的提交记录，包含提交内容、提交结果、时间等信息）

开发语言是Go，使用的ORM框架是Gorm

### 需求1：按题目分组查询用户提交数 ###

#### SQL ####

SELECT
    		problem,count(*)
    	FROM
    		submissions
    	WHERE
    		creator = 5 
    	GROUP BY problem

![5ce5d274fa7783e5c3af45f4b56cd117.png][]

#### Gorm写法 ####

func test(user int) {
        
    	conn := db.GetDBConnection()
    	conn.Model(model.Submission{
        }).Select("problem, count(*) submission_count").
    		Where("a.creator=?", user)
    }

### 需求2：按题目分组查询，并统计count，获取最新的时间 ###

#### SQL ####

SELECT
    	max(a.created_at) last_time,
    	b.title,
    	b.difficulty,
    	count(*) submission_count
    FROM
    	submissions a
    	LEFT JOIN problems b ON a.problem = b.id 
    WHERE
    	a.creator = 5 
    GROUP BY
    	a.problem

![fea8e10ae96fa1f72365c57b2b2f9ea9.png][]

#### Gorm写法 ####

func test(user int) {
        
    	conn := db.GetDBConnection()
    	conn.Table("(?) a", conn.Model(model.Submission{
        })).
    		Joins("left join problems b on a.problem=b.id").
    		Select("max(a.created_at) last_time,b.title,b.difficulty, count(*) submission_count").
    		Where("a.creator=?", user).Group("a.problem")
    }

### 需求3：在需求2的基础上，按时间倒序排序 ###

> 分析：
> 
> 需要按题目分组的同时，获取到最后一次提交的时间，并按提交的先后顺序倒叙排序，所以做两次分组：  
> 第一次用题目ID和主键ID分组，并按主键倒叙排序；（主键为自增）  
> 第二次使用第一次的查询作为子查询，按题目ID分组，使用any\_value()函数获取同一分组中的第一条记录的时间，也就是最近一次提交的时间，  
> 需求2中，直接使用max(created\_at)获取最新的提交时间，只使用题目ID分组，无法满足分组的同时，实现所有记录的倒叙排序

#### SQL ####

SELECT
    	t.problem,
    	any_value ( t.created_at ) last_time,
    	t.title,
    	t.difficulty 
    FROM
    	(
    	SELECT
    		submissions.problem,
    		submissions.created_at,
    		problems.title,
    		problems.difficulty 
    	FROM
    		`submissions`
    		LEFT JOIN problems ON submissions.problem = problems.id 
    	WHERE
    		( submissions.creator = 5 ) 
    		AND `submissions`.`deleted_at` IS NULL 
    	GROUP BY
    		submissions.problem,
    		submissions.id 
    	ORDER BY
    		submissions.id DESC 
    	) AS t 
    GROUP BY
    	`t`.`problem`

![2403ce73e3d6b5b25eb7f6db33c5d3ab.png][]

#### Gorm写法 ####

type MySubmissionsDTO struct {
        
    	Title      string    `json:"title" form:"title"`             // 标题
    	Difficulty string    `json:"difficulty" form:"difficulty"`   // 难度
    	LastTime   time.Time `json:"last_time" form:"last_time"`     // 最后提交时间
    	PassedTime string    `json:"passed_time" form:"passed_time"` // 经过的时间
    	ProblemSubmissionCount
    }
    
    type ProblemSubmissionCount struct {
        
    	Problem         uint `json:"problem" form:"problem"`                   // 题目ID
    	SubmissionCount int  `json:"submission_count" form:"submission_count"` // 提交次数
    }
    
    func TestSql(user uint) {
        
    	conn := db.GetDBConnection()
    	tx := conn.Model(&model.Submission{
        }).
    		Select("submissions.problem,submissions.created_at,problems.title,problems.difficulty").
    		Joins("left join problems on submissions.problem = problems.id").
    		Where("submissions.creator=?", user)
    
    	//第一次分组：根据题目ID+主键ID分组
    	tx.Group("submissions.problem,submissions.id")
    	//按主键ID倒叙排序
    	tx.Order("submissions.id desc")
    
    	//第二次分组：根据题目ID分组（from子查询示例：子查询的参数是一个*gorm.DB对象）
    	tx2 := conn.Table("(?) as t", tx).Select("t.problem,any_value(t.created_at) last_time,t.title,t.difficulty").
    		Group("t.problem")
    
    	//计算count时，最好是将子查询包起来，避免count值有误
    	var total int64
    	conn.Table("(?) as t1", tx2).Count(&total)
    	var list [] MySubmissionsDTO
    	tx2.Find(&list)
    	fmt.Println("total: ", total)
    	fmt.Println("list: ", list)
    }

### 需求4：同时查询多个不同条件count值 ###

#### SQL ####

select
    (SELECT
    	count(*) 
    FROM
    	submissions 
    WHERE
    	creator = 5 
    	AND result = 1) passed_count,
    (SELECT
    	count(*) 
    FROM
    	submissions 
    WHERE
    	creator = 5 ) submission_count

或：

SELECT
    	* 
    FROM
    	( SELECT count(*) submission_count FROM `submissions` WHERE ( submissions.creator = 5 ) AND `submissions`.`deleted_at` IS NULL ) AS submission_count,
    	( SELECT count(*) passed_count FROM `submissions` WHERE ( submissions.creator = 5 AND result = 1 ) AND `submissions`.`deleted_at` IS NULL ) AS passed_count

![c2634578bc92af0abefe181f64d21f15.png][]

#### Gorm写法 ####

type Result struct {
        
    	PassedCount          int `json:"passed_count" form:"passed_count"`                     // 已通过题目数量
    	SubmissionTotalCount int `json:"submission_total_count" form:"submission_total_count"` // 提交总数
    }
    
    func test() {
        
    	conn := db.GetDBConnection()
    	submissionCountSql := conn.Model(&model.Submission{
        }).Select("count(*) submission_total_count").
    		Where("submissions.creator=?", 5)
    	passedCountSql := conn.Model(&model.Submission{
        }).Select("count(*) passed_count").
    		Where("submissions.creator=? and result=1", 5)
    	var result = Result{
        }
    	conn.Table("(?) as submission_total_count,(?) as passed_count", submissionCountSql, passedCountSql).
    		Find(&result)
    	fmt.Println("result: ", result)
    }

[5ce5d274fa7783e5c3af45f4b56cd117.png]: https://img-blog.csdnimg.cn/img_convert/5ce5d274fa7783e5c3af45f4b56cd117.png
[fea8e10ae96fa1f72365c57b2b2f9ea9.png]: https://img-blog.csdnimg.cn/img_convert/fea8e10ae96fa1f72365c57b2b2f9ea9.png
[2403ce73e3d6b5b25eb7f6db33c5d3ab.png]: https://img-blog.csdnimg.cn/img_convert/2403ce73e3d6b5b25eb7f6db33c5d3ab.png
[c2634578bc92af0abefe181f64d21f15.png]: https://img-blog.csdnimg.cn/img_convert/c2634578bc92af0abefe181f64d21f15.png