0

I have a model named Post, which has a boolean field called is_answer. If the is_answer field of a Post is True, it's a "question"; otherwise, it's an "answer". I want to create the following question-answer relationship:

One "question" may have many "answer"s, but one "answer" has and only has one "question". Due to the fact that both "question" and "answer" are essentially Posts, I think the relationship must be self-referencing.

Here is what I've tried:

class Post(db.Model):
    __tablename__ = 'posts'
    id = db.Column(db.Integer, primary_key=True)
    is_question = db.Column(db.Boolean)
    post_id = db.Column(db.Integer, db.ForeignKey('posts.id'))
    question = db.relationship('Post', backref=db.backref('answer', lazy='dynamic'), uselist=False, lazy='dynamic')

The error is:

ArgumentError: Post.question and back-reference Post.answer are both of the same direction symbol('ONETOMANY'). Did you mean to set remote_side on the many-to-one side ?

nalzok
  • 14,965
  • 21
  • 72
  • 139

2 Answers2

3

You need to add remote_side argument to create self-referenced relation. More information in documentaion.

UPDATED: by the way, I think you don't need boolean flag is_question, because you can determine questions and answers by checking post_id field is Null or not.

class Post(Base):
    __tablename__ = 'posts'
    id = Column(Integer, primary_key=True)
    post_id = Column(Integer, ForeignKey('posts.id'))
    question = relationship('Post', remote_side=[id], backref=backref('answers'), uselist=False)

Test:

session.add(
    Post(
        id=1,
        post_id=None
    )
)
session.add(
    Post(
        id=2,
        post_id=1
    )
)

session.add(
    Post(
        id=3,
        post_id=1
    )
)

session.commit()

question = session.query(Post).get(1)
print question.answers  # output [post2, post3]
answer = session.query(Post).get(2)
print answer.question.id  # output 1

# Receive all answers
print session.query(Post).filter(Post.post_id.isnot(None)).all()
ShabashP
  • 578
  • 2
  • 10
0

You can use the below question and answer table.

class Answer(Base):
        __tablename__="answers"
        id = Column(Integer, primary_key=True)
        mcq_id = Column(Integer,ForeignKey('questions.id'))
        answer_text = Column(Text())
        is_correct = Column(Boolean, nullable=False, default=False)

    class Question(Base):
        __tablename__="questions"
        id = Column(Integer, primary_key=True)
        question_text = Column(Text())
        answer_explanation = Column(Text())
        answer_choices = relationship('Answer',
                                     primaryjoin="and_(Question.id == Answer.mcq_id )",
                                     cascade="all, delete-orphan",
                                     foreign_keys=[Answer.mcq_id])
# If you have more than one answers then define this function in your model.
     def has_more_than_one_correct_answer(self):
        count = 0 
        for choice in self.answer_choices:
            if choice.is_correct:
                count = count + 1
        if count > 1:
            return True
        else:
            return False

You can see the relationship between two tables. And you can access the relationship using joinedload or joinedload_all if you are using sqlalchemy.

SumanKalyan
  • 1,681
  • 14
  • 24