[FIXED] Self-referencing a table for a foreign key relationship in Flask-Sqlalchemy

Issue

I'm sure there is an easy solution to this problem but I haven't been able to find it or think it. I have a model "Entry" which are supposed to be in sequence with each other (For instance entry 1 might be the first part of a blog post, entry 2 the second part and so on)

class Entry(db.Model):
        __tablename__="entry"
        id = db.Column(db.Integer, primary_key=True)
                ...

        prev_entry_id = db.Column(db.Integer, db.ForeignKey('entry.id'))
        next_entry_id = db.Column(db.Integer, db.ForeignKey('entry.id'))
        prev_entry = db.relationship("Entry", backref='next', remote_side='entry.id', foreign_keys=[prev_entry_id])  #previous_entry object
        next_entry = db.relationship("Entry",  backref="previous", uselist=False, foreign_keys=[next_entry_id])  #next_entry object

The idea is if you get one entry, say entry-56 you can navigate forward (57) and backward (55), step-by-step, all the way through, as if it were a pointer.

However I'm getting a ton of errors from sqlalchemy:

Table has no attribute 'id'

If I comment out "prev_entry" I get:

sqlalchemy.exc.InvalidRequestError: One or more mappers failed to initialize - can't proceed with initialization of other mappers. Triggering mapper: 'mapped class Entry->entry'. Original exception was: 'Table' object has no attribute 'id'

If I comment out both "next_*entry" I get: Table has no attribute 'id'

If I comment out both "next_entry"* and "prev_entry" it at least loads.

In short, does anyone know the correct way to set this up in Flask-SqlAlchemy?

Solution

The "no attribute id" error occurs because SQLAlchemy interprets 'entry.id' as referring to the underlying Table object, rather than the model. Fix this by using either 'Entry.id' (model reference) or 'entry.c.id' (table reference).

Additionally, use back_populates rather than backref to configure the reverse relationships:

prev_entry = db.relationship(
    'Entry',
    back_populates='next_entry',
    remote_side='Entry.id',
    foreign_keys=[prev_entry_id],
)  # previous_entry object
next_entry = db.relationship(
    'Entry',
    back_populates='prev_entry',
    uselist=False,
    foreign_keys=[next_entry_id],
)  # next_entry object

Answered By - snakecharmerb