title: SQLAlchemy left join using subquery
body: <p>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.</p>
  
  <p>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.</p>
  
  <p>I want some thing like:</p>
  
  <pre><code>current_user = 'testuser'
  posts.query.outerjoin(favorites, and_(posts.post_id == favorites.post_id, favorites.username == current_user)).limit(10).all()
  </code></pre>
  
  <p>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:</p>
  
  <pre><code>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
  </code></pre>
  
  <p>It's also worth mentioning that I have defined the relationship on posts like:</p>
  
  <pre><code>favorites = db.relationship("favorites")
  </code></pre>
  
  <p>And I have defined the foreign key on favorites like this:</p>
  
  <pre><code>post_id = db.Column(db.String(255), db.ForeignKey('posts.post_id'))
  </code></pre>
  
  <p>How can I accomplish this in SQLAlchemy?</p>