go多表关联通用视图查询

本文介绍了基于开源框架webframe的视图创建与Go代码实现。主要内容包括：创建了两个PostgreSQL视图： view_student_fsrs_cards：关联fsrs_cards和vocab_words表 view_student_fsrs_words：扩展包含words表字段实现了Go语言数据访问层：定义了ViewStudentFsrsWords结构体及DTO 提供了DAO操作类

leijmdas

1210人浏览 · 2026-01-10 09:35:50

leijmdas · 2026-01-10 09:35:50 发布

开源框架：https://gitee.com/gowebframe3/webframe.git

本文介绍了基于开源框架webframe的视图创建与Go代码实现。主要内容包括：

创建了两个PostgreSQL视图：

view_student_fsrs_cards：关联fsrs_cards和vocab_words表
view_student_fsrs_words：扩展包含words表字段

实现了Go语言数据访问层：

定义了ViewStudentFsrsWords结构体及DTO
提供了DAO操作类和方法
包含自动迁移、缓存等功能

封装了UI接口层：

构建查询请求处理类
支持分页、排序、条件过滤等
提供测试用例示例

代码特点：

采用泛型设计
支持ORM操作
包含完善的查询构建功能
提供前后端交互数据结构

整套实现采用分层架构，包含数据库视图、数据访问层和UI接口层，适用于学生词汇学习系统的开发。

创建视图

-- public.view_student_fsrs_cards source

CREATE OR REPLACE VIEW public.view_student_fsrs_cards

AS SELECT vw.vocab_id,

fc.id,

fc.user_id,

fc.ctype,

fc.obj_id,

fc.front,

fc.tags,

fc.due,

fc.stability,

fc.difficulty,

fc.elapsed_days,

fc.scheduled_days,

fc.reps,

fc.lapses,

fc.state,

fc.last_review,

fc.created_at,

fc.updated_at,

fc.deleted_at,

fc.word_history_type,

fc.student_vocab_id

FROM fsrs_cards fc,

vocab_words vw

WHERE fc.obj_id = vw.word_id;

-- public.view_student_fsrs_words source

CREATE OR REPLACE VIEW public.view_student_fsrs_words

AS SELECT vsfc.vocab_id,

vsfc.id,

vsfc.user_id,

vsfc.ctype,

vsfc.obj_id,

vsfc.front,

vsfc.tags,

vsfc.due,

vsfc.stability,

vsfc.difficulty,

vsfc.elapsed_days,

vsfc.scheduled_days,

vsfc.reps,

vsfc.lapses,

vsfc.state,

vsfc.last_review,

vsfc.created_at,

vsfc.updated_at,

vsfc.deleted_at,

vsfc.word_history_type,

vsfc.student_vocab_id,

w.word,

w.meaning,

w.level,

w.ipa,

w.spelling,

w.audio,

w.remark,

w.vocab,

w.part_speech

FROM view_student_fsrs_cards vsfc,

words w

WHERE vsfc.obj_id = w.id;

生成代码

func (self *TestDbSuite) Test001_dbMakeGoCodes() {

    //makego.MakeGoCodes("student_plan_template")plan_personl_meta 
    makego.MakeGoCodes("view_student_fsrs_words")

}

package dbentity

import ( 
    "gitee.com/gowebframe3/webframe/goconfig/base/basedto" 
    "gitee.com/gowebframe3/webframe/goconfig/ichubconfig" 
    "gitee.com/gowebframe3/webframe/goweb/generaldb/generaldao" 
    "github.com/gogf/gf/v2/util/gconv"
    "github.com/jinzhu/gorm"
    "strings"
    "time"
)

/*
@Title    文件名称: view_student_fsrs_words.go
@Description  描述: 统一返回结构

@Author  作者: raymond@163.com  时间(2026-01-10 09:27:19)
@Update  作者: raymond@163.com  时间(2026-01-10 09:27:19)
*/

