def topics():
forum_id = request.args.get('forumId', type=int)
page = request.args.get('pageIndex', 1, type=int)
perpage = request.args.get('pageSize', 10, type=int)
keywords = request.args.get('keywords')
p = Praises.query.filter(Praises.status == 1).subquery()
c = Comments.query.filter(Comments.status == 1).subquery()
query = db.Query([Topics, func.count(p.c.user_id.distinct()).label('praisenum'), \
func.count(c.c.comment_id.distinct()).label('commentnum'), Forums.name], db.session()) \
.filter(Topics.forum_id == forum_id, Topics.status >= 1) \
.outerjoin(p, p.c.topic_id == Topics.topic_id) \
.outerjoin(c, c.c.topic_id == Topics.topic_id) \
.join(Forums, Forums.forum_id == Topics.forum_id) \
.group_by(Topics.topic_id).order_by(Topics.last_modify_time.desc())
if keywords:
keywords = keywords.split(' ')
topics = []
for keyword in keywords:
topics.extend(
query.filter(
or_(Topics.content.like('%' + keyword + '%'),
Topics.title.like('%' + keyword + '%'))).all())
pagenum = int(math.ceil(len(topics) / perpage))
try:
topics = topics[(page - 1) * perpage:page *
perpage] if page < pagenum else topics[(page - 1) *
perpage:]
except:
topics = topics[0:perpage] if len(topics) > perpage else topics
else:
query = query.paginate(page, perpage, False)
topics = query.items
pagenum = query.pages
user_ids = [t.Topics.user_id for t in topics]
users = {
a.uid: (a.nickname, a.profileUrl)
for a in Account.query.filter(Account.uid.in_(user_ids)).all()
}
forumName = Forums.query.filter(Forums.forum_id == forum_id).first().name
return jsonify({
'result': 'true',
'msg': {
'forumName': forumName,
'page': page,
'pagenum': pagenum,
'basics': [TopicNode(t, users).pack_res() for t in topics]
}
})
def newsdetail():
news_id = request.args.get('news_id', 1, type=int)
detail = db.Query([NewsBasic, NewsDetail], db.session()).filter(
NewsBasic.id == news_id, NewsBasic.articleId == NewsDetail.id).first()
if detail.NewsBasic.provinceId != 'all':
provinceName = db.Query(Province.province_name, db.session()).filter(
Province.province_id == detail.NewsBasic.provinceId).scalar()
if detail.NewsBasic.majorId != 'all':
majorId = str(detail.NewsBasic.majorId).split('_')[1]
majorName = db.Query(Major.major_name, db.session()).filter(
Major.major_id == majorId,
Major.province_name == provinceName).first()[0]
else:
majorName = '全部'
else:
provinceName = '全部'
majorName = '全部'
return jsonify({
'result':
'true',
'msg':
NewsDetailNode(detail, majorName, provinceName).pack_res()
})
def topic():
topic_id = request.args.get('topicId', type=int)
p = Praises.query.filter(Praises.status == 1).subquery()
c = Comments.query.filter(Comments.status == 1).subquery()
topic = db.Query([Topics, func.count(p.c.user_id.distinct()).label('praisenum'), func.count(c.c.comment_id.distinct()).label('commentnum'), Forums.name], db.session()) \
.filter(Topics.topic_id == topic_id) \
.outerjoin(p, p.c.topic_id == Topics.topic_id) \
.outerjoin(c, c.c.topic_id == Topics.topic_id) \
.join(Forums, Forums.forum_id == Topics.forum_id) \
.group_by(Topics.topic_id).order_by(Topics.last_modify_time.desc()).first()
users = {
a.uid: (a.nickname, a.profileUrl)
for a in Account.query.filter(
Account.uid == topic.Topics.user_id).all()
}
return jsonify({
'result': 'true',
'msg': TopicNode(topic, users).pack_res()
})
def replyComment():
if request.form['user_id']:
topic_id = int(request.form['topic_id'])
reply_id = int(request.form['reply_id'])
parentcomment = Comments.query.get(reply_id)
reply_floor_no = db.Query(
func.count(Comments.comment_id),
db.session()).filter(Comments.topic_id == topic_id,
Comments.comment_id <= reply_id).first()[0]
comment = Comments(content=request.form['content'], pub_time=func.unix_timestamp(), images=request.form['images'], status=1, \
topic_id=topic_id, forum_id=int(request.form['forum_id']), user_id=request.form['user_id'], user_name=request.form['user_name'], \
reply_id=reply_id, reply_floor_no=reply_floor_no, reply_user_id=parentcomment.user_id, reply_user_name=parentcomment.user_name, \
reply_content=parentcomment.content)
db.session.add(comment)
db.session.commit()
return jsonify({'result': 'true'})
else:
return jsonify({
'result': 'false',
'msg': 'The request does not have a user_id'
})
def getcomments():
articleId = request.args.get('articleId', 1, type=int)
page = request.args.get('page', 1, type=int)
perpage = request.args.get('perpage', 10, type=int)
#子查询方法
ParentComment = aliased(NewsComment)
ParentUser = aliased(Account)
query = db.Query([NewsComment, Account.nickname, Account.profileUrl, ParentComment.content.label('parentcomment'), \
ParentUser.nickname.label('parentuser')],db.session()).filter(NewsComment.articleId == articleId) \
.outerjoin(Account, Account.uid == NewsComment.uid).outerjoin(ParentComment, ParentComment.id == NewsComment.parentId). \
outerjoin(ParentUser, ParentUser.uid == ParentComment.uid).order_by(NewsComment.id).paginate(page, perpage, False)
comments = query.items
pagenum = query.pages
return jsonify({
'result': 'true',
'msg': {
'comments': [CommentNode(c).pack_res() for c in comments]
},
'page': page,
'pagenum': pagenum
})