微信小程序旅游助手社区功能后端设计与实现

微信小程序旅游助手社区功能后端设计与实现

下面我们将详细设计并实现微信小程序旅游助手的社区功能后端模块，包括发帖、点赞、评论等核心功能。

1. 数据库设计

1.1 社区核心表结构

-- 社区帖子表
CREATE TABLE CommunityPosts (
    PostID BIGINT IDENTITY(1,1) PRIMARY KEY,
    UserID INT NOT NULL,
    Title NVARCHAR(200) NOT NULL,
    Content NVARCHAR(MAX) NOT NULL,
    PostType NVARCHAR(20) NOT NULL DEFAULT 'normal', -- normal, question, experience, share
    Location NVARCHAR(100), -- 发帖位置
    Latitude DECIMAL(10, 8),
    Longitude DECIMAL(11, 8),
    ViewCount INT DEFAULT 0,
    LikeCount INT DEFAULT 0,
    CommentCount INT DEFAULT 0,
    ShareCount INT DEFAULT 0,
    CollectionCount INT DEFAULT 0,
    IsAnonymous BIT DEFAULT 0,
    Status NVARCHAR(20) DEFAULT 'published', -- draft, published, deleted, banned
    IsTop BIT DEFAULT 0, -- 是否置顶
    IsFeatured BIT DEFAULT 0, -- 是否精华
    CreatedAt DATETIME2 DEFAULT GETUTCDATE(),
    UpdatedAt DATETIME2 DEFAULT GETUTCDATE(),
    
    CONSTRAINT FK_CommunityPosts_UserID FOREIGN KEY (UserID) REFERENCES Users(UserID),
    CONSTRAINT CHK_CommunityPosts_PostType CHECK (PostType IN ('normal', 'question', 'experience', 'share')),
    CONSTRAINT CHK_CommunityPosts_Status CHECK (Status IN ('draft', 'published', 'deleted', 'banned'))
);

-- 帖子图片表
CREATE TABLE PostImages (
    ImageID BIGINT IDENTITY(1,1) PRIMARY KEY,
    PostID BIGINT NOT NULL,
    ImageURL NVARCHAR(500) NOT NULL,
    ThumbnailURL NVARCHAR(500), -- 缩略图
    ImageOrder INT DEFAULT 0,
    Description NVARCHAR(200),
    CreatedAt DATETIME2 DEFAULT GETUTCDATE(),
    
    CONSTRAINT FK_PostImages_PostID FOREIGN KEY (PostID) REFERENCES CommunityPosts(PostID)
);

-- 帖子标签关联表
CREATE TABLE PostTags (
    PostTagID BIGINT IDENTITY(1,1) PRIMARY KEY,
    PostID BIGINT NOT NULL,
    TagName NVARCHAR(50) NOT NULL,
    CreatedAt DATETIME2 DEFAULT GETUTCDATE(),
    
    CONSTRAINT FK_PostTags_PostID FOREIGN KEY (PostID) REFERENCES CommunityPosts(PostID),
    CONSTRAINT UQ_PostTags_PostTag UNIQUE (PostID, TagName)
);

-- 评论表
CREATE TABLE Comments (
    CommentID BIGINT IDENTITY(1,1) PRIMARY KEY,
    PostID BIGINT NOT NULL,
    UserID INT NOT NULL,
    ParentCommentID BIGINT, -- 父评论ID，支持回复
    Content NVARCHAR(1000) NOT NULL,
    LikeCount INT DEFAULT 0,
    IsAuthor BIT DEFAULT 0, -- 是否是帖子作者
    Status NVARCHAR(20) DEFAULT 'published', -- published, deleted
    CreatedAt DATETIME2 DEFAULT GETUTCDATE(),
    UpdatedAt DATETIME2 DEFAULT GETUTCDATE(),
    
    CONSTRAINT FK_Comments_PostID FOREIGN KEY (PostID) REFERENCES CommunityPosts(PostID),
    CONSTRAINT FK_Comments_UserID FOREIGN KEY (UserID) REFERENCES Users(UserID),
    CONSTRAINT FK_Comments_ParentCommentID FOREIGN KEY (ParentCommentID) REFERENCES Comments(CommentID),
    CONSTRAINT CHK_Comments_Status CHECK (Status IN ('published', 'deleted'))
);

-- 点赞表
CREATE TABLE Likes (
    LikeID BIGINT IDENTITY(1,1) PRIMARY KEY,
    PostID BIGINT NULL, -- 可以为空，支持评论点赞
    CommentID BIGINT NULL, -- 可以为空，支持帖子点赞
    UserID INT NOT NULL,
    LikeType NVARCHAR(20) NOT NULL, -- post, comment
    CreatedAt DATETIME2 DEFAULT GETUTCDATE(),
    
    CONSTRAINT FK_Likes_PostID FOREIGN KEY (PostID) REFERENCES CommunityPosts(PostID),
    CONSTRAINT FK_Likes_CommentID FOREIGN KEY (CommentID) REFERENCES Comments(CommentID),
    CONSTRAINT FK_Likes_UserID FOREIGN KEY (UserID) REFERENCES Users(UserID),
    CONSTRAINT CHK_Likes_LikeType CHECK (LikeType IN ('post', 'comment')),
    -- 确保一个用户对同一内容只能点赞一次
    CONSTRAINT UQ_Likes_UserPost UNIQUE (UserID, PostID) WHERE PostID IS NOT NULL AND CommentID IS NULL,
    CONSTRAINT UQ_Likes_UserComment UNIQUE (UserID, CommentID) WHERE CommentID IS NOT NULL AND PostID IS NULL
);

-- 收藏表
CREATE TABLE Collections (
    CollectionID BIGINT IDENTITY(1,1) PRIMARY KEY,
    UserID INT NOT NULL,
    PostID BIGINT NOT NULL,
    CreatedAt DATETIME2 DEFAULT GETUTCDATE(),
    
    CONSTRAINT FK_Collections_UserID FOREIGN KEY (UserID) REFERENCES Users(UserID),
    CONSTRAINT FK_Collections_PostID FOREIGN KEY (PostID) REFERENCES CommunityPosts(PostID),
    CONSTRAINT UQ_Collections_UserPost UNIQUE (UserID, PostID)
);

-- 关注表
CREATE TABLE Follows (
    FollowID BIGINT IDENTITY(1,1) PRIMARY KEY,
    FollowerID INT NOT NULL, -- 关注者
    FollowingID INT NOT NULL, -- 被关注者
    CreatedAt DATETIME2 DEFAULT GETUTCDATE(),
    
    CONSTRAINT FK_Follows_FollowerID FOREIGN KEY (FollowerID) REFERENCES Users(UserID),
    CONSTRAINT FK_Follows_FollowingID FOREIGN KEY (FollowingID) REFERENCES Users(UserID),
    CONSTRAINT UQ_Follows_FollowerFollowing UNIQUE (FollowerID, FollowingID),
    CONSTRAINT CHK_Follows_NotSelf CHECK (FollowerID != FollowingID)
);

