Relationship between two tables, SQLAlchemy

Question

I want to make a relationship between AuthorComments and Reply to his comments.

Here is my models.py:

class AuthorComments(Base):

    id = db.Column(db.Integer, primary_key=True)
    author_id = db.Column(db.Integer, db.ForeignKey('author.id'))
    name = db.Column(db.String(50))
    email = db.Column(db.String(50), unique=True)
    comment = db.Column(db.Text)
    live = db.Column(db.Boolean)

    comments = db.relationship('Reply', backref='reply', lazy='joined')

    def __init__(self,author, name, email, comment, live=True):

        self.author_id = author.id
        self.name = name
        self.email = email
        self.comment = comment
        self.live = live

class Reply(Base):

    id = db.Column(db.Integer, primary_key=True)
    reply_id = db.Column(db.Integer, db.ForeignKey('author.id'))
    name = db.Column(db.String(50))
    email = db.Column(db.String(50), unique=True)
    comment = db.Column(db.Text)
    live = db.Column(db.Boolean)

    def __init__(self,author, name, email, comment, live=True):

        self.reply_id = author.id
        self.name = name
        self.email = email
        self.comment = comment
        self.live = live

Why am I getting this error: sqlalchemy.exc.InvalidRequestError

InvalidRequestError: One or more mappers failed to initialize - can't proceed with initialization of other mappers. Original exception was: Could not determine join condition between parent/child tables on relationship AuthorComments.comments - there are no foreign keys linking these tables. Ensure that referencing columns are associated with a ForeignKey or ForeignKeyConstraint, or specify a 'primaryjoin' expression.

Answer 1

Your trouble is that SQLAlchemy doesn't know, for a given row of the child table (Reply), which row of the parent table (AuthorComments) to select! You need to define a foreign-key column in Reply that references a column of its parent AuthorComments.

Here is the documentation on defining one-to-many relationships in SQLAlchemy.

Something like this:

class AuthorComments(Base):
    __tablename__ = 'author_comment'
    ...

class Reply(Base):
    ...
    author_comment_id = db.Column(db.Integer, db.ForeignKey('author_comment.id'))
    ...
    author_comment = db.relationship(
        'AuthorComments',
        backref='replies',
        lazy='joined'
        )

will result in each reply acquiring a relationship to an author_comment such that some_reply.author_comment_id == some_author_comment.id, or None if no such equality exists.

The backref allows each author_comment to, reciprocally, have a relationship to a collection of replies called replies, satisfying the above condition.