type  ViewStudentFsrsWords struct { 
     basedto.BaseEntity  // gorm:"-"
       //  
     VocabId int64 `json:"vocabId,string" gorm:"column:vocab_id;type:bigint`
    //  
     Id int64 `json:"id,string" gorm:"autoIncrement:id;PRIMARY_KEY"`
    //  
     UserId int64 `json:"userId,string" gorm:"column:user_id;type:bigint`
    //  
     LastReview time.Time `json:"lastReview" gorm:"column:last_review;type:timestamp with time zone`
    //  
     CreatedAt time.Time `json:"createdAt" gorm:"column:created_at;type:timestamp with time zone`
    //  
     UpdatedAt time.Time `json:"updatedAt" gorm:"column:updated_at;type:timestamp with time zone`
    //  
     DeletedAt *time.Time `json:"deletedAt" gorm:"column:deleted_at;type:timestamp with time zone`
    //  
     WordHistoryType int16 `json:"wordHistoryType" gorm:"column:word_history_type;type:smallint`
    //  
     StudentVocabId int64 `json:"studentVocabId,string" gorm:"column:student_vocab_id;type:bigint`
    //  
     Level int16 `json:"level" gorm:"column:level;type:smallint`
    //  
     Audio string `json:"audio" gorm:"column:audio;type:json`
    //  
     Vocab int16 `json:"vocab" gorm:"column:vocab;type:smallint`
    //  
     ObjId int64 `json:"objId,string" gorm:"column:obj_id;type:bigint`
    //  
     Due time.Time `json:"due" gorm:"column:due;type:timestamp with time zone`
    //  
     Stability float64 `json:"stability" gorm:"column:stability;type:numeric`
    //  
     Difficulty float64 `json:"difficulty" gorm:"column:difficulty;type:numeric`
    //  
     ElapsedDays int64 `json:"elapsedDays,string" gorm:"column:elapsed_days;type:bigint`
    //  
     ScheduledDays int64 `json:"scheduledDays,string" gorm:"column:scheduled_days;type:bigint`
    //  
     Reps int64 `json:"reps,string" gorm:"column:reps;type:bigint`
    //  
     Lapses int64 `json:"lapses,string" gorm:"column:lapses;type:bigint`
    //  
     State int16 `json:"state" gorm:"column:state;type:smallint`
    //  
     Ctype string `json:"ctype" gorm:"column:ctype;type:character varying`
    //  
     Ipa string `json:"ipa" gorm:"column:ipa;type:character varying`
    //  
     Front string `json:"front" gorm:"column:front;type:character varying`
    //  
     Tags string `json:"tags" gorm:"column:tags;type:character varying`
    //  
     Spelling string `json:"spelling" gorm:"column:spelling;type:character varying`
    //  
     PartSpeech string `json:"partSpeech" gorm:"column:part_speech;type:character varying`
    //  
     Word string `json:"word" gorm:"column:word;type:character varying`
    //  
     Meaning string `json:"meaning" gorm:"column:meaning;type:character varying`
    //  
     Remark string `json:"remark" gorm:"column:remark;type:character varying` 

}

type  ViewStudentFsrsWordsDto struct {
    // basedto.BaseEntity
    ViewStudentFsrsWords 

}


func NewViewStudentFsrsWords() *ViewStudentFsrsWords {
    return &ViewStudentFsrsWords{}
}
func NewViewStudentFsrsWordsDto() *ViewStudentFsrsWordsDto {
    return &ViewStudentFsrsWordsDto{}
}

func (self *ViewStudentFsrsWords) PkeyName() string {
    return "id"
}
func (self *ViewStudentFsrsWords) PkeyValue() int64 {
    return self.Id
}
func (self *ViewStudentFsrsWords) TableName() string {
    return "view_student_fsrs_words"
}

func (self *ViewStudentFsrsWords) NewDao()  * generaldao.BaseDao[int64, *ViewStudentFsrsWords] {
    return  generaldao.NewBaseDao[int64, *ViewStudentFsrsWords]()
}

func (self *ViewStudentFsrsWords) AutoMigrate (db *gorm.DB) error {
    return db.AutoMigrate(self).Error
}

func NewDaoViewStudentFsrsWords()  * generaldao.BaseDao[int64, *ViewStudentFsrsWords] {
    return  generaldao.NewBaseDao[int64, *ViewStudentFsrsWords]()
}