-- 消息通知表
CREATE TABLE Notifications (
    NotificationID BIGINT IDENTITY(1,1) PRIMARY KEY,
    UserID INT NOT NULL, -- 接收用户
    FromUserID INT, -- 发送用户（系统通知可为空）
    Type NVARCHAR(50) NOT NULL, -- like, comment, follow, reply, system
    RelatedPostID BIGINT NULL,
    RelatedCommentID BIGINT NULL,
    Content NVARCHAR(500) NOT NULL,
    IsRead BIT DEFAULT 0,
    CreatedAt DATETIME2 DEFAULT GETUTCDATE(),
    
    CONSTRAINT FK_Notifications_UserID FOREIGN KEY (UserID) REFERENCES Users(UserID),
    CONSTRAINT FK_Notifications_FromUserID FOREIGN KEY (FromUserID) REFERENCES Users(UserID),
    CONSTRAINT FK_Notifications_RelatedPostID FOREIGN KEY (RelatedPostID) REFERENCES CommunityPosts(PostID),
    CONSTRAINT FK_Notifications_RelatedCommentID FOREIGN KEY (RelatedCommentID) REFERENCES Comments(CommentID)
);

1.2 索引优化

-- 社区帖子索引
CREATE NONCLUSTERED INDEX IX_CommunityPosts_UserID ON CommunityPosts(UserID) WHERE Status = 'published';
CREATE NONCLUSTERED INDEX IX_CommunityPosts_PostType ON CommunityPosts(PostType) WHERE Status = 'published';
CREATE NONCLUSTERED INDEX IX_CommunityPosts_CreatedAt ON CommunityPosts(CreatedAt) WHERE Status = 'published';
CREATE NONCLUSTERED INDEX IX_CommunityPosts_LikeCount ON CommunityPosts(LikeCount) WHERE Status = 'published';
CREATE NONCLUSTERED INDEX IX_CommunityPosts_IsTop ON CommunityPosts(IsTop) WHERE Status = 'published' AND IsTop = 1;
CREATE NONCLUSTERED INDEX IX_CommunityPosts_IsFeatured ON CommunityPosts(IsFeatured) WHERE Status = 'published' AND IsFeatured = 1;

-- 覆盖索引优化帖子列表查询
CREATE NONCLUSTERED INDEX IX_CommunityPosts_List ON CommunityPosts (
    CreatedAt, PostType, LikeCount
) INCLUDE (
    UserID, Title, ViewCount, CommentCount, IsTop, IsFeatured
) WHERE Status = 'published';

-- 评论索引
CREATE NONCLUSTERED INDEX IX_Comments_PostID ON Comments(PostID) WHERE Status = 'published';
CREATE NONCLUSTERED INDEX IX_Comments_UserID ON Comments(UserID) WHERE Status = 'published';
CREATE NONCLUSTERED INDEX IX_Comments_ParentCommentID ON Comments(ParentCommentID) WHERE Status = 'published' AND ParentCommentID IS NOT NULL;
CREATE NONCLUSTERED INDEX IX_Comments_CreatedAt ON Comments(CreatedAt) WHERE Status = 'published';

-- 点赞索引
CREATE NONCLUSTERED INDEX IX_Likes_PostID ON Likes(PostID);
CREATE NONCLUSTERED INDEX IX_Likes_CommentID ON Likes(CommentID);
CREATE NONCLUSTERED INDEX IX_Likes_UserID ON Likes(UserID);

-- 收藏索引
CREATE NONCLUSTERED INDEX IX_Collections_UserID ON Collections(UserID);
CREATE NONCLUSTERED INDEX IX_Collections_PostID ON Collections(PostID);

-- 关注索引
CREATE NONCLUSTERED INDEX IX_Follows_FollowerID ON Follows(FollowerID);
CREATE NONCLUSTERED INDEX IX_Follows_FollowingID ON Follows(FollowingID);

-- 通知索引
CREATE NONCLUSTERED INDEX IX_Notifications_UserID ON Notifications(UserID) WHERE IsRead = 0;
CREATE NONCLUSTERED INDEX IX_Notifications_CreatedAt ON Notifications(CreatedAt);
CREATE NONCLUSTERED INDEX IX_Notifications_Type ON Notifications(Type);

2. Flask 后端实现

2.1 社区蓝图初始化

# app/__init__.py
from flask import Flask
from flask_sqlalchemy import SQLAlchemy
from flask_jwt_extended import JWTManager
from config import config

db = SQLAlchemy()
jwt = JWTManager()

def create_app(config_name):
    app = Flask(__name__)
    app.config.from_object(config[config_name])
    
    db.init_app(app)
    jwt.init_app(app)
    
    # 注册社区蓝图
    from app.community import community_bp
    app.register_blueprint(community_bp, url_prefix='/api/community')
    
    return app

2.2 社区模型定义

# app/models/community.py
from app import db
from datetime import datetime
from sqlalchemy import event

class CommunityPost(db.Model):
    __tablename__ = 'CommunityPosts'
    
    PostID = db.Column(db.BigInteger, primary_key=True)
    UserID = db.Column(db.Integer, db.ForeignKey('Users.UserID'), nullable=False)
    Title = db.Column(db.String(200), nullable=False)
    Content = db.Column(db.Text, nullable=False)
    PostType = db.Column(db.String(20), nullable=False, default='normal')
    Location = db.Column(db.String(100))
    Latitude = db.Column(db.Numeric(10, 8))
    Longitude = db.Column(db.Numeric(11, 8))
    ViewCount = db.Column(db.Integer, default=0)
    LikeCount = db.Column(db.Integer, default=0)
    CommentCount = db.Column(db.Integer, default=0)
    ShareCount = db.Column(db.Integer, default=0)
    CollectionCount = db.Column(db.Integer, default=0)
    IsAnonymous = db.Column(db.Boolean, default=False)
    Status = db.Column(db.String(20), default='published')
    IsTop = db.Column(db.Boolean, default=False)
    IsFeatured = db.Column(db.Boolean, default=False)
    CreatedAt = db.Column(db.DateTime, default=datetime.utcnow)
    UpdatedAt = db.Column(db.DateTime, default=datetime.utcnow, onupdate=datetime.utcnow)
    
    # 关系
    user = db.relationship('User', backref=db.backref('posts', lazy='dynamic'))
    images = db.relationship('PostImage', backref='post', lazy='dynamic', cascade='all, delete-orphan')
    tags = db.relationship('PostTag', backref='post', lazy='dynamic', cascade='all, delete-orphan')
    comments = db.relationship('Comment', backref='post', lazy='dynamic')
    likes = db.relationship('Like', backref='post', lazy='dynamic')
    collections = db.relationship('Collection', backref='post', lazy='dynamic')
    
    def to_dict(self, include_content=True, include_user=True):
        data = {
            'post_id': self.PostID,
            'title': self.Title,
            'post_type': self.PostType,
            'location': self.Location,
            'latitude': float(self.Latitude) if self.Latitude else None,
            'longitude': float(self.Longitude) if self.Longitude else None,
            'view_count': self.ViewCount,
            'like_count': self.LikeCount,
            'comment_count': self.CommentCount,
            'share_count': self.ShareCount,
            'collection_count': self.CollectionCount,
            'is_anonymous': self.IsAnonymous,
            'is_top': self.IsTop,
            'is_featured': self.IsFeatured,
            'created_at': self.CreatedAt.isoformat(),
            'updated_at': self.UpdatedAt.isoformat()
        }
        
        if include_content:
            data['content'] = self.Content
            
        if include_user and not self.IsAnonymous:
            data['user'] = {
                'user_id': self.user.UserID,
                'username': self.user.Username,
                'avatar_url': self.user.AvatarURL,
                'display_name': self.user.DisplayName
            }
        elif self.IsAnonymous:
            data['user'] = {
                'user_id': 0,
                'username': '匿名用户',
                'avatar_url': None,
                'display_name': '匿名用户'
            }
            
        return data

