def get_feed(user, page, ordered_by_votes):
"""
Get paginated list of posts from communities that a given user is a member of from
the database.
"""
ordered_by = Post.date_created.desc()
if ordered_by_votes:
ordered_by = db.literal_column("votes").desc()
posts = (db.session.query(
Post.title,
Post.post,
Post.date_created,
db.func.coalesce(db.func.sum(PostVote.vote), 0).label("votes"),
AppUser.username,
Community.name.label("community_name"),
).outerjoin(PostVote, Post.id == PostVote.post_id).join(
AppUser, Post.user_id == AppUser.id).join(
Community, Post.community_id == Community.id).join(
CommunityMember,
Post.community_id == CommunityMember.community_id).filter(
CommunityMember.user_id == user.id).group_by(
Post.id, AppUser.id,
Community.id).order_by(ordered_by).paginate(
page=page, per_page=5))
return posts
def get_communities_by_membership(page):
"""
Gets paginated list of communities by number of members from the database.
"""
communities = (db.session.query(
Community.id,
Community.name,
Community.description,
Community.date_created,
Community.user_id,
AppUser.username,
db.func.count(CommunityMember.id).label("community_members"),
).join(AppUser, Community.user_id == AppUser.id).outerjoin(
CommunityMember,
Community.id == CommunityMember.community_id).group_by(
Community.id, AppUser.id).order_by(
db.literal_column("community_members").desc()).paginate(
page=page, per_page=5))
return communities
def get_community_posts(community_id, page, ordered_by_votes):
"""
Gets paginated list of posts from a specified community from the database.
"""
ordered_by = Post.date_created.desc()
if ordered_by_votes:
ordered_by = db.literal_column("votes").desc()
posts = (db.session.query(
Post.title,
Post.post,
Post.date_created,
db.func.coalesce(db.func.sum(PostVote.vote), 0).label("votes"),
AppUser.username,
).outerjoin(PostVote, Post.id == PostVote.post_id).join(
AppUser, Post.user_id == AppUser.id).filter(
Post.community_id == community_id).group_by(
Post.id, AppUser.id).order_by(ordered_by).paginate(page=page,
per_page=5))
return posts
def get_post_replies(post_id, page, ordered_by_votes):
"""
Gets paginated list of replies for a specified post from the database.
"""
ordered_by = Reply.date_created.desc()
if ordered_by_votes:
ordered_by = db.literal_column("votes").desc()
replies = (
db.session.query(
Reply.id,
Reply.reply,
Reply.user_id,
Reply.date_created,
db.func.coalesce(db.func.sum(ReplyVote.vote), 0).label("votes"),
AppUser.username,
)
.join(AppUser, Reply.user_id == AppUser.id)
.outerjoin(ReplyVote, Reply.id == ReplyVote.reply_id)
.filter(Reply.post_id == post_id)
.group_by(Reply.id, AppUser.id)
.order_by(ordered_by)
.paginate(page=page, per_page=5)
)
return replies