func (self *ViewStudentFsrsWords) ObjectKey() string {

    var keys = []any{
       ichubconfig.FindEnv(), "db", self.TableName(), self.PkeyValue(),
    }
    return strings.Join(gconv.SliceStr(keys), ":")
}

func (self *ViewStudentFsrsWords) CacheKey() string {

    return self.ObjectKey()
}

func (self *ViewStudentFsrsWords) CacheKeyOf(id int64) string {
    self.Id = id
    return self.ObjectKey()
}

package dbentity

import (
    "gitee.com/gowebframe3/webframe/goconfig/base/basedto"
    "gitee.com/gowebframe3/webframe/goweb/generaldb/generaldao"

     
)

/*
@Title    文件名称: view_student_fsrs_words.go
@Description  描述: 统一返回结构

@Author  作者: raymond@163.com  时间({{date}})
@Update  作者: raymond@163.com  时间({{date}})
*/

 

type ViewStudentFsrsWordsDao struct {
    basedto.BaseEntity
    *generaldao.BaseDao[int64, *ViewStudentFsrsWords]
}

func NewViewStudentFsrsWordsDao() *ViewStudentFsrsWordsDao {
    return &ViewStudentFsrsWordsDao{
       BaseDao: generaldao.NewBaseDao[int64, *ViewStudentFsrsWords](),
    }
}

 UI接口封装

基础类

应用类

package viewui

import (
    "training-go/beapi/db/uibase"
    "training-go/beapi/dbview/viewentity"

    "gitee.com/gowebframe3/webframe/goconfig/base/basedto"
    "gitee.com/gowebframe3/webframe/goweb/pagemodel"
)

type ViewStudentFsrsWordsRequest struct {
    basedto.BaseEntity
    uibase.UiQueryRequest[int64, *viewentity.ViewStudentFsrsWords]
}

func NewViewStudentFsrsWordsRequest() *ViewStudentFsrsWordsRequest {
    var req = &ViewStudentFsrsWordsRequest{}
    req.InitDao()
    req.initQuery()
    return req
}
func (self *ViewStudentFsrsWordsRequest) initQuery() *ViewStudentFsrsWordsRequest {
    self.SetBeforeQuery(func() {
       if self.Param.Id > 0 {
          self.DbEq("id", self.Param.Id)
       }

       if self.Param.Word != "" {
          self.DbEq("word", self.Param.Word)
       }
       if self.Param.PartSpeech != "" {
          self.DbEq("part_speech", self.Param.PartSpeech)
       }
       if self.Param.UserId > 0 {
          self.DbEq("user_id", self.Param.UserId)
       }
       if self.Param.VocabId > 0 {
          self.DbEq("vocab_id", self.Param.VocabId)
       }
       if self.Param.Reps > 0 {
          self.DbEq("reps", self.Param.Reps)
       }

       if self.Param.WordHistoryType > 0 {
          self.DbEq("word_history_type", self.Param.WordHistoryType)
       }
       if self.CreatedAtStart != "" {
          self.DbBetween("created_at", self.CreatedAtStart, self.CreatedAtEnd)
       }
       if self.Param.Meaning != "" {
          self.DbLike("meaning", self.Param.Meaning)
       }
       //common
       if self.Keyword != "" {
          self.DbKeyword("word,meaning", self.Keyword)
       }
       if self.IfBetweenValues() {
          self.DbBetween("reps", self.BetweenValues[0], self.BetweenValues[1])
       }
       if self.CreatedAtStart != "" {
          self.DbBetween("created_at", self.CreatedAtStart, self.CreatedAtEnd)
       }

    })
    return self
}
func (self *ViewStudentFsrsWordsRequest) UiList() *pagemodel.PageResult[*viewentity.ViewStudentFsrsWords] {

    var ret = self.List()

    return ret
}

// 查询Query
func (self *ViewStudentFsrsWordsRequest) List() *pagemodel.PageResult[*viewentity.ViewStudentFsrsWords] {

    self.BuildRequest().BeforeQuery()()
    self.Page(self.PageSize, self.PageCurrent)
    if self.OrderBys == "" {
       self.OrderByDesc("created_at")
    }
    return self.QueryModel()
}测试

