python - 将 MySQL 转换为 Flask-SQLAlchemy

Question

我目前正在尝试使用 SQLAlchemy 的功能将我编写的 MySQL 语句转换为

我有一张表 Project 和一张表 Likes。这些包含喜欢的项目。我想获取所有特定用户项目并获取相应的点赞数。

这是 MYSQL:

SELECT project.id as id, project.name as name, 
   project.description as description, 
   project.user_id as user_id, 
   iFNULL(like_count,0) as like_count,
   project.created_on
   FROM milo.project LEFT JOIN (
       SELECT COUNT(*) as like_count, project_id 
           FROM milo.likes 
           GROUP BY project_id
   ) counting 
ON counting.project_id = milo.project.id 
WHERE (milo.project.user_id = 1) 
ORDER BY project.created_on

所以这将表 Project 连接到另一个通过查询创建的表，该查询计算该项目的点赞数

此查询获取 Like 计数:

res = db.session.query(func.count(likes.Likes.project_id), project.Project) \
        .join(project.Project, (likes.Likes.project_id == project.Project.id)) \
        .group_by(likes.Likes.project_id)

此查询获取用户项目:

user_projects = Project.query.filter_by(user_id=1).all()

我最接近的是:

projects = db.session.query(func.count(Likes.project_id), Project) \
        .join(Project, (Project.id == Likes.project_id)) \
        .filter(Likes.user_id == user.id) \
        .group_by(Likes.project_id).order_by(Project.created_on.desc()) \
        .all()

但问题在于，如果一个项目没有人点赞，它就不会被退回!显然它还需要一个 Left Join，我在这里找不到。

谁能帮忙翻译这份声明？

Answer 1

我相信您正在搜索 outerjoin .

所以

.join(Project, (Project.id == Likes.project_id)) \

成为

.outerjoin(Project, (Project.id == Likes.project_id))