这条 SQL 语句怎么转换成 SQLAchemy 代码？

Question

问题描述

想问下怎么把我的 SQL 语句转换成 SQLAchemy 的形式？
想写一个推荐版块，暂时想到用 favorite 收藏表，like 点赞表，topic 帖子表，reply 评论表四个表连接起来，按 topic.id 进行分组；分完组后统计收藏，点赞，帖子，评论的数量，然后做倒序排列。

问题出现的环境背景及自己尝试过哪些方法

写出了 SQL 语句，但是 SQLAlchemy 的文档看不太懂，无法自己转换成 SQLAlchemy 代码。

相关代码

// 请把代码文本粘贴到下方（请勿用图片代替代码）
写出来的 SQL 语句如下：

SELECT 
    t.id,
    t.views,
    t.title,
    t.content,
    t.user_id,
    t.movie_id,
    t.board_id,
    r.id AS reply_id,
    COUNT(r.id) AS reply_count,
    COUNT(f.id) AS favorite_count,
    COUNT(l.id) AS like_count,
    COUNT(r.id)+COUNT(f.id)+COUNT(l.id) as all_count
FROM
    web21.topic t
        LEFT JOIN
    web21.reply r ON t.id = r.topic_id
        LEFT JOIN
    web21.favorite f ON t.id = f.topic_id
        LEFT JOIN
    web21.like l ON t.id = l.topic_id
GROUP BY t.id
ORDER BY all_count DESC;

@main.route("/")
def index():
    board_id = int(request.args.get('board_id', 1))
    page = request.args.get('page', 1, type=int)
    recommended = 1
    if board_id == recommended:
        # 这里放 SQLAlchemy Query 语句
    else:
        paginate = Topic.query \
            .filter_by(board_id=board_id) \
            .order_by(Topic.created_time.desc()) \
            .paginate(page, 10)
    ms = paginate.items
    token = new_csrf_token()
    bs = Board.all()
    u = current_user()
    s = forum_status()
    return render_template("topic/index.html",
                           ms=ms,
                           token=token,
                           bs=bs,
                           bid=board_id,
                           user=u,
                           paginate=paginate,
                           status=s,
                           )

你期待的结果是什么？实际看到的错误信息又是什么？

想要用 SQLAlchemy ORM 来实现推荐版块的排序。

Answer 1

您可以使用以下代码将您的原始 SQL 语句转换为 SQLAlchemy 查询：

from sqlalchemy.sql import func

recommended = 1
if board_id == recommended:
    results = db.session.query(
        Topic.id,
        Topic.views,
        Topic.title,
        Topic.content,
        Topic.user_id,
        Topic.movie_id,
        Topic.board_id,
        func.count(Reply.id).label('reply_count'),
        func.count(Favorite.id).label('favorite_count'),
        func.count(Like.id).label('like_count'),
        (func.count(Reply.id) + func.count(Favorite.id) + func.count(Like.id)).label('all_count')
    ).outerjoin(Reply).outerjoin(Favorite).outerjoin(Like)\
    .group_by(Topic.id)\
    .order_by('all_count DESC')\
    .all()

else:
    paginate = Topic.query \
        .filter_by(board_id=board_id) \
        .order_by(Topic.created_time.desc()) \
        .paginate(page, 10)
    ms = paginate.items

这个查询使用 SQLAlchemy 的函数来计算 count，然后将结果分组，并按 all_count 进行排序。您需要修改表和列名以匹配您的实际数据库模式。