//根据用户关注的话题给它推荐相关的用户 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 "" }