class PostImage(db.Model):
    __tablename__ = 'PostImages'
    
    ImageID = db.Column(db.BigInteger, primary_key=True)
    PostID = db.Column(db.BigInteger, db.ForeignKey('CommunityPosts.PostID'), nullable=False)
    ImageURL = db.Column(db.String(500), nullable=False)
    ThumbnailURL = db.Column(db.String(500))
    ImageOrder = db.Column(db.Integer, default=0)
    Description = db.Column(db.String(200))
    CreatedAt = db.Column(db.DateTime, default=datetime.utcnow)
    
    def to_dict(self):
        return {
            'image_id': self.ImageID,
            'image_url': self.ImageURL,
            'thumbnail_url': self.ThumbnailURL,
            'description': self.Description,
            'order': self.ImageOrder
        }

class PostTag(db.Model):
    __tablename__ = 'PostTags'
    
    PostTagID = db.Column(db.BigInteger, primary_key=True)
    PostID = db.Column(db.BigInteger, db.ForeignKey('CommunityPosts.PostID'), nullable=False)
    TagName = db.Column(db.String(50), nullable=False)
    CreatedAt = db.Column(db.DateTime, default=datetime.utcnow)

class Comment(db.Model):
    __tablename__ = 'Comments'
    
    CommentID = db.Column(db.BigInteger, primary_key=True)
    PostID = db.Column(db.BigInteger, db.ForeignKey('CommunityPosts.PostID'), nullable=False)
    UserID = db.Column(db.Integer, db.ForeignKey('Users.UserID'), nullable=False)
    ParentCommentID = db.Column(db.BigInteger, db.ForeignKey('Comments.CommentID'))
    Content = db.Column(db.String(1000), nullable=False)
    LikeCount = db.Column(db.Integer, default=0)
    IsAuthor = db.Column(db.Boolean, default=False)
    Status = db.Column(db.String(20), default='published')
    CreatedAt = db.Column(db.DateTime, default=datetime.utcnow)
    UpdatedAt = db.Column(db.DateTime, default=datetime.utcnow, onupdate=datetime.utcnow)
    
    # 关系
    user = db.relationship('User', backref=db.backref('comments', lazy='dynamic'))
    parent = db.relationship('Comment', remote_side=[CommentID], backref=db.backref('replies', lazy='dynamic'))
    likes = db.relationship('Like', backref='comment', lazy='dynamic')
    
    def to_dict(self, include_replies=False):
        data = {
            'comment_id': self.CommentID,
            'post_id': self.PostID,
            'user': {
                'user_id': self.user.UserID,
                'username': self.user.Username,
                'avatar_url': self.user.AvatarURL,
                'display_name': self.user.DisplayName
            },
            'content': self.Content,
            'like_count': self.LikeCount,
            'is_author': self.IsAuthor,
            'created_at': self.CreatedAt.isoformat(),
            'is_reply': self.ParentCommentID is not None
        }
        
        if include_replies and self.replies:
            data['replies'] = [reply.to_dict() for reply in self.replies.filter_by(Status='published').all()]
            
        return data

class Like(db.Model):
    __tablename__ = 'Likes'
    
    LikeID = db.Column(db.BigInteger, primary_key=True)
    PostID = db.Column(db.BigInteger, db.ForeignKey('CommunityPosts.PostID'))
    CommentID = db.Column(db.BigInteger, db.ForeignKey('Comments.CommentID'))
    UserID = db.Column(db.Integer, db.ForeignKey('Users.UserID'), nullable=False)
    LikeType = db.Column(db.String(20), nullable=False)
    CreatedAt = db.Column(db.DateTime, default=datetime.utcnow)
    
    user = db.relationship('User', backref=db.backref('likes', lazy='dynamic'))

class Collection(db.Model):
    __tablename__ = 'Collections'
    
    CollectionID = db.Column(db.BigInteger, primary_key=True)
    UserID = db.Column(db.Integer, db.ForeignKey('Users.UserID'), nullable=False)
    PostID = db.Column(db.BigInteger, db.ForeignKey('CommunityPosts.PostID'), nullable=False)
    CreatedAt = db.Column(db.DateTime, default=datetime.utcnow)
    
    user = db.relationship('User', backref=db.backref('collections', lazy='dynamic'))

class Follow(db.Model):
    __tablename__ = 'Follows'
    
    FollowID = db.Column(db.BigInteger, primary_key=True)
    FollowerID = db.Column(db.Integer, db.ForeignKey('Users.UserID'), nullable=False)
    FollowingID = db.Column(db.Integer, db.ForeignKey('Users.UserID'), nullable=False)
    CreatedAt = db.Column(db.DateTime, default=datetime.utcnow)
    
    follower = db.relationship('User', foreign_keys=[FollowerID], backref=db.backref('following', lazy='dynamic'))
    following = db.relationship('User', foreign_keys=[FollowingID], backref=db.backref('followers', lazy='dynamic'))

class Notification(db.Model):
    __tablename__ = 'Notifications'
    
    NotificationID = db.Column(db.BigInteger, primary_key=True)
    UserID = db.Column(db.Integer, db.ForeignKey('Users.UserID'), nullable=False)
    FromUserID = db.Column(db.Integer, db.ForeignKey('Users.UserID'))
    Type = db.Column(db.String(50), nullable=False)
    RelatedPostID = db.Column(db.BigInteger, db.ForeignKey('CommunityPosts.PostID'))
    RelatedCommentID = db.Column(db.BigInteger, db.ForeignKey('Comments.CommentID'))
    Content = db.Column(db.String(500), nullable=False)
    IsRead = db.Column(db.Boolean, default=False)
    CreatedAt = db.Column(db.DateTime, default=datetime.utcnow)
    
    user = db.relationship('User', foreign_keys=[UserID], backref=db.backref('notifications', lazy='dynamic'))
    from_user = db.relationship('User', foreign_keys=[FromUserID])
    related_post = db.relationship('CommunityPost', foreign_keys=[RelatedPostID])
    related_comment = db.relationship('Comment', foreign_keys=[RelatedCommentID])
    
    def to_dict(self):
        data = {
            'notification_id': self.NotificationID,
            'type': self.Type,
            'content': self.Content,
            'is_read': self.IsRead,
            'created_at': self.CreatedAt.isoformat()
        }
        
        if self.from_user:
            data['from_user'] = {
                'user_id': self.from_user.UserID,
                'username': self.from_user.Username,
                'avatar_url': self.from_user.AvatarURL
            }
            
        if self.related_post:
            data['related_post'] = {
                'post_id': self.related_post.PostID,
                'title': self.related_post.Title
            }
            
        return data

