SQLAlchemy使用子查询左联接 [英] SQLAlchemy left join using subquery

问题描述

让我们说有一个表"posts"包含博客文章，还有一个表"favorites"将用户名链接到帖子.许多用户可以收藏一则帖子，因此关系是许多收藏夹的一则帖子.

Lets say there's a table "posts" which contains blog posts, and another table "favorites" which links a username to a post. Many users can favorite a post, so the relationship is one post to many favorites.

我试图弄清楚将帖子加入收藏夹的语法，但我只希望那些用户是当前用户的收藏夹.

I am trying to figure out the syntax to join posts to favorites, but I only want those favorites where the user is the current user.

我想要类似的东西

current_user = 'testuser'
posts.query.outerjoin(favorites, and_(posts.post_id == favorites.post_id, favorites.username == current_user)).limit(10).all()

这使我非常接近，只是看起来"favorites.username == current_user"条件基本上被忽略了.这就是我在实际的SQL中寻找的东西:

This get me really close, except it seems like the "favorites.username == current_user" condition is basically getting ignored. This is what I am looking for in actual SQL:

SELECT *
FROM posts p
LEFT JOIN (
            SELECT * FROM favorites f WHERE f.user_id = 'testuser'
          ) ff ON ff.post_id = p.post_id
LIMIT 10

值得一提的是，我已经在以下帖子中定义了关系:

It's also worth mentioning that I have defined the relationship on posts like:

favorites = db.relationship("favorites")

我已经在收藏夹上定义了外键，如下所示:

And I have defined the foreign key on favorites like this:

post_id = db.Column(db.String(255), db.ForeignKey('posts.post_id'))

如何在SQLAlchemy中完成此操作?

How can I accomplish this in SQLAlchemy?

推荐答案

真的，您只需要将outerjoin替换为join，过滤器就可以正常工作.

Really you just need to replace the outerjoin with join, and the filter would work just fine.

此外，如果您的favorites表不包含其他信息，而仅包含链接users和posts，则应考虑简单地定义.在文档示例中，父级/子级"将是您的用户/帖子".

Also, if your favorites table contains no additional information and only links users and posts, you should consider simply defining a `Many to Many' relationship. In the documentation examples Parent/Child would be your User/Post.

更新1:只是为了回答给出您的评论的问题的第二部分，下面的查询应该给您一个想法:

Update-1: just to answer second part of the question given your comment, the query below should give you an idea:

current_user = 2
subq = (db.session.query(favorites)
        .filter(favorites.user_id == current_user).subquery('ff'))
q = (db.session.query(posts, subq.c.score)
     .outerjoin(subq, subq.c.post_id == posts.post_id))
q = q.order_by(subq.c.score.desc())
for post, score in q:
    print(post, score)

这篇关于SQLAlchemy使用子查询左联接的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！