一个gorm PageSql封装的进化

开始：

func (self *CoachService) ListCoachesAssign(req *QueryCoachRequest, trialFlag bool) *pagesql.PageResult { if req.PageSize == 0 { req.PageSize = 100 } var pagesql = pagesql.DefaultPageSql[viewdto.CoachVo]() var keywordAnd, sexWhere, phoneWhere, coachIdWhere, userTypeWhere string if req.Keyword != "" { keywordAnd = fmt.Sprintf(` and ( %s ) `, pagesql.KeywordWhere2Or("name,phone,user_no", req.Keyword)) } if req.Param.Sex != nil { sexWhere = fmt.Sprintf(` and sex = %d `, *req.Param.Sex) if *req.Param.Sex == 0 { sexWhere = ` and ( sex = 0 or sex is null ) ` } } if req.Param.Phone != "" { phoneWhere = fmt.Sprintf(` and phone = '%s' `, req.Param.Phone) } if req.Param.CoachId > 0 { phoneWhere = fmt.Sprintf(` and id = %d `, req.Param.CoachId) } userTypeWhere = fmt.Sprintf(` and user_type in (40,42) and opc_id = %d `, req.OpcId) if trialFlag { userTypeWhere = fmt.Sprintf(` and user_type in (41,42) and opc_id = %d `, req.OpcId) } var sql = `select * from users where deleted_at is null and rel_role='C' and is_pause=0 ` + keywordAnd + sexWhere + phoneWhere + coachIdWhere + userTypeWhere + ` and not (id in (select distinct coach_id from train_plan tp where status in (-1,0,1) and coach_id is not null and startd_at > ? and (startd_at between ? and ? or end_at between ? and ?) limit 2000))` var start = ctxt.FindBeanCoachContext().CoachIntervalBefore(req.Param.PlanStart) var end = ctxt.FindBeanCoachContext().CoachIntervalAfter(req.Param.PlanEnd) pagesql.SetLimit(req.PageSize) pagesql.AppendSql(sql) var args = []any{time.Now(), start, end, start, end} golog.Stat("sql:", pagesql.Sql, args) return pagesql.Scan2PageResultIf(false, args...) }

演化：

func (self *FacadeFsrs) QueryModelPersonPlanWordCards(req *facadedto.FsrsCardsRequest) *pagemodel.PageResult[*fsrsentity.FsrsCards] { if req.VocabID == 0 { if ret := userservice.FindBeanUserService().FindByIdAtCache(req.StudentId); ret.ExistRecord() { req.VocabID = ret.Data.VocabId } } if len(req.Filtered) == 0 { req.Filtered = []int64{0} } var where = ` WHERE fsrs_cards.user_id = %d AND fsrs_cards.obj_id IN (SELECT json_array_elements_text(meta->'items')::int FROM plan_personal WHERE plan_personal.user_id = %d AND plan_personal.type = 'formal' AND plan_personal.scope_id = %d ) AND fsrs_cards.obj_id NOT IN (%s) ` where = fmt.Sprintf(where, req.StudentId, req.StudentId, req.VocabID, strings.Join(gconv.SliceStr(req.Filtered), ",")) var countSql = `select COUNT(1) count FROM fsrs_cards ` + where var sql = `select fsrs_cards.* FROM fsrs_cards ` + where + ` order by fsrs_status(state),due ` var dao = daosingle.FindBeanDaoSingle().FsrsCardsDao.DefaultDao() dao.PageSize = req.PageSize dao.PageCurrent = req.PageCurrent var rets = dao.DbSelectPageResult(sql) if ret := daosingle.FindBeanDaoSingle().FsrsCardsDao.DbSelectCount(countSql); ret.IsSuccess() { rets.Total = gconv.Int(ret.Data) } else { rets.PageFail(ret.Msg) } return rets }

进化：

func (self *FacadeFsrs) QueryModelFsrsCardsSimple(req *FsrsCardsRequest) *page.PageResult { req.FillStudentVcab().Check() var dao = dbjoinsimple.FindBeanDbJoinSimple() dao.WithTabler(fsrsentity.FindBeanFsrsCards()) dao.WithSelect("fsrs_cards.id card_id,reps,obj_id,words.id,words.audio,words.hint,words.word,meaning,ipa,due,word_history_type") dao.WithJoins("join words on fsrs_cards.obj_id = words.id") dao.WithWhere("user_id = ?", req.StudentId) dao.WithWhere("ctype=?", "words") if req.TrainPlanId > 0 { dao.WithWhere("obj_id not in (select word_id from train_words where train_plan_id = ? and if_review = true) ", req.TrainPlanId) } dao.WithWhere(`obj_id IN (SELECT json_array_elements_text(meta->'items')::int FROM plan_personal WHERE plan_personal.user_id = ? AND plan_personal.type = 'formal' AND plan_personal.scope_id = ? ) `, req.StudentId, req.VocabID) if req.WordHistoryType > 0 { dao.WithWhere("word_history_type =?", req.WordHistoryType) } if req.Ipa != "" { dao.WithWhere("word_history_type =?", req.Ipa) } if req.Meaning != "" { dao.WithWhere("meaning like ?", req.Meaning) } if req.Word != "" { dao.WithWhere("word like ?", req.Word) } if req.Reps != nil && len(req.Reps) > 1 { dao.WithWhere("reps between ? and ? ", req.Reps[0], req.Reps[1]) } dao.WithOrder("fsrs_status(state)", true) dao.WithOrder("due", true) dao.WithPageSize(req.PageCurrent, req.PageSize) return dao.DbQueryJoin2Page(&[]*FsrsCards{}) }这篇文章展示了Go语言中数据库查询方法的演进历程： 初始版本使用原生SQL拼接方式构建查询，包含大量条件判断和字符串拼接，处理教练服务相关查询。 演化版本改进为使用预定义SQL模板和参数化查询，增强了词汇学习计划相关的卡片查询功能，包含分页处理和结果统计。 进化版本采用更结构化的构建器模式(dao模式)，通过链式调用设置查询条件、连接表和排序规则，实现了更清晰、更模块化的单词卡片查询功能。 这三个版本体现了从原始SQL到ORM风格的转变，代码可读性和可维护性逐步提升，同时保持了查询的灵活性。