# 更新帖子计数字段的触发器
@event.listens_for(Comment, 'after_insert')
@event.listens_for(Comment, 'after_delete')
def update_post_comment_count(mapper, connection, target):
    if target.Status == 'published':
        post_table = CommunityPost.__table__
        if mapper.class_ == Comment:
            # 计算新的评论数
            stmt = db.select([db.func.count(Comment.CommentID)]).where(
                db.and_(
                    Comment.PostID == target.PostID,
                    Comment.Status == 'published'
                )
            )
            new_count = connection.execute(stmt).scalar()
            
            # 更新帖子评论数
            connection.execute(
                post_table.update()
                .where(post_table.c.PostID == target.PostID)
                .values(CommentCount=new_count, UpdatedAt=datetime.utcnow())
            )

2.3 社区服务层

# app/services/community_service.py
from app.models.community import CommunityPost, PostImage, PostTag, Comment, Like, Collection, Follow, Notification
from app.models import User
from app import db
from sqlalchemy import or_, and_, func, desc
from datetime import datetime, timedelta

class CommunityService:
    
    @staticmethod
    def create_post(user_id, post_data):
        """创建帖子"""
        try:
            post = CommunityPost(
                UserID=user_id,
                Title=post_data['title'],
                Content=post_data['content'],
                PostType=post_data.get('post_type', 'normal'),
                Location=post_data.get('location'),
                Latitude=post_data.get('latitude'),
                Longitude=post_data.get('longitude'),
                IsAnonymous=post_data.get('is_anonymous', False),
                Status='published'
            )
            
            db.session.add(post)
            db.session.flush()  # 获取PostID
            
            # 处理图片
            if 'images' in post_data:
                for index, image_data in enumerate(post_data['images']):
                    image = PostImage(
                        PostID=post.PostID,
                        ImageURL=image_data['image_url'],
                        ThumbnailURL=image_data.get('thumbnail_url'),
                        Description=image_data.get('description'),
                        ImageOrder=index
                    )
                    db.session.add(image)
            
            # 处理标签
            if 'tags' in post_data:
                for tag_name in post_data['tags']:
                    tag = PostTag(
                        PostID=post.PostID,
                        TagName=tag_name.strip()
                    )
                    db.session.add(tag)
            
            db.session.commit()
            
            # 返回完整的帖子信息
            return CommunityService.get_post_detail(post.PostID, user_id)
            
        except Exception as e:
            db.session.rollback()
            raise e
    
    @staticmethod
    def get_posts(page=1, per_page=20, filters=None, sort_by='created_at', user_id=None):
        """获取帖子列表"""
        query = CommunityPost.query.filter_by(Status='published')
        
        # 应用过滤器
        if filters:
            if filters.get('post_type'):
                query = query.filter(CommunityPost.PostType == filters['post_type'])
            if filters.get('user_id'):
                query = query.filter(CommunityPost.UserID == filters['user_id'])
            if filters.get('is_featured'):
                query = query.filter(CommunityPost.IsFeatured == True)
            if filters.get('is_top'):
                query = query.filter(CommunityPost.IsTop == True)
            if filters.get('search'):
                search_term = f"%{filters['search']}%"
                query = query.filter(
                    or_(
                        CommunityPost.Title.ilike(search_term),
                        CommunityPost.Content.ilike(search_term)
                    )
                )
            if filters.get('tags'):
                tag_names = [tag.strip() for tag in filters['tags'].split(',')]
                query = query.join(PostTag).filter(PostTag.TagName.in_(tag_names))
            if filters.get('location'):
                location_term = f"%{filters['location']}%"
                query = query.filter(CommunityPost.Location.ilike(location_term))
        
        # 应用排序
        if sort_by == 'hot':
            # 热度排序：点赞数*2 + 评论数*3 + 浏览量*0.1
            query = query.order_by(desc(
                CommunityPost.LikeCount * 2 + 
                CommunityPost.CommentCount * 3 + 
                CommunityPost.ViewCount * 0.1
            ))
        elif sort_by == 'like':
            query = query.order_by(desc(CommunityPost.LikeCount))
        elif sort_by == 'comment':
            query = query.order_by(desc(CommunityPost.CommentCount))
        else:  # created_at
            query = query.order_by(desc(CommunityPost.CreatedAt))
        
        # 分页
        pagination = query.paginate(
            page=page,
            per_page=per_page,
            error_out=False
        )
        
        posts = pagination.items
        
        # 构建返回数据
        posts_data = []
        for post in posts:
            post_data = post.to_dict(include_content=False)
            
            # 添加图片信息
            post_data['images'] = [img.to_dict() for img in post.images.order_by(PostImage.ImageOrder).limit(3).all()]
            
            # 添加标签信息
            post_data['tags'] = [tag.TagName for tag in post.tags.all()]
            
            # 检查用户是否点赞、收藏
            if user_id:
                post_data['is_liked'] = CommunityService.check_post_liked(post.PostID, user_id)
                post_data['is_collected'] = CommunityService.check_post_collected(post.PostID, user_id)
            else:
                post_data['is_liked'] = False
                post_data['is_collected'] = False
                
            posts_data.append(post_data)
        
        return {
            'posts': posts_data,
            'pagination': {
                'page': page,
                'per_page': per_page,
                'total': pagination.total,
                'pages': pagination.pages
            }
        }
    
    @staticmethod
    def get_post_detail(post_id, user_id=None):
        """获取帖子详情"""
        post = CommunityPost.query.filter_by(PostID=post_id, Status='published').first()
        if not post:
            return None
        
        # 增加浏览量
        post.ViewCount += 1
        db.session.commit()
        
        post_data = post.to_dict(include_content=True)
        
        # 添加图片信息
        post_data['images'] = [img.to_dict() for img in post.images.order_by(PostImage.ImageOrder).all()]
        
        # 添加标签信息
        post_data['tags'] = [tag.TagName for tag in post.tags.all()]
        
        # 检查用户是否点赞、收藏
        if user_id:
            post_data['is_liked'] = CommunityService.check_post_liked(post_id, user_id)
            post_data['is_collected'] = CommunityService.check_post_collected(post_id, user_id)
        else:
            post_data['is_liked'] = False
            post_data['is_collected'] = False
            
        return post_data
    
    @staticmethod
    def like_post(post_id, user_id):
        """点赞帖子"""
        try:
            # 检查是否已经点赞
            existing_like = Like.query.filter_by(
                PostID=post_id, 
                UserID=user_id,
                LikeType='post'
            ).first()
            
            if existing_like:
                return {'liked': True, 'like_count': existing_like.post.LikeCount}
            
            # 创建点赞记录
            like = Like(
                PostID=post_id,
                UserID=user_id,
                LikeType='post'
            )
            db.session.add(like)
            
            # 更新帖子点赞数
            post = CommunityPost.query.get(post_id)
            post.LikeCount += 1
            post.UpdatedAt = datetime.utcnow()
            
            # 创建通知（排除自己给自己点赞）
            if post.UserID != user_id:
                notification = Notification(
                    UserID=post.UserID,
                    FromUserID=user_id,
                    Type='like',
                    RelatedPostID=post_id,
                    Content=f'点赞了你的帖子'
                )
                db.session.add(notification)
            
            db.session.commit()
            
            return {'liked': True, 'like_count': post.LikeCount}
            
        except Exception as e:
            db.session.rollback()
            raise e
    
    @staticmethod
    def unlike_post(post_id, user_id):
        """取消点赞帖子"""
        try:
            like = Like.query.filter_by(
                PostID=post_id, 
                UserID=user_id,
                LikeType='post'
            ).first()
            
            if not like:
                return {'liked': False, 'like_count': 0}
            
            # 删除点赞记录
            db.session.delete(like)
            
            # 更新帖子点赞数
            post = CommunityPost.query.get(post_id)
            post.LikeCount = max(0, post.LikeCount - 1)
            post.UpdatedAt = datetime.utcnow()
            
            db.session.commit()
            
            return {'liked': False, 'like_count': post.LikeCount}
            
        except Exception as e:
            db.session.rollback()
            raise e
    
    @staticmethod
    def check_post_liked(post_id, user_id):
        """检查用户是否点赞了帖子"""
        return Like.query.filter_by(
            PostID=post_id, 
            UserID=user_id,
            LikeType='post'
        ).first() is not None
    
    @staticmethod
    def collect_post(post_id, user_id):
        """收藏帖子"""
        try:
            # 检查是否已经收藏
            existing_collection = Collection.query.filter_by(
                PostID=post_id, 
                UserID=user_id
            ).first()
            
            if existing_collection:
                return {'collected': True, 'collection_count': existing_collection.post.CollectionCount}
            
            # 创建收藏记录
            collection = Collection(
                PostID=post_id,
                UserID=user_id
            )
            db.session.add(collection)
            
            # 更新帖子收藏数
            post = CommunityPost.query.get(post_id)
            post.CollectionCount += 1
            post.UpdatedAt = datetime.utcnow()
            
            db.session.commit()
            
            return {'collected': True, 'collection_count': post.CollectionCount}
            
        except Exception as e:
            db.session.rollback()
            raise e
    
    @staticmethod
    def uncollect_post(post_id, user_id):
        """取消收藏帖子"""
        try:
            collection = Collection.query.filter_by(
                PostID=post_id, 
                UserID=user_id
            ).first()
            
            if not collection:
                return {'collected': False, 'collection_count': 0}
            
            # 删除收藏记录
            db.session.delete(collection)
            
            # 更新帖子收藏数
            post = CommunityPost.query.get(post_id)
            post.CollectionCount = max(0, post.CollectionCount - 1)
            post.UpdatedAt = datetime.utcnow()
            
            db.session.commit()
            
            return {'collected': False, 'collection_count': post.CollectionCount}
            
        except Exception as e:
            db.session.rollback()
            raise e
    
    @staticmethod
    def check_post_collected(post_id, user_id):
        """检查用户是否收藏了帖子"""
        return Collection.query.filter_by(
            PostID=post_id, 
            UserID=user_id
        ).first() is not None
    
    @staticmethod
    def create_comment(post_id, user_id, comment_data):
        """创建评论"""
        try:
            post = CommunityPost.query.get(post_id)
            if not post or post.Status != 'published':
                raise ValueError('帖子不存在或已被删除')
            
            # 检查是否是回复评论
            parent_comment_id = comment_data.get('parent_comment_id')
            parent_comment = None
            if parent_comment_id:
                parent_comment = Comment.query.get(parent_comment_id)
                if not parent_comment or parent_comment.PostID != post_id:
                    raise ValueError('父评论不存在')
            
            comment = Comment(
                PostID=post_id,
                UserID=user_id,
                ParentCommentID=parent_comment_id,
                Content=comment_data['content'],
                IsAuthor=(user_id == post.UserID)
            )
            
            db.session.add(comment)
            db.session.flush()
            
            # 更新帖子评论数（通过触发器自动更新）
            
            # 创建通知
            # 给帖子作者发通知（排除自己评论自己）
            if post.UserID != user_id:
                notification = Notification(
                    UserID=post.UserID,
                    FromUserID=user_id,
                    Type='comment',
                    RelatedPostID=post_id,
                    RelatedCommentID=comment.CommentID,
                    Content=f'评论了你的帖子'
                )
                db.session.add(notification)
            
            # 如果是回复评论，给父评论作者发通知
            if parent_comment and parent_comment.UserID != user_id:
                notification = Notification(
                    UserID=parent_comment.UserID,
                    FromUserID=user_id,
                    Type='reply',
                    RelatedPostID=post_id,
                    RelatedCommentID=comment.CommentID,
                    Content=f'回复了你的评论'
                )
                db.session.add(notification)
            
            db.session.commit()
            
            return comment.to_dict()
            
        except Exception as e:
            db.session.rollback()
            raise e
    
    @staticmethod
    def get_post_comments(post_id, page=1, per_page=20, user_id=None):
        """获取帖子评论列表"""
        # 获取顶级评论（非回复）
        query = Comment.query.filter_by(
            PostID=post_id, 
            Status='published',
            ParentCommentID=None
        ).order_by(desc(Comment.CreatedAt))
        
        pagination = query.paginate(
            page=page,
            per_page=per_page,
            error_out=False
        )
        
        comments = pagination.items
        
        comments_data = []
        for comment in comments:
            comment_data = comment.to_dict(include_replies=True)
            
            # 检查用户是否点赞
            if user_id:
                comment_data['is_liked'] = CommunityService.check_comment_liked(comment.CommentID, user_id)
            else:
                comment_data['is_liked'] = False
                
            comments_data.append(comment_data)
        
        return {
            'comments': comments_data,
            'pagination': {
                'page': page,
                'per_page': per_page,
                'total': pagination.total,
                'pages': pagination.pages
            }
        }
    
    @staticmethod
    def like_comment(comment_id, user_id):
        """点赞评论"""
        try:
            # 检查是否已经点赞
            existing_like = Like.query.filter_by(
                CommentID=comment_id, 
                UserID=user_id,
                LikeType='comment'
            ).first()
            
            if existing_like:
                return {'liked': True, 'like_count': existing_like.comment.LikeCount}
            
            # 创建点赞记录
            like = Like(
                CommentID=comment_id,
                UserID=user_id,
                LikeType='comment'
            )
            db.session.add(like)
            
            # 更新评论点赞数
            comment = Comment.query.get(comment_id)
            comment.LikeCount += 1
            comment.UpdatedAt = datetime.utcnow()
            
            # 创建通知（排除自己给自己点赞）
            if comment.UserID != user_id:
                notification = Notification(
                    UserID=comment.UserID,
                    FromUserID=user_id,
                    Type='like',
                    RelatedCommentID=comment_id,
                    Content=f'点赞了你的评论'
                )
                db.session.add(notification)
            
            db.session.commit()
            
            return {'liked': True, 'like_count': comment.LikeCount}
            
        except Exception as e:
            db.session.rollback()
            raise e
    
    @staticmethod
    def check_comment_liked(comment_id, user_id):
        """检查用户是否点赞了评论"""
        return Like.query.filter_by(
            CommentID=comment_id, 
            UserID=user_id,
            LikeType='comment'
        ).first() is not None
    
    @staticmethod
    def follow_user(follower_id, following_id):
        """关注用户"""
        try:
            if follower_id == following_id:
                raise ValueError('不能关注自己')
            
            # 检查是否已经关注
            existing_follow = Follow.query.filter_by(
                FollowerID=follower_id,
                FollowingID=following_id
            ).first()
            
            if existing_follow:
                return {'followed': True}
            
            # 创建关注记录
            follow = Follow(
                FollowerID=follower_id,
                FollowingID=following_id
            )
            db.session.add(follow)
            
            # 创建通知
            notification = Notification(
                UserID=following_id,
                FromUserID=follower_id,
                Type='follow',
                Content=f'关注了你'
            )
            db.session.add(notification)
            
            db.session.commit()
            
            return {'followed': True}
            
        except Exception as e:
            db.session.rollback()
            raise e
    
    @staticmethod
    def unfollow_user(follower_id, following_id):
        """取消关注用户"""
        try:
            follow = Follow.query.filter_by(
                FollowerID=follower_id,
                FollowingID=following_id
            ).first()
            
            if not follow:
                return {'followed': False}
            
            db.session.delete(follow)
            db.session.commit()
            
            return {'followed': False}
            
        except Exception as e:
            db.session.rollback()
            raise e
    
    @staticmethod
    def get_user_notifications(user_id, page=1, per_page=20):
        """获取用户通知"""
        query = Notification.query.filter_by(UserID=user_id).order_by(desc(Notification.CreatedAt))
        
        pagination = query.paginate(
            page=page,
            per_page=per_page,
            error_out=False
        )
        
        notifications = pagination.items
        
        # 标记为已读
        unread_ids = [n.NotificationID for n in notifications if not n.IsRead]
        if unread_ids:
            Notification.query.filter(Notification.NotificationID.in_(unread_ids)).update(
                {'IsRead': True},
                synchronize_session=False
            )
            db.session.commit()
        
        return {
            'notifications': [n.to_dict() for n in notifications],
            'pagination': {
                'page': page,
                'per_page': per_page,
                'total': pagination.total,
                'pages': pagination.pages
            }
        }
    
    @staticmethod
    def get_hot_tags(limit=10):
        """获取热门标签"""
        # 统计最近30天内使用的热门标签
        thirty_days_ago = datetime.utcnow() - timedelta(days=30)
        
        hot_tags = db.session.query(
            PostTag.TagName,
            func.count(PostTag.PostTagID).label('usage_count')
        ).join(CommunityPost).filter(
            CommunityPost.CreatedAt >= thirty_days_ago,
            CommunityPost.Status == 'published'
        ).group_by(PostTag.TagName).order_by(
            func.count(PostTag.PostTagID).desc()
        ).limit(limit).all()
        
        return [{'tag_name': tag, 'usage_count': count} for tag, count in hot_tags]
    
    @staticmethod
    def get_user_activity_stats(user_id):
        """获取用户社区活动统计"""
        post_count = CommunityPost.query.filter_by(UserID=user_id, Status='published').count()
        like_count = Like.query.filter_by(UserID=user_id).count()
        comment_count = Comment.query.filter_by(UserID=user_id, Status='published').count()
        collection_count = Collection.query.filter_by(UserID=user_id).count()
        
        # 获取总获赞数
        received_likes = db.session.query(func.count(Like.LikeID)).join(
            CommunityPost, Like.PostID == CommunityPost.PostID
        ).filter(
            CommunityPost.UserID == user_id,
            Like.LikeType == 'post'
        ).scalar()
        
        # 获取粉丝数和关注数
        follower_count = Follow.query.filter_by(FollowingID=user_id).count()
        following_count = Follow.query.filter_by(FollowerID=user_id).count()
        
        return {
            'post_count': post_count,
            'like_count': like_count,
            'comment_count': comment_count,
            'collection_count': collection_count,
            'received_likes': received_likes or 0,
            'follower_count': follower_count,
            'following_count': following_count
        }