func (self *TestViewUiSuite) Test001_FindBeanViewStudentFsrsWordsRequest() {
    var req = FindBeanViewStudentFsrsWordsRequest()
    req.PageSize = 2
    req.OrderByDesc("reps")
    req.BetweenValues = []int{1, 3}
    var ret = req.UiList()

    golog.Info(req, ret)
}

2026-01-10 09:33:10.444 [INFO] {
"code": 200,
"msg": "成功",
"exist": true,
"total": 3831,
"pageSize": 2,
"pageCurrent": 1,
"totalPages": 1916,
"data": [
{
"word": "key",
"id": 69006,
"reps": 3
},
{
"reps": 3,
"id": 69006,
"word": "key"
}
]
}

基础类代码：

package uibase

import (
    "reflect"
    "strings"

    "training-go/beapi/common"

    "gitee.com/gowebframe3/webframe/goconfig/base/basedto"
    "gitee.com/gowebframe3/webframe/goconfig/ichublog/golog"
    dto "gitee.com/gowebframe3/webframe/goweb/dto"
    "gitee.com/gowebframe3/webframe/goweb/generaldb/generaldao"
    "gitee.com/gowebframe3/webframe/goweb/generaldb/generaliface"
    "gitee.com/gowebframe3/webframe/goweb/pagedb"
    "gitee.com/gowebframe3/webframe/goweb/pagemodel"
    "github.com/gogf/gf/v2/util/gconv"
)

type UiQueryRequest[P generaliface.GoPkey, E generaliface.IBaseModel[P]] struct {
    UiQueryParam[P, E]
    *generaldao.BaseDao[P, E]

    Token       string                             `json:"token"`
    QueryDbName string                             `json:"queryDbName"`
    beforQuery  func()                             `json:"-"`
    afterQuery  func(E generaliface.IBaseModel[P]) `json:"-"`
    beforeSave  func()                             `json:"-"`
}

func NewUiQueryRequest[P generaliface.GoPkey, E generaliface.IBaseModel[P]]() *UiQueryRequest[P, E] {
    var req = &UiQueryRequest[P, E]{
       UiQueryParam: UiQueryParam[P, E]{},
    }
    req.Param = req.NewOfPtrType()
    return req
}
func (self *UiQueryRequest[P, E]) AfterQuery() func(E generaliface.IBaseModel[P]) {
    return self.afterQuery
}

func (self *UiQueryRequest[P, E]) SetAfterQuery(afterQuery func(E generaliface.IBaseModel[P])) {
    self.afterQuery = afterQuery
}

func (self *UiQueryRequest[P, E]) BeforeQuery() func() {
    return self.beforQuery
}

func (self *UiQueryRequest[P, E]) SetBeforeQuery(q func()) {
    self.beforQuery = q
}
func (self *UiQueryRequest[P, E]) IsNilValue(v any) bool {
    if v == nil {
       return true
    }
    rv := reflect.ValueOf(v)
    // 仅对可nil类型判断
    switch rv.Kind() {
    case reflect.Ptr, reflect.Slice, reflect.Map, reflect.Chan, reflect.Func:
       return rv.IsNil()
    }
    return false
}
func (self *UiQueryRequest[P, E]) InitDao() *UiQueryRequest[P, E] {

    if self.IsNilValue(self.Param) {
       self.Param = self.NewOfType()
    }
    if self.BaseDao == nil {
       self.BaseDao = generaldao.Default[P, E]()
    }

    if self.PageSize == 0 {
       self.PageSize = 10
       self.PageCurrent = 1
    }
    return self
}

