gorm 常用操作

//子查询
//GetLatelyUserOperationFlow 获取每个列表数据中的最近一条的未读消息
func GetLatelyUserOperationFlow(orgId int32, objectId []int32) (userOperationFlowList []*TUserOperationFlow) {
    var subQuery = dao.InnerDao.Gorm.Table(TableUserOperationFlow(orgId)).Select("objectId objectId1 , max(createTime) createTime1 ").Where(" `status` = 2 and objectId in (?) ", objectId).Group("objectId").SubQuery()
    dao.InnerDao.Gorm.Table(TableUserOperationFlow(orgId)).Select(
        fmt.Sprintf("%s,%s,%s,%s,%s,%s,%s",
            TUserOperationFlowColumns.FlowID,
            TUserOperationFlowColumns.OperatorUserID,
            TUserOperationFlowColumns.EventType,
            TUserOperationFlowColumns.ObjectID,
            TUserOperationFlowColumns.ContentInfo,
            TUserOperationFlowColumns.CreateTime,
            TUserOperationFlowColumns.Status)).Joins(" join (?) as M on objectId = objectId1 and createTime = createTime1  ", subQuery).Scan(&userOperationFlowList)
    return userOperationFlowList
}

最终生成的sql

SELECT flowId,operatorUserId,eventType,objectId,contentInfo,createTime,status FROM  t_user_operation_flow_2817360   join ((SELECT objectId objectId1 , max(createTime) createTime1  FROM  t_user_operation_flow_2817360   WHERE ( `status` = 2 and objectId in (1,3) ) GROUP BY objectId)) as M on objectId = objectId1 and createTime = createTime1     

 

//conditionGetList 动态构建查询条件
func conditionGetList(TableName string, count, sceneId int32, userId int64) funDB {
    return func(db *gorm.DB) *gorm.DB {
        if count > 0 {
            db.Limit(count)
        }
        if sceneId > 0 {
            db.Where(fmt.Sprintf("%s.tags->'$.firEvaScene = ?", TableName), sceneId)
        }
        if userId > 0 {
            db.Where(fmt.Sprintf("%s.creatorId = ?", TableName), userId)
        }
        return db
    }
}

//GetSceneActivityList 动态条件查询场景活动
func GetSceneActivityList(orgId, count, sceneId int32, userId int64) (list []model.ActivityScene, err error) {
    list = make([]model.ActivityScene, 0)
    err = dao.InnerDao.Gorm.Table(TableActivity(orgId)).Select(" activityId,name,extra , extra->'$.firEvaScene' as firEvaScene ").Where(" extra->'$.firEvaScene' > 0 ").Scopes(conditionGetList(TableActivity(orgId), count, sceneId, userId)).Order(" createTime DESC").Scan(&list).Error
    return
}