SQLAlchemy query.filter returned no FROM clauses due to auto-correlation

Question

I'm a beginner SQLAlchemy user frustrated with the extensive documentation.

I have a newsfeed that is updated when a new revision is made to some content object. content always has at least one revision. content is related to 1 or more topics through an association table. I'm given a set of topic.ids T, and would like to show the N most recent "approved" revisions belonging to a row in content that has at least one topic in T. ("approved" is just an enum attribute on revision)

Here are the models and the relevant attributes:

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)

Here's what I've got so far:

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)

and this error is happening:

Select statement returned no FROM clauses due to auto-correlation; specify correlate(<tables>) to control correlation manually.:

SELECT * 
FROM topic, revision 
WHERE topic.id IN (:id_1, :id_2, :id_3...) 
AND revision.status = :status_1

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.

BONUS: :) I would also like to show only one revision per row of content. If somebody hits save a bunch of times, I don't want the feed to be cluttered.

Again, I'm very new to SQLAlchemy (and actually, relational databases), so an answer targeted to a beginner would be much appreciated!

EDIT: adding .correlate(Revision) after the .where clause fixes things, but I'm still working to figure out exactly what is going on here.

Answer 1

This is a very late response, but I am replying in case someone finds this topic.

Your second expression within the exist statement (Revision.status == Revision.Statuses.APPROVED) calls the table Revision for a second time in your query. The first time you call this table is by writing "session.query(Revision)"

If we "translate" your exists statement in PostgreSQL it would be:

EXISTS (SELECT 1 
        FROM Revision
        WHERE topic.id IN (:id_1, :id_2, :id_3...) 
        AND revision.status = :status_1
)

Calling the same table twice (FROM Revision) in the same query is not allowed unless you use an alias. So, you can create an alias of the desired table, using the aliased() function and solve your problem. Your code should be fine like this:

from sqlalchemy.orm import aliased

aliasRev = aliased(Revision)

revisions = session.query(Revision).outerjoin(Content).outerjoin(Topic).filter(
    ~exists().where(
        and_(
            Topic.id.in_(T),
            aliasRev.status == Revision.Statuses.APPROVED
        )           )   
).order_by(Revision.ts_created.desc()).limit(N)