func (self *UiQueryRequest[P, E]) NewOfType() E {
    var t E
    var typeOf = reflect.TypeOf(t)
    if typeOf.Kind() == reflect.Ptr {
       typeOf = typeOf.Elem()
    }
    var value = reflect.New(typeOf)
    return value.Interface().(E)
}
func (self *UiQueryRequest[P, E]) From(param *UiRequestParam) *UiQueryRequest[P, E] {
    err := gconv.Struct(param, &self.UiQueryParam)
    if err != nil {
       golog.Error(err)
    }
    self.BuildRequest()
    return self
}
func (self *UiQueryRequest[P, E]) UiQueryModel(param *UiRequestParam) *pagemodel.PageResult[E] {
    self.From(param)
    if self.beforQuery != nil {
       self.beforQuery()
    }
    if self.QueryDbName != "" {
       self.InitFuncGetDb(self.QueryDbName)
    }
    var ret = self.QueryModel()
    if ret.ExistRecord() {
       for i := range ret.Data {
          ret.Data[i].Fill()
       }
    }
    return ret
}
func (self *UiQueryRequest[P, E]) BuildOrderBys() *UiQueryRequest[P, E] {

    return self.buildOrderBys()

}
func (self *UiQueryRequest[P, E]) IfOrderBys() bool {

    return self.OrderBys != "" && len(self.OrderBys) != 0
}
func (self *UiQueryRequest[P, E]) buildOrderBys() *UiQueryRequest[P, E] {

    if self.IfOrderBys() {
       self.PageDbRequest.OrderBys = []*dto.OrderByDto{}
       var orderList = strings.Split(self.OrderBys, ",")
       for i := range orderList {
          var bys = strings.Split(orderList[i], "|")
          if len(bys) == 1 {
             bys = append(bys, "asc")
          }
          self.OrderBy(bys[0], bys[1])
          self.PageDbRequest.OrderBys = append(self.PageDbRequest.OrderBys, &dto.OrderByDto{
             Field: bys[0],
             Sort:  bys[1],
          })
       }

    }
    return self
}

func (self *UiQueryRequest[P, E]) BuildRequest() *UiQueryRequest[P, E] {
    if self.BaseDao == nil {
       self.BaseDao = generaldao.Default[P, E]()
    }
    if self.PageCurrent == 0 {
       self.InitDao()
    }
    self.PageDbRequest.PageSize = self.PageSize
    self.PageDbRequest.PageCurrent = self.PageCurrent
    if self.Tabler() == nil {
       var entity = self.NewOfType()
       self.PageDbRequest.SetTabler(entity)
       self.PageDbRequest.TableName = entity.TableName()
    }
    self.buildOrderBys()
    if self.CreatedAtStart != "" {
       self.DbBetween("created_at", self.CreatedAtStart, self.CreatedAtEnd)
    }
    if self.CreatedAtStart != "" {
       self.DbBetween("created_at", self.CreatedAtStart, self.CreatedAtEnd)
    }
    if self.UpdatedAtStart != "" {
       self.DbBetween("updated_at", self.UpdatedAtStart, self.UpdatedAtEnd)
    }
    self.BuildSeachParam()
    return self
}
func (self *UiQueryRequest[P, E]) BuildFrom(sp *common.SearchParam) *UiQueryRequest[P, E] {
    self.FromSearchParam(sp)
    return self.BuildSeachParam()
}

func (self *UiQueryRequest[P, E]) BuildSeachParam() *UiQueryRequest[P, E] {
    self.BuildFilters()

    return self.BuildKeyword()
}

func (self *UiQueryRequest[P, E]) BuildFilters() *UiQueryRequest[P, E] {
    if self.Filters != nil {
       for k, v := range self.Filters {
          self.DbEq(k, v)
       }
    }
    return self
}
func (self *UiQueryRequest[P, E]) BuildKeyword() *UiQueryRequest[P, E] {
    if self.keywordFields != "" && self.Keyword != "" {
       for _, v := range strings.Split(self.keywordFields, ",") {
          self.DbLike(v, "%"+self.Keyword+"%")

       }
    }
    return self
}

func (self *UiQueryRequest[P, E]) ResultUserFailMsg(msg string) *basedto.IchubResult {
    return basedto.ResultCodeMsg(33000, msg)
}

