SQLAlchemy left join using subquery
Let's say there is a "posts" table that contains blog posts, and another "favorites" table that associates a username with a post. Many users may love a message, so a relationship is one message for many a select few.
I'm trying to figure out the syntax for adding posts to favorites, but I only want favorites where the user is the current user.
I need something like:
current_user = 'testuser'
posts.query.outerjoin(favorites, and_(posts.post_id == favorites.post_id, favorites.username == current_user)).limit(10).all()
This got me to really close, except that the "favorite.username == current_user" state is mostly ignored. This is what I'm looking for in real 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 is also worth noting that I have defined relationships on posts, for example:
favorites = db.relationship("favorites")
And I have defined foreign key in favorites like:
post_id = db.Column(db.String(255), db.ForeignKey('posts.post_id'))
How can I accomplish this in SQLAlchemy?
source to share
In fact, you just need to replace outerjoin
with join
and the filter will work fine.
Also, if your table favorites
contains no additional information and only links users
and posts
, you should simply define `Many to Many relationships . In the documentation examples, Parent / Child will be your user / post.
Update-1: To answer the 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)
source to share