2.4 社区API路由

# app/community/__init__.py
from flask import Blueprint

community_bp = Blueprint('community', __name__)

from app.community import routes

# app/community/routes.py
from flask import request, jsonify
from flask_jwt_extended import jwt_required, get_jwt_identity
from app.community import community_bp
from app.services.community_service import CommunityService
from app.utils.validation import validate_post_data, validate_comment_data

@community_bp.route('/posts', methods=['POST'])
@jwt_required()
def create_post():
    """创建帖子"""
    try:
        current_user_id = get_jwt_identity()
        data = request.get_json()
        
        # 验证数据
        errors = validate_post_data(data)
        if errors:
            return jsonify({
                'success': False,
                'message': '数据验证失败',
                'errors': errors
            }), 400
        
        post = CommunityService.create_post(current_user_id, data)
        
        return jsonify({
            'success': True,
            'message': '帖子发布成功',
            'data': post
        }), 201
        
    except Exception as e:
        return jsonify({
            'success': False,
            'message': f'发布帖子失败: {str(e)}'
        }), 500

@community_bp.route('/posts', methods=['GET'])
def get_posts():
    """获取帖子列表"""
    try:
        page = request.args.get('page', 1, type=int)
        per_page = request.args.get('per_page', 20, type=int)
        post_type = request.args.get('type')
        search = request.args.get('search')
        tags = request.args.get('tags')
        location = request.args.get('location')
        sort_by = request.args.get('sort_by', 'created_at')
        
        # 获取当前用户ID（如果已登录）
        current_user_id = None
        try:
            current_user_id = get_jwt_identity()
        except:
            pass
        
        filters = {}
        if post_type:
            filters['post_type'] = post_type
        if search:
            filters['search'] = search
        if tags:
            filters['tags'] = tags
        if location:
            filters['location'] = location
        
        result = CommunityService.get_posts(
            page=page,
            per_page=per_page,
            filters=filters,
            sort_by=sort_by,
            user_id=current_user_id
        )
        
        return jsonify({
            'success': True,
            'data': result
        }), 200
        
    except Exception as e:
        return jsonify({
            'success': False,
            'message': f'获取帖子列表失败: {str(e)}'
        }), 500

@community_bp.route('/posts/<int:post_id>', methods=['GET'])
def get_post(post_id):
    """获取帖子详情"""
    try:
        # 获取当前用户ID（如果已登录）
        current_user_id = None
        try:
            current_user_id = get_jwt_identity()
        except:
            pass
        
        post = CommunityService.get_post_detail(post_id, current_user_id)
        if not post:
            return jsonify({
                'success': False,
                'message': '帖子不存在或已被删除'
            }), 404
        
        return jsonify({
            'success': True,
            'data': post
        }), 200
        
    except Exception as e:
        return jsonify({
            'success': False,
            'message': f'获取帖子详情失败: {str(e)}'
        }), 500

@community_bp.route('/posts/<int:post_id>/like', methods=['POST'])
@jwt_required()
def like_post(post_id):
    """点赞帖子"""
    try:
        current_user_id = get_jwt_identity()
        
        result = CommunityService.like_post(post_id, current_user_id)
        
        return jsonify({
            'success': True,
            'message': '点赞成功' if result['liked'] else '取消点赞成功',
            'data': result
        }), 200
        
    except Exception as e:
        return jsonify({
            'success': False,
            'message': f'操作失败: {str(e)}'
        }), 500

@community_bp.route('/posts/<int:post_id>/like', methods=['DELETE'])
@jwt_required()
def unlike_post(post_id):
    """取消点赞帖子"""
    try:
        current_user_id = get_jwt_identity()
        
        result = CommunityService.unlike_post(post_id, current_user_id)
        
        return jsonify({
            'success': True,
            'message': '取消点赞成功',
            'data': result
        }), 200
        
    except Exception as e:
        return jsonify({
            'success': False,
            'message': f'操作失败: {str(e)}'
        }), 500

@community_bp.route('/posts/<int:post_id>/collect', methods=['POST'])
@jwt_required()
def collect_post(post_id):
    """收藏帖子"""
    try:
        current_user_id = get_jwt_identity()
        
        result = CommunityService.collect_post(post_id, current_user_id)
        
        return jsonify({
            'success': True,
            'message': '收藏成功' if result['collected'] else '取消收藏成功',
            'data': result
        }), 200
        
    except Exception as e:
        return jsonify({
            'success': False,
            'message': f'操作失败: {str(e)}'
        }), 500

@community_bp.route('/posts/<int:post_id>/collect', methods=['DELETE'])
@jwt_required()
def uncollect_post(post_id):
    """取消收藏帖子"""
    try:
        current_user_id = get_jwt_identity()
        
        result = CommunityService.uncollect_post(post_id, current_user_id)
        
        return jsonify({
            'success': True,
            'message': '取消收藏成功',
            'data': result
        }), 200
        
    except Exception as e:
        return jsonify({
            'success': False,
            'message': f'操作失败: {str(e)}'
        }), 500

@community_bp.route('/posts/<int:post_id>/comments', methods=['POST'])
@jwt_required()
def create_comment(post_id):
    """创建评论"""
    try:
        current_user_id = get_jwt_identity()
        data = request.get_json()
        
        # 验证数据
        errors = validate_comment_data(data)
        if errors:
            return jsonify({
                'success': False,
                'message': '数据验证失败',
                'errors': errors
            }), 400
        
        comment = CommunityService.create_comment(post_id, current_user_id, data)
        
        return jsonify({
            'success': True,
            'message': '评论成功',
            'data': comment
        }), 201
        
    except ValueError as e:
        return jsonify({
            'success': False,
            'message': str(e)
        }), 400
    except Exception as e:
        return jsonify({
            'success': False,
            'message': f'评论失败: {str(e)}'
        }), 500

@community_bp.route('/posts/<int:post_id>/comments', methods=['GET'])
def get_comments(post_id):
    """获取帖子评论"""
    try:
        page = request.args.get('page', 1, type=int)
        per_page = request.args.get('per_page', 20, type=int)
        
        # 获取当前用户ID（如果已登录）
        current_user_id = None
        try:
            current_user_id = get_jwt_identity()
        except:
            pass
        
        result = CommunityService.get_post_comments(post_id, page, per_page, current_user_id)
        
        return jsonify({
            'success': True,
            'data': result
        }), 200
        
    except Exception as e:
        return jsonify({
            'success': False,
            'message': f'获取评论失败: {str(e)}'
        }), 500

@community_bp.route('/comments/<int:comment_id>/like', methods=['POST'])
@jwt_required()
def like_comment(comment_id):
    """点赞评论"""
    try:
        current_user_id = get_jwt_identity()
        
        result = CommunityService.like_comment(comment_id, current_user_id)
        
        return jsonify({
            'success': True,
            'message': '点赞成功',
            'data': result
        }), 200
        
    except Exception as e:
        return jsonify({
            'success': False,
            'message': f'操作失败: {str(e)}'
        }), 500

@community_bp.route('/users/<int:user_id>/follow', methods=['POST'])
@jwt_required()
def follow_user(user_id):
    """关注用户"""
    try:
        current_user_id = get_jwt_identity()
        
        result = CommunityService.follow_user(current_user_id, user_id)
        
        return jsonify({
            'success': True,
            'message': '关注成功',
            'data': result
        }), 200
        
    except ValueError as e:
        return jsonify({
            'success': False,
            'message': str(e)
        }), 400
    except Exception as e:
        return jsonify({
            'success': False,
            'message': f'关注失败: {str(e)}'
        }), 500

@community_bp.route('/users/<int:user_id>/follow', methods=['DELETE'])
@jwt_required()
def unfollow_user(user_id):
    """取消关注用户"""
    try:
        current_user_id = get_jwt_identity()
        
        result = CommunityService.unfollow_user(current_user_id, user_id)
        
        return jsonify({
            'success': True,
            'message': '取消关注成功',
            'data': result
        }), 200
        
    except Exception as e:
        return jsonify({
            'success': False,
            'message': f'操作失败: {str(e)}'
        }), 500

@community_bp.route('/notifications', methods=['GET'])
@jwt_required()
def get_notifications():
    """获取用户通知"""
    try:
        current_user_id = get_jwt_identity()
        page = request.args.get('page', 1, type=int)
        per_page = request.args.get('per_page', 20, type=int)
        
        result = CommunityService.get_user_notifications(current_user_id, page, per_page)
        
        return jsonify({
            'success': True,
            'data': result
        }), 200
        
    except Exception as e:
        return jsonify({
            'success': False,
            'message': f'获取通知失败: {str(e)}'
        }), 500

@community_bp.route('/tags/hot', methods=['GET'])
def get_hot_tags():
    """获取热门标签"""
    try:
        limit = request.args.get('limit', 10, type=int)
        
        tags = CommunityService.get_hot_tags(limit)
        
        return jsonify({
            'success': True,
            'data': tags
        }), 200
        
    except Exception as e:
        return jsonify({
            'success': False,
            'message': f'获取热门标签失败: {str(e)}'
        }), 500

@community_bp.route('/users/me/stats', methods=['GET'])
@jwt_required()
def get_user_stats():
    """获取用户社区统计"""
    try:
        current_user_id = get_jwt_identity()
        
        stats = CommunityService.get_user_activity_stats(current_user_id)
        
        return jsonify({
            'success': True,
            'data': stats
        }), 200
        
    except Exception as e:
        return jsonify({
            'success': False,
            'message': f'获取统计失败: {str(e)}'
        }), 500

2.5 数据验证工具

# app/utils/validation.py
import re

def validate_post_data(data):
    """验证帖子数据"""
    errors = []
    
    if not data.get('title') or len(data['title'].strip()) == 0:
        errors.append('帖子标题不能为空')
    elif len(data['title']) > 200:
        errors.append('帖子标题不能超过200个字符')
    
    if not data.get('content') or len(data['content'].strip()) == 0:
        errors.append('帖子内容不能为空')
    elif len(data['content']) > 10000:
        errors.append('帖子内容不能超过10000个字符')
    
    post_type = data.get('post_type', 'normal')
    if post_type not in ['normal', 'question', 'experience', 'share']:
        errors.append('无效的帖子类型')
    
    # 验证图片数据
    if 'images' in data:
        if len(data['images']) > 9:
            errors.append('最多只能上传9张图片')
        for image in data['images']:
            if not image.get('image_url'):
                errors.append('图片URL不能为空')
    
    # 验证标签
    if 'tags' in data:
        if len(data['tags']) > 5:
            errors.append('最多只能添加5个标签')
        for tag in data['tags']:
            if len(tag) > 20:
                errors.append('标签长度不能超过20个字符')
    
    return errors

def validate_comment_data(data):
    """验证评论数据"""
    errors = []
    
    if not data.get('content') or len(data['content'].strip()) == 0:
        errors.append('评论内容不能为空')
    elif len(data['content']) > 1000:
        errors.append('评论内容不能超过1000个字符')
    
    return errors

3. 微信小程序集成

3.1 小程序API调用示例

// 小程序社区功能API调用示例

// 获取帖子列表
wx.request({
  url: 'https://api.yourdomain.com/api/community/posts',
  method: 'GET',
  data: {
    page: 1,
    per_page: 20,
    type: 'experience',
    sort_by: 'hot'
  },
  header: {
    'Authorization': 'Bearer ' + token // 可选，用于个性化推荐
  },
  success: function(res) {
    if (res.data.success) {
      console.log('帖子列表:', res.data.data);
    }
  }
});

// 发布帖子
wx.request({
  url: 'https://api.yourdomain.com/api/community/posts',
  method: 'POST',
  header: {
    'Authorization': 'Bearer ' + token,
    'Content-Type': 'application/json'
  },
  data: {
    title: '我的旅行经历',
    content: '分享一次难忘的旅行...',
    post_type: 'experience',
    location: '北京市',
    tags: ['旅行', '分享', '北京'],
    images: [
      {
        image_url: 'https://example.com/image1.jpg',
        thumbnail_url: 'https://example.com/thumb1.jpg',
        description: '景点照片'
      }
    ],
    is_anonymous: false
  },
  success: function(res) {
    if (res.data.success) {
      wx.showToast({ title: '发布成功' });
    }
  }
});

// 点赞帖子
wx.request({
  url: 'https://api.yourdomain.com/api/community/posts/123/like',
  method: 'POST',
  header: {
    'Authorization': 'Bearer ' + token
  },
  success: function(res) {
    if (res.data.success) {
      console.log('点赞结果:', res.data.data);
    }
  }
});

// 发表评论
wx.request({
  url: 'https://api.yourdomain.com/api/community/posts/123/comments',
  method: 'POST',
  header: {
    'Authorization': 'Bearer ' + token,
    'Content-Type': 'application/json'
  },
  data: {
    content: '这个帖子很棒！',
    parent_comment_id: null // 如果是回复评论，传入父评论ID
  },
  success: function(res) {
    if (res.data.success) {
      wx.showToast({ title: '评论成功' });
    }
  }
});

4. 性能优化策略

4.1 缓存策略

# app/services/cache_service.py
import redis
import json
from functools import wraps
from app import app

# Redis连接
redis_client = redis.Redis(
    host=app.config['REDIS_HOST'],
    port=app.config['REDIS_PORT'],
    db=app.config['REDIS_DB'],
    decode_responses=True
)

def cache_result(prefix, timeout=300):
    """缓存装饰器"""
    def decorator(func):
        @wraps(func)
        def wrapper(*args, **kwargs):
            # 生成缓存键
            cache_key = f"{prefix}:{str(args)}:{str(kwargs)}"
            
            # 尝试从缓存获取
            cached = redis_client.get(cache_key)
            if cached:
                return json.loads(cached)
            
            # 执行函数
            result = func(*args, **kwargs)
            
            # 缓存结果
            redis_client.setex(cache_key, timeout, json.dumps(result))
            
            return result
        return wrapper
    return decorator

class CacheService:
    
    @staticmethod
    def get_hot_posts():
        """获取热门帖子（缓存）"""
        cache_key = 'community:hot_posts'
        cached = redis_client.get(cache_key)
        if cached:
            return json.loads(cached)
        
        # 从数据库获取并缓存
        posts = CommunityService.get_posts(sort_by='hot')
        redis_client.setex(cache_key, 600, json.dumps(posts))  # 缓存10分钟
        return posts
    
    @staticmethod
    def clear_post_cache(post_id):
        """清除帖子相关缓存"""
        keys_patterns = [
            'community:hot_posts',
            f'community:post:{post_id}',
            'community:posts:*'
        ]
        
        for pattern in keys_patterns:
            keys = redis_client.keys(pattern)
            if keys:
                redis_client.delete(*keys)

4.2 数据库查询优化

-- 定期更新统计信息的存储过程
CREATE PROCEDURE dbo.sp_UpdateCommunityStats
AS
BEGIN
    -- 更新帖子热度分数（用于排序）
    UPDATE CommunityPosts 
    SET UpdatedAt = GETUTCDATE()
    WHERE PostID IN (
        SELECT PostID 
        FROM CommunityPosts 
        WHERE Status = 'published' 
        AND DATEDIFF(HOUR, UpdatedAt, GETUTCDATE()) > 1
    );
    
    -- 清理过期缓存
    DELETE FROM Cache WHERE ExpiresAt < GETUTCDATE();
END;
GO

-- 创建维护作业
EXEC sp_add_job
    @job_name = 'Community Maintenance',
    @enabled = 1;

EXEC sp_add_jobstep
    @job_name = 'Community Maintenance',
    @step_name = 'Update Stats',
    @subsystem = 'TSQL',
    @command = 'EXEC dbo.sp_UpdateCommunityStats';

EXEC sp_add_jobschedule
    @job_name = 'Community Maintenance',
    @name = 'Daily Maintenance',
    @freq_type = 4, -- 每天
    @freq_interval = 1,
    @active_start_time = 20000; -- 凌晨2点执行

这套社区功能后端系统提供了：

1. 完整的社区功能：发帖、点赞、评论、收藏、关注
2. 实时通知系统：用户互动实时提醒
3. 内容管理：帖子分类、标签、置顶、精华
4. 用户关系：关注系统和个人主页
5. 性能优化：缓存策略和数据库优化
6. 微信小程序集成：完整的API接口
7. 数据安全：权限验证和数据验证

通过这套系统，可以为微信小程序旅游助手提供强大的社区互动功能，增强用户粘性和活跃度。