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