func (self *UiQueryRequest[P, E]) ResultUserSystemMsg(msg string) *basedto.IchubResult {
    return basedto.ResultCodeMsg(500, msg)
}
func (self *UiQueryRequest[P, E]) BuildOrderBysDao(dao *pagedb.PageDbRequest) *UiQueryRequest[P, E] {

    if self.IfOrderBys() {
       self.PageDbRequest.OrderBys = []*dto.OrderByDto{}
       var orderList = strings.Split(self.OrderBys, ",")
       for i := range orderList {
          var bys = strings.Split(orderList[i], "|")
          if len(bys) == 1 {
             bys = append(bys, "asc")
          }
          self.OrderBy(bys[0], bys[1])
          dao.OrderBys = append(dao.OrderBys, &dto.OrderByDto{
             Field: bys[0],
             Sort:  bys[1],
          })
       }

    }
    return self
}

package uibase

import (
    "training-go/beapi/common"

    "gitee.com/gowebframe3/webframe/goweb/generaldb/generaliface"
)

type UiQueryParam[P generaliface.GoPkey, E generaliface.IBaseModel[P]] struct {
    UiQueryParamBase
    Param E `json:"param"`
}

package uibase

type UiQueryParamBase struct {
    PageSize    int    `json:"pageSize"`    // 页大小：10
    PageCurrent int    `json:"pageCurrent"` // 当前页：1
    OrderBys    string `json:"orderBys"`    // 排序： rating|desc,trainCount|desc

    UserId         int64  `json:"userId,string"` // 用户标识
    CreatedAtStart string `json:"createdAtStart"`
    CreatedAtEnd   string `json:"createdAtEnd"`
    UpdatedAtStart string `json:"updatedAtStart"`
    UpdatedAtEnd   string `json:"updatedAtEnd"`

    BetweenValues []int          `json:"betweenValues"`
    BetweenField  string         `json:"betweenField"`
    keywordFields string         `json:"-"`
    Keyword       string         `json:"keyword"` // 通用查询 like
    Filters       map[string]any `json:"-"`
}

func (self *UiQueryParamBase) KeywordFields() string {
    return self.keywordFields
}

func (self *UiQueryParamBase) SetKeywordFields(keywordFields string) {
    self.keywordFields = keywordFields
}

func (self *UiQueryParamBase) BetweenFieldValues(f string, v []int) {
    self.BetweenField = f
    self.BetweenValues = v
}
func (self *UiQueryParamBase) IfBetweenValues() bool {

    return self.BetweenValues != nil && len(self.BetweenValues) > 0
}

腾讯云开发者社区

腾讯云面向开发者汇聚海量精品云计算使用和开发经验，营造开放的云计算技术生态圈。

更多推荐

Elasticsearch复杂数据类型终极指南：从入门到精通

Elasticsearch作为功能强大的搜索引擎，支持多种复杂数据类型，让开发者能够灵活处理各种结构化和非结构化数据。本文将带你全面了解Elasticsearch中的复杂数据类型，从基础概念到实际应用，助你轻松掌握数据建模的核心技巧。## 内部对象：构建层级化数据结构在Elasticsearch中，对象类型（Object）是最基础的复杂数据类型之一，用于表示具有嵌套关系的数据。例如，我们可

腾讯云开发者社区

终极指南：Flink SQL连接器版本管理从混乱到有序的升级之路

Apache Flink作为流处理领域的佼佼者，其SQL连接器的版本管理一直是开发者面临的核心挑战。本文将系统讲解Flink SQL连接器版本管理的最佳实践，帮助你轻松应对版本兼容性问题，实现从混乱到有序的升级之旅。## 连接器版本管理的常见痛点 😫在Flink应用开发中，连接器版本管理常常让开发者头疼不已。不同版本的连接器可能导致各种兼容性问题，例如API变更、功能差异甚至运行时错误。

腾讯云开发者社区

如何快速搭建Neon无服务器PostgreSQL：面向初学者的完整指南

Neon是一款革命性的无服务器PostgreSQL解决方案，它通过分离存储和计算层，实现了自动扩缩容、类代码式数据库分支以及零级扩展能力。本指南将帮助你从零开始搭建Neon开发环境，体验这款创新数据库的强大功能。## 准备工作：环境要求与依赖项在开始搭建Neon环境前，请确保你的系统满足以下要求：- Linux操作系统（推荐Ubuntu 20.04+或Debian 11+）- Git