// 获取由用户发布的评论
// @page: 从1开始
func Comment_ByUser(userId int64, page, pagesize int) []Comment {
if page < 1 {
page = 1
}
page = page - 1
if pagesize == 0 {
pagesize = 20
}
var db *goku.MysqlDB = GetDB()
defer db.Close()
qi := goku.SqlQueryInfo{}
qi.Limit = pagesize
qi.Offset = page * pagesize
qi.Where = "`user_id`=?"
qi.Params = []interface{}{userId}
qi.Order = "id desc"
var comments []Comment
err := db.GetStructs(&comments, qi)
if err != nil {
goku.Logger().Errorln(err.Error())
return nil
}
return comments
}
//模糊搜索用户
func User_SearchByName(name string, ctx *goku.HttpContext) ([]*VUser, error) {
var db *goku.MysqlDB = GetDB()
defer db.Close()
qi := goku.SqlQueryInfo{}
qi.Fields = "`id`,`name`,`email`,`description`,`user_pic`,`friend_count`,`topic_count`,`ftopic_count`,`status`,`follower_count`,`link_count`,`create_time`"
qi.Where = "name_lower LIKE ?"
qi.Params = []interface{}{strings.ToLower(name) + "%"}
qi.Limit = 10
qi.Offset = 0
qi.Order = "link_count DESC"
rows, err := db.Select("user", qi)
if err != nil {
goku.Logger().Errorln(err.Error())
return nil, err
}
users := make([]User, 0)
for rows.Next() {
user := User{}
err = rows.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 err != nil {
goku.Logger().Errorln(err.Error())
return nil, err
}
users = append(users, user)
}
return User_ToVUsers(users, ctx), nil
}
// 获取由用户收藏的link
// @page: 从1开始
func FavoriteLink_ByUser(userId int64, page, pagesize int) []Link {
var db *goku.MysqlDB = GetDB()
defer db.Close()
page, pagesize = utils.PageCheck(page, pagesize)
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.comment_count, l.create_time"
qi.Join = " ufl INNER JOIN `link` l ON ufl.link_id=l.id"
qi.Where = "ufl.user_id=?"
qi.Params = []interface{}{userId}
qi.Limit = pagesize
qi.Offset = pagesize * page
qi.Order = "ufl.create_time desc"
rows, err := db.Select("user_favorite_link", qi)
if err != nil {
goku.Logger().Errorln(err.Error())
return nil
}
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.CommentCount, &link.CreateTime)
if err != nil {
goku.Logger().Errorln(err.Error())
return nil
}
links = append(links, link)
}
return links
}
// 获取用户关注的话题列表
func User_GetFollowTopics(userId int64, page, pagesize int) ([]Topic, error) {
var db *goku.MysqlDB = GetDB()
defer db.Close()
page, pagesize = utils.PageCheck(page, pagesize)
qi := goku.SqlQueryInfo{}
qi.Fields = "t.id, t.name, t.description, t.pic"
qi.Join = " tf INNER JOIN `topic` t ON tf.topic_id=t.id"
qi.Where = "tf.user_id=?"
qi.Params = []interface{}{userId}
qi.Limit = pagesize
qi.Offset = pagesize * page
qi.Order = "t.id desc"
rows, err := db.Select("topic_follow", qi)
if err != nil {
goku.Logger().Errorln(err.Error())
return nil, err
}
defer rows.Close()
topics := make([]Topic, 0)
for rows.Next() {
topic := Topic{}
err = rows.Scan(&topic.Id, &topic.Name, &topic.Description, &topic.Pic)
if err != nil {
goku.Logger().Errorln(err.Error())
return nil, err
}
topics = append(topics, topic)
}
return topics, nil
}
func Topic_SearchByName(name string) ([]Topic, error) {
var db *goku.MysqlDB = GetDB()
// db.Debug = true
defer db.Close()
qi := goku.SqlQueryInfo{}
qi.Fields = "`id`,`name`,`name_lower`,`description`,`pic`,`click_count`,`follower_count`,`link_count`"
qi.Where = "name_lower LIKE ?" //"name_lower LIKE '%" + strings.ToLower(name) + "%'"
qi.Params = []interface{}{strings.ToLower(name) + "%"} //"%" +
qi.Limit = 10
qi.Offset = 0
qi.Order = "link_count DESC"
rows, err := db.Select("topic", qi)
topics := make([]Topic, 0)
if err != nil {
goku.Logger().Errorln(err.Error())
return topics, err
}
for rows.Next() {
topic := Topic{}
err = rows.Scan(&topic.Id, &topic.Name, &topic.NameLower, &topic.Description, &topic.Pic, &topic.ClickCount, &topic.FollowerCount, &topic.LinkCount)
if err != nil {
goku.Logger().Errorln(err.Error())
return topics, err
}
topics = append(topics, topic)
}
return topics, nil
}
// 获取关注topicId的用户列表
func Topic_GetFollowers(topicId int64, page, pagesize int) ([]User, error) {
var db *goku.MysqlDB = GetDB()
defer db.Close()
page, pagesize = utils.PageCheck(page, pagesize)
qi := goku.SqlQueryInfo{}
qi.Fields = "u.id, u.name, u.email, u.user_pic"
qi.Join = " tf INNER JOIN `user` u ON tf.user_id=u.id"
qi.Where = "tf.topic_id=?"
qi.Params = []interface{}{topicId}
qi.Limit = pagesize
qi.Offset = pagesize * page
qi.Order = "u.id desc"
rows, err := db.Select("topic_follow", qi)
if err != nil {
goku.Logger().Errorln(err.Error())
return nil, err
}
defer rows.Close()
users := make([]User, 0)
for rows.Next() {
user := User{}
err = rows.Scan(&user.Id, &user.Name, &user.Email, &user.UserPic)
if err != nil {
goku.Logger().Errorln(err.Error())
return nil, err
}
users = append(users, user)
}
return users, nil
}
// 关注好友的最新链接的未读数
func NewestLinkUnread_Friends(userId, lastReadLinkId int64) (int64, error) {
if userId < 1 {
return 0, nil
}
var db *goku.MysqlDB = GetDB()
defer db.Close()
qi := goku.SqlQueryInfo{}
qi.Where = "`user_id`=? and `link_id`>?"
qi.Params = []interface{}{userId, lastReadLinkId}
qi.Fields = "count(*)"
tableName := LinkForUser_TableName(userId)
rows, err := db.Select(tableName, qi)
var unreadCount int64
if err != nil {
goku.Logger().Errorln(err.Error())
return 0, err
}
if rows.Next() {
err = rows.Scan(&unreadCount)
if err != nil {
goku.Logger().Errorln(err.Error())
return 0, err
}
}
return unreadCount, nil
}
// 获取收到的评论列表
// @page: 从1开始
// @return: comments, total-count, err
func CommentForUser_GetByPage(userId int64, page, pagesize int, order string) ([]Comment, int64, error) {
var db *goku.MysqlDB = GetDB()
defer db.Close()
page, pagesize = utils.PageCheck(page, pagesize)
qi := goku.SqlQueryInfo{}
qi.Limit = pagesize
qi.Offset = page * pagesize
qi.Where = "cfu.user_id=?"
qi.Join = " cfu INNER JOIN `comment` c ON cfu.comment_id=c.id"
qi.Fields = `c.id, c.user_id, c.link_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`
if order == "" {
qi.Order = "create_time desc"
} else {
qi.Order = order
}
qi.Params = []interface{}{userId}
rows, err := db.Select("comment_for_user", qi)
if err != nil {
goku.Logger().Errorln(err.Error())
return nil, 0, err
}
defer rows.Close()
comments := make([]Comment, 0)
for rows.Next() {
c := Comment{}
err = rows.Scan(&c.Id, &c.UserId, &c.LinkId, &c.ParentPath, &c.ChildrenCount,
&c.TopParentId, &c.ParentId, &c.Deep, &c.Status, &c.Content,
&c.CreateTime, &c.VoteUp, &c.VoteDown, &c.RedditScore)
if err != nil {
goku.Logger().Errorln(err.Error())
return nil, 0, err
}
comments = append(comments, c)
}
total, err := db.Count("comment_for_user", "user_id=?", userId)
if err != nil {
goku.Logger().Errorln(err.Error())
}
return comments, total, nil
}
// 获取link的评论
func Comment_ForLink(linkId int64) []Comment {
var db *goku.MysqlDB = GetDB()
defer db.Close()
qi := goku.SqlQueryInfo{}
qi.Where = "`link_id`=?"
qi.Params = []interface{}{linkId}
qi.Order = "id asc"
var comments []Comment
err := db.GetStructs(&comments, qi)
if err != nil {
goku.Logger().Errorln(err.Error())
return nil
}
return comments
}
// 获取属于某用户的link
// @page: 从1开始
// @orderType: 排序类型, hot:热门, hotc:热议, time:最新, vote:投票得分, ctvl:争议
func Link_ForUser(userId int64, orderType string, page, pagesize int) ([]Link, error) {
var db *goku.MysqlDB = GetDB()
defer db.Close()
page, pagesize = utils.PageCheck(page, pagesize)
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 = " ul INNER JOIN `link` l ON ul.link_id=l.id"
qi.Where = "ul.user_id=?"
qi.Params = []interface{}{userId}
qi.Limit = pagesize
qi.Offset = pagesize * page
switch orderType {
case golink.ORDER_TYPE_TIME: // 最新
qi.Order = "l.id desc"
case golink.ORDER_TYPE_HOTC: // 热议
qi.Order = "l.comment_count desc, id desc"
case golink.ORDER_TYPE_CTVL: // 争议
qi.Order = "l.dispute_score desc, id desc"
case golink.ORDER_TYPE_VOTE: // 得分
qi.Order = "l.vote_up desc, id desc"
default:
qi.Order = "l.reddit_score desc, id desc"
}
rows, err := db.Select(LinkForUser_TableName(userId), 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
}
// 根据id列表获取link
func Link_GetByIdList(searchItems []utils.SearchHitItem) ([]Link, error) {
hashTable := map[int64]*Link{}
var db *goku.MysqlDB = GetDB()
defer db.Close()
var strLinkIdList string
for _, item := range searchItems {
strLinkIdList += item.Id + ","
}
strLinkIdList += "0"
qi := goku.SqlQueryInfo{}
qi.Fields = "id, user_id, title, context, topics, vote_up, vote_down, view_count, comment_count, create_time, status"
qi.Where = "id IN(" + strLinkIdList + ")"
rows, err := db.Select("link", 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.CommentCount, &link.CreateTime, &link.Status)
if err != nil {
goku.Logger().Errorln(err.Error())
return nil, err
}
hashTable[link.Id] = link
}
for _, item := range searchItems {
linkId, err := strconv.ParseInt(item.Id, 10, 64)
link := hashTable[linkId]
if err == nil && link != nil {
links = append(links, *link)
}
}
return links, nil
}
func User_GetByIds(ids []int64) ([]User, error) {
var db *goku.MysqlDB = GetDB()
defer db.Close()
users := []User{}
qi := goku.SqlQueryInfo{}
sids := ""
for _, id := range ids {
sids += "," + strconv.FormatInt(id, 10)
}
qi.Where = "id in (" + sids[1:] + ")"
// qi.Params = []interface{}{ids}
err := db.GetStructs(&users, qi)
if err != nil {
goku.Logger().Errorln("User_GetByIds error:", err.Error())
return nil, err
}
return users, nil
}
// 获取用户关注的粉丝列表
func UserFollow_Followers(userId int64, page, pagesize int) ([]User, error) {
if page < 1 {
page = 1
}
page = page - 1
if pagesize == 0 {
pagesize = 20
}
var db *goku.MysqlDB = GetDB()
defer db.Close()
qi := goku.SqlQueryInfo{}
qi.Fields = "u.id, u.name, u.email, u.user_pic, u.follower_count, u.link_count, u.topic_count, u.friend_count, u.ftopic_count"
qi.Join = " uf INNER JOIN `user` u ON uf.user_id=u.id"
qi.Where = "uf.follow_id=?"
qi.Params = []interface{}{userId}
qi.Limit = pagesize
qi.Offset = pagesize * page
// qi.Order = "u.id desc"
qi.Order = "uf.create_time desc"
rows, err := db.Select("user_follow", qi)
if err != nil {
goku.Logger().Errorln(err.Error())
return nil, err
}
defer rows.Close()
users := make([]User, 0)
for rows.Next() {
user := User{}
err = rows.Scan(&user.Id, &user.Name, &user.Email, &user.UserPic,
&user.FollowerCount, &user.LinkCount, &user.TopicCount, &user.FriendCount, &user.FtopicCount)
if err != nil {
goku.Logger().Errorln(err.Error())
return nil, err
}
users = append(users, user)
}
return users, nil
}
// 获取由用户发布的link
// @page: 从1开始
func Link_ByUser(userId int64, page, pagesize int) []Link {
var db *goku.MysqlDB = GetDB()
defer db.Close()
page, pagesize = utils.PageCheck(page, pagesize)
qi := goku.SqlQueryInfo{}
qi.Limit = pagesize
qi.Offset = page * pagesize
qi.Where = "`user_id`=? and `status`=0"
qi.Params = []interface{}{userId}
qi.Order = "id desc"
var links []Link
err := db.GetStructs(&links, qi)
if err != nil {
goku.Logger().Errorln(err.Error())
return nil
}
return links
}
// 将linkid推送给userid的所有粉丝
func LinkForUser_ToUserFollowers(userId, linkId int64) error {
db := GetDB()
defer db.Close()
qi := goku.SqlQueryInfo{}
qi.Fields = "`user_id`"
qi.Where = "`follow_id`=?"
qi.Params = []interface{}{userId}
rows, err := db.Select("user_follow", qi)
if err != nil {
goku.Logger().Errorln(err.Error())
return err
}
var uid int64
for rows.Next() {
err = rows.Scan(&uid)
if err == nil && uid > 0 {
linkForUser_AddWithDb(db, uid, linkId, LinkForUser_ByUser)
}
}
return 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
}
// @page: 从1开始
// @orderType: 排序类型, hot:热门, hotc:热议, time:最新, vote:投票得分, ctvl:争议
// @dataType: 2:热门;
// 3:争议[3:全部时间；10:这个小时；11:今天；12:这周；13:这个月；14:今年];
// [投票时间范围: 4:全部时间；5:这个小时；6:今天；7:这周；8:这个月；9:今年]
func LinkForHome_GetByPage(orderType string, dataType, page, pagesize int) ([]Link, error) {
if page < 1 {
page = 1
}
page = page - 1
if pagesize == 0 {
pagesize = 20
}
var db *goku.MysqlDB = GetDB()
defer db.Close()
var rows *sql.Rows
var err error
if orderType != golink.ORDER_TYPE_TIME {
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.comment_count, l.create_time, l.status"
qi.Join = " lfh INNER JOIN `link` l ON lfh.link_id=l.id"
qi.Where = "lfh.data_type=?"
qi.Limit = pagesize
qi.Offset = pagesize * page
switch orderType {
case golink.ORDER_TYPE_HOTC: // 热议
qi.Order = "l.comment_count desc, lfh.link_id desc"
dataType = 2
case golink.ORDER_TYPE_CTVL: // 争议
qi.Order = "lfh.score DESC,lfh.link_id desc"
dataType = 3
case golink.ORDER_TYPE_VOTE: // 得分
qi.Order = "lfh.score desc, lfh.link_id desc"
dataType = 4
default:
qi.Order = "lfh.score desc, lfh.link_id desc"
dataType = 2
}
qi.Params = []interface{}{dataType}
rows, err = db.Select("tui_link_for_home", qi)
if err != nil {
goku.Logger().Errorln(err.Error())
return nil, err
}
defer rows.Close()
} else {
qi := goku.SqlQueryInfo{}
qi.Fields = "id, user_id, title, context, topics, vote_up, vote_down, view_count, comment_count, create_time, status"
qi.Where = "status=0"
qi.Limit = pagesize
qi.Offset = pagesize * page
qi.Order = "id desc"
rows, err = db.Select("link", 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.CommentCount, &link.CreateTime, &link.Status)
if err != nil {
goku.Logger().Errorln(err.Error())
return nil, err
}
links = append(links, link)
}
return links, nil
}
// 转换为用于view显示用的实例
func Link_ToVLink(links []Link, ctx *goku.HttpContext) []VLink {
if links == nil || len(links) < 1 {
return nil
}
var userId int64
if user, ok := ctx.Data["user"].(*User); ok && user != nil {
userId = user.Id
}
l := len(links)
vlinks := make([]VLink, l, l)
uids := make([]string, l, l)
lids := make([]string, l, l)
lindex := make(map[int64]*VLink)
for i, link := range links {
uids[i] = strconv.FormatInt(link.UserId, 10)
lids[i] = strconv.FormatInt(link.Id, 10)
vlinks[i] = VLink{Link: link}
lindex[link.Id] = &(vlinks[i])
}
var db *goku.MysqlDB = GetDB()
defer db.Close()
// 添加用户信息
userIndex := make(map[int64]*User)
qi := goku.SqlQueryInfo{}
qi.Where = fmt.Sprintf("`id` in (%v)", strings.Join(uids, ","))
var users []User
err := db.GetStructs(&users, qi)
if err != nil {
goku.Logger().Errorln(err.Error())
} else if users != nil {
for i, _ := range users {
user := &users[i]
userIndex[user.Id] = user
}
}
for i, _ := range vlinks {
link := &vlinks[i]
if user, ok := userIndex[link.UserId]; ok {
link.user = user
if user.Id == userId {
link.SharedByMe = true
}
}
}
// 添加投票信息
if userId > 0 {
qi = goku.SqlQueryInfo{}
qi.Where = fmt.Sprintf("`user_id`=%v AND `link_id` in (%v)", userId, strings.Join(lids, ","))
var srs []LinkSupportRecord
err = db.GetStructs(&srs, qi)
if err != nil {
goku.Logger().Errorln(err.Error())
} else if srs != nil {
for _, sr := range srs {
if sr.Score == 1 {
lindex[sr.LinkId].VoteUped = true
} else if sr.Score == -1 {
lindex[sr.LinkId].VoteDowned = true
}
}
}
// 添加收藏信息
qi.Fields = "link_id"
rows, err := db.Select("user_favorite_link", qi)
if err != nil {
goku.Logger().Errorln(err.Error())
} else {
var linkId int64
for rows.Next() {
err = rows.Scan(&linkId)
if err != nil {
goku.Logger().Errorln(err.Error())
continue
}
lindex[linkId].Favorited = true
}
}
}
return vlinks
}