Golang MysqlDB.Debug方法代码示例

//根据用户关注的话题给它推荐相关的用户
func User_RecommendFromTopic(userId int64) ([]User, error) {
	iRecommendCount := 10
	var db *goku.MysqlDB = GetDB()
	db.Debug = true
	defer db.Close()

	sql := "SELECT `topic_id` FROM `topic_follow` WHERE `user_id`=? ORDER BY `create_time` DESC limit ?"
	topicRows, topicErr := db.Query(sql, userId, iRecommendCount)
	if topicErr != nil {
		return nil, topicErr
	}
	topicIds := make([]int64, 0)
	var topicId int64
	for topicRows.Next() {
		topicErr = topicRows.Scan(&topicId)
		if topicErr == nil {
			topicIds = append(topicIds, topicId)
		}
	}

	hashUsers := map[int64]int64{}
	users := make([]User, 0)
	tLen := len(topicIds)
	var uCount int
	uCount = iRecommendCount / tLen
	strUserIds := fmt.Sprintf("%d", userId)
	hashUsers[userId] = userId
	if tLen > 0 {
		sql = "SELECT u.`id`,u.`name`,u.`email`,u.`description`,u.`user_pic`,u.`friend_count`,u.`topic_count`,u.`ftopic_count`,u.`status`," +
			"u.`follower_count`,u.`link_count`,u.`create_time` FROM `tui_link_for_topic_top` tl INNER JOIN `link` l ON " +
			"tl.`topic_id`=? AND tl.`link_id`=l.`id` AND l.`user_id` NOT IN(?) " +
			"AND NOT EXISTS(SELECT 1 FROM `user_follow` uf WHERE uf.`user_id`=? AND uf.`follow_id`=l.`user_id`) " +
			"INNER JOIN `user` u ON u.`id`=l.`user_id` " +
			"ORDER BY tl.`reddit_score` DESC limit ?"

		for _, tId := range topicIds {
			userRows, userErr := db.Query(sql, tId, strUserIds, userId, uCount)
			if userErr == nil {
				for userRows.Next() {
					user := User{}
					userErr = userRows.Scan(&user.Id, &user.Name, &user.Email, &user.Description, &user.UserPic, &user.FriendCount, &user.TopicCount, &user.FtopicCount, &user.Status, &user.FollowerCount, &user.LinkCount, &user.CreateTime)
					if userErr == nil && hashUsers[userId] <= 0 {
						users = append(users, user)
						strUserIds += fmt.Sprintf(",%d", user.Id)
						hashUsers[user.Id] = user.Id
					}

				}
			}
		}
	}
	//fmt.Print(users)
	return users, nil
}

// 获取属于某话题的link
// @page: 从1开始
func Link_ForTopic(topicId int64, page, pagesize int, sortType string, t string) ([]Link, error) {
	var db *goku.MysqlDB = GetDB()
	db.Debug = true
	defer db.Close()

	page, pagesize = utils.PageCheck(page, pagesize)

	sortField := "tl.reddit_score DESC,tl.link_id DESC"
	tableName := "tui_link_for_topic_top"
	switch {
	case sortType == golink.ORDER_TYPE_HOTC: //热议
		sortField = "l.comment_count DESC,tl.link_id DESC"
		tableName = "tui_link_for_topic_top"
	case sortType == golink.ORDER_TYPE_CTVL: //争议
		sortField = "tl.vote_abs_score ASC,tl.vote_add_score DESC,tl.link_id DESC"
		tableName = "tui_link_for_topic_hot"
	case sortType == golink.ORDER_TYPE_TIME: //最新
		sortField = "tl.link_id desc"
		tableName = "tui_link_for_topic_later"
	case sortType == golink.ORDER_TYPE_VOTE: //得分
		sortField = "tl.vote DESC, tl.link_id DESC"
		tableName = "tui_link_for_topic_vote"
	default: //热门
		sortField = "tl.reddit_score DESC,tl.link_id DESC"
		tableName = "tui_link_for_topic_top"
	}

	qi := goku.SqlQueryInfo{}
	qi.Fields = "l.id, l.user_id, l.title, l.context, l.topics, l.vote_up, l.vote_down, l.view_count, l.click_count, l.comment_count, l.create_time"
	qi.Join = " tl INNER JOIN `link` l ON tl.link_id=l.id"

	if sortType == golink.ORDER_TYPE_CTVL || sortType == golink.ORDER_TYPE_VOTE {
		qi.Where = "tl.topic_id=? AND tl.time_type=?"
		switch {
		case t == "all": //1:全部时间；2:这个小时；3:今天；4:这周；5:这个月；6:今年
			qi.Params = []interface{}{topicId, 1}
		case t == "hour":
			qi.Params = []interface{}{topicId, 2}
		case t == "day":
			qi.Params = []interface{}{topicId, 3}
		case t == "week":
			qi.Params = []interface{}{topicId, 4}
		case t == "month":
			qi.Params = []interface{}{topicId, 5}
		case t == "year":
			qi.Params = []interface{}{topicId, 6}
		default:
			qi.Params = []interface{}{topicId, 1}
		}
	} else {
		qi.Where = "tl.topic_id=?"
		qi.Params = []interface{}{topicId}
	}
	qi.Limit = pagesize
	qi.Offset = pagesize * page
	qi.Order = sortField

	rows, err := db.Select(tableName, qi)

	if err != nil {
		goku.Logger().Errorln(err.Error())
		return nil, err
	}
	defer rows.Close()

	links := make([]Link, 0)
	for rows.Next() {
		link := Link{}
		err = rows.Scan(&link.Id, &link.UserId, &link.Title, &link.Context, &link.Topics,
			&link.VoteUp, &link.VoteDown, &link.ViewCount, &link.ClickCount, &link.CommentCount, &link.CreateTime)
		if err != nil {
			goku.Logger().Errorln(err.Error())
			return nil, err
		}
		links = append(links, link)
	}
	return links, nil
}

// topId:评论根节点id，加他过滤缩小范围，提升速度
// sortType:"top":热门；"hot":热议；"later":最新；"vote":得分
func GetSortComments(exceptIds string, parentPath string, topId int64, linkId int64, sortType string, permaFilter string, isLoadMore bool) string {
	var arrExceptIds []string
	if exceptIds != "" {
		arrExceptIds = strings.Split(exceptIds, ",")
		//检查每个都是整数才能往后执行
		for _, id := range arrExceptIds {
			_, err := strconv.ParseInt(id, 10, 64)
			if err != nil {
				return ""
			}
		}
	}

	pId := int64(0)
	var arrParentPath []string
	if parentPath != "/" {
		arrParentPath = strings.Split(strings.Trim(parentPath, "/"), "/")
		//检查每个都是整数才能往后执行,通过arrParentPath.len知道当前loadmore第几级
		for _, id := range arrParentPath {
			id, err := strconv.ParseInt(id, 10, 64)
			if err != nil {
				return ""
			}
			pId = id
		}
	}

	sortField := "c.reddit_score DESC,c.id DESC"
	switch {
	case sortType == "top": //热门
		sortField = "c.reddit_score DESC,c.id DESC"
	case sortType == "hot": //热议
		sortField = "ABS(c.vote_up-c.vote_down) ASC,(c.vote_up+c.vote_down) DESC,c.id DESC"
	case sortType == "later": //最新
		sortField = "c.id DESC"
	case sortType == "vote": //得分
		sortField = "(c.vote_up-c.vote_down) DESC, c.id DESC"
	}

	level := len(arrParentPath)

	var db *goku.MysqlDB = GetDB()
	db.Debug = true
	defer db.Close()

	where := " c.link_id=? "
	if permaFilter != "" { //显示某个评论
		sql := fmt.Sprintf("SELECT c.`id`,c.`link_id`,c.`user_id`,c.`parent_path`,c.`children_count`,c.`top_parent_id`,c.`parent_id`,c.`deep`,c.`status`,c.`content`,c.`create_time`,c.`vote_up`,c.`vote_down`,c.`reddit_score`,u.name AS user_name FROM comment c INNER JOIN `user` u ON %s %s AND c.user_id=u.id order by %s LIMIT 0,%v", where, permaFilter, sortField, golink.MaxCommentCount)

		rows, err := db.Query(sql, linkId)
		if err == nil {
			return BuildCommentTree(db, &rows, 1, exceptIds, level, parentPath, pId, sortType, isLoadMore)
		}

	} else {
		if level == 0 { //根级别的loadmore
			if exceptIds != "" {
				where += fmt.Sprintf("AND c.top_parent_id NOT IN(%s) AND c.Id NOT IN(%s)", exceptIds, exceptIds)
			}
			sql := fmt.Sprintf("SELECT c.`id`,c.`link_id`,c.`user_id`,c.`parent_path`,c.`children_count`,c.`top_parent_id`,c.`parent_id`,c.`deep`,c.`status`,c.`content`,c.`create_time`,c.`vote_up`,c.`vote_down`,c.`reddit_score`,u.name AS user_name FROM comment c INNER JOIN `user` u ON %s AND c.user_id=u.id order by %s LIMIT 0,%v", where, sortField, golink.MaxCommentCount)
			rows, err := db.Query(sql, linkId)
			if err == nil {
				link, errLink := Link_GetById(linkId)
				if errLink == nil {
					return BuildCommentTree(db, &rows, link.CommentRootCount-len(arrExceptIds), exceptIds, level, parentPath, pId, sortType, isLoadMore)
				}
			}
		} else if level > 0 {
			if exceptIds != "" {
				where += fmt.Sprintf(" AND c.top_parent_id=? AND c.id NOT IN(%s) AND c.parent_path like '%s%s' ", exceptIds, parentPath, "%")
			} else {
				where += fmt.Sprintf(" AND c.top_parent_id=? AND c.parent_path like '%s%s' ", parentPath, "%")
			}
			for _, id := range arrExceptIds {
				where += fmt.Sprintf(" AND c.parent_path not like '%s%s/%s'", parentPath, id, "%")
			}
			sql := fmt.Sprintf("SELECT c.`id`,c.`link_id`,c.`user_id`,c.`parent_path`,c.`children_count`,c.`top_parent_id`,c.`parent_id`,c.`deep`,c.`status`,c.`content`,c.`create_time`,c.`vote_up`,c.`vote_down`,c.`reddit_score`,u.name AS user_name FROM comment c INNER JOIN `user` u ON %s AND c.user_id=u.id order by %s LIMIT 0,%v", where, sortField, golink.MaxCommentCount)

			rows, err := db.Query(sql, linkId, topId)
			if err == nil {
				commentId, _ := strconv.ParseInt(arrParentPath[level-1], 10, 64)
				pComment, errComment := Comment_GetById(commentId)
				if errComment == nil {
					return BuildCommentTree(db, &rows, pComment.ChildrenCount-len(arrExceptIds), exceptIds, level, parentPath, pId, sortType, isLoadMore)
				}
			}
		}
	}

	return ""
}