title: SQLAlchemy query.filter returned no FROM clauses due to auto-correlation
body: <p>I'm a beginner SQLAlchemy user frustrated with the extensive documentation.</p>
  
  <p>I have a newsfeed that is updated when a new <strong>revision</strong> is made to some <strong>content</strong> object. <strong>content</strong> always has at least one <strong>revision</strong>. <strong>content</strong> is related to 1 or more <strong>topics</strong> through an association table. I'm given a set of <strong>topic.id</strong>s T, and would like to show the N most recent "approved" revisions belonging to a row in <strong>content</strong> that has at least one <strong>topic</strong> in T. ("approved" is just an enum attribute on <strong>revision</strong>)</p>
  
  <p>Here are the models and the relevant attributes:</p>
  
  <pre><code>class Revision(Model):
      __tablename__ = 'revision'
  
      class Statuses(object): # enum
          APPROVED = 'approved'
          PROPOSED = 'proposed'
          REJECTED = 'rejected'
          values = [APPROVED, PROPOSED, REJECTED]
      id = Column(Integer, primary_key=True)
      data = Column(JSONB, default=[], nullable=False)
      content_id = db.Column(db.Integer, db.ForeignKey('content.id'), nullable=False)
  
  class Content(Model):
      __tablename__ = 'content'
  
      id = Column(Integer, primary_key=True)
      topic_edges = relationship(
          'TopicContentAssociation',
          primaryjoin='Content.id == TopicContentAssociation.content_id',
          backref='content',
          lazy='dynamic',
          cascade='all, delete-orphan'
      )
  
      revisions = relationship(
          'Revision',
          lazy='dynamic',
          backref='content',
          cascade='all, delete-orphan'
      )
  
  class TopicContentAssociation(Model):
      __tablename__ = 'topic_content_association'
      topic_id = Column(Integer, ForeignKey('topic.id'), primary_key=True)
      content_id = Column(Integer, ForeignKey('content.id'), primary_key=True)
  
  class Topic(Model):
      __tablename__ = 'topic'
      id = Column(Integer, primary_key=True)
  </code></pre>
  
  <p>Here's what I've got so far:</p>
  
  <pre><code>revisions = session.query(Revision).outerjoin(Content).outerjoin(Topic).filter(
      ~exists().where(
          and_(
              Topic.id.in_(T),
              Revision.status == Revision.Statuses.APPROVED
          )           )   
  ).order_by(Revision.ts_created.desc()).limit(N)
  </code></pre>
  
  <p>and this error is happening:</p>
  
  <p>Select statement returned no FROM clauses due to auto-correlation; specify correlate(&lt;tables>) to control correlation manually.:</p>
  
  <pre><code>SELECT * 
  FROM topic, revision 
  WHERE topic.id IN (:id_1, :id_2, :id_3...) 
  AND revision.status = :status_1
  </code></pre>
  
  <p>The interesting part is that if I remove the and_ operator and the second expression within it (lines 3, 5, and 6), the error seems to go away.</p>
  
  <p>BONUS: :) I would also like to show only one <strong>revision</strong> per row of <strong>content</strong>. If somebody hits save a bunch of times, I don't want the feed to be cluttered.</p>
  
  <p>Again, I'm very new to SQLAlchemy (and actually, relational databases), so an answer targeted to a beginner would be much appreciated!</p>
  
  <p>EDIT: adding <code>.correlate(Revision)</code> after the <code>.where</code> clause fixes things, but I'm still working to figure out exactly what is going on here.</p>