0

I have a Quiz model that can have different types of questions, namely Numeric and Text based.

I have set up each type as a separate table, but wish to be able to access all of them by a relationship on the Quiz model.

As an extra, I would also appreciate if you could help me work out how to then make it order_by a column that all question types will have.

I have read through the sqlalchemy docs and think maybe it might have something to do with composite secondary joins?

class Quiz(db.Model):
    id = db.Column(db.Integer, primary_key=True)

    questions = db.relationship('Question', back_populates="centre", lazy="dynamic")

    owner_id = db.Column(db.Integer, db.ForeignKey('user.id'))
    owner = db.relationship("User", back_populates="child")


class NumericQuestion(Question):
id = db.Column(db.Integer, primary_key=True)
    text = db.Column(db.Text, nullable=False)

    quiz_id = db.Column(db.Integer, db.ForeignKey('quiz.id'), nullable=False)
    quiz = db.relationship('Quiz', back_populates="questions")

    answer = db.Column(db.Numeric(precision=12, scale=5))


class TextQuestion(Question):
id = db.Column(db.Integer, primary_key=True)
    text = db.Column(db.Text, nullable=False)

    quiz_id = db.Column(db.Integer, db.ForeignKey('quiz.id'), nullable=False)
    quiz = db.relationship('Quiz', back_populates="questions")

    answer = db.Column(db.Text)
Ilja Everilä
  • 50,538
  • 7
  • 126
  • 127
  • What you seem to be after is a generic association / relationship: https://stackoverflow.com/questions/17688224/sqlalchemy-generic-relationship-simple-example, https://stackoverflow.com/questions/17703239/sqlalchemy-generic-foreign-key-like-in-django-orm. On the other hand what you have is an inheritance pattern, so you could probably instead leverage polymorphism, if you'd use joined inheritance or single table inheritance. I'm not a 100% sure, but I suppose a polymorphic relationship is also possible with concrete table inheritance, using `polymorphic_union()` or such. – Ilja Everilä Jul 31 '19 at 07:48

1 Answers1

2

Managed to work this one out thanks to Ilja Everilä.

The trick is to use sqlalchemy polymorphic inheritance.

class QuestionType(enum.Enum):
    Numeric = 0
    Text = 1

    @property
    def type(self):
        return {
            QuestionType.Numeric: NumericQuestion,
            QuestionType.Text: TextQuestion
        }[self]

class Quiz(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    name = db.Column(db.String(30), unique=True, nullable=False)

    questions: flask_sqlalchemy.BaseQuery = db.relationship('Question', back_populates="quiz",
                                                            order_by="Question.sort_index")

    owner_id = db.Column(db.Integer, db.ForeignKey('user.id'))
    owner = db.relationship("User", back_populates="quizzes")


class Question(db.Model):
    __mapper_args__ = {'polymorphic_on': "type"}

    def __init__(self, *args, **kwargs):
        raise NotImplementedError

    id = db.Column(db.Integer, primary_key=True)
    text = db.Column(db.Text, nullable=False)
    sort_index = db.Column(db.Integer, nullable=False)

    type = db.Column(db.Enum(QuestionType), nullable=False)

    quiz_id = db.Column(db.Integer, db.ForeignKey('quiz.id'), nullable=False)
    quiz = db.relationship('Quiz', back_populates="questions")


class NumericQuestion(Question):
    __tablename__ = "numericQuestion"
    __mapper_args__ = {'polymorphic_identity': QuestionType.Numeric}

    def __init__(self, *args, **kwargs):
        super(Question, self).__init__(*args, **kwargs)

    id = Column(db.Integer, db.ForeignKey('question.id'), primary_key=True)
    answer = db.Column(db.Numeric(precision=12, scale=5))  # 0000000.00000


class TextQuestion(Question):
    __tablename__ = "textQuestion"
    __mapper_args__ = {'polymorphic_identity': QuestionType.Text}

    def __init__(self, *args, **kwargs):
        super(Question, self).__init__(*args, **kwargs)

    id = Column(db.Integer, db.ForeignKey('question.id'), primary_key=True)
    answer = db.Column(db.Text)