CODEWITHSUNDEEP
pythonpostgresqlsqlalchemyflask-sqlalchemy
user8340738
user8340738

Reputation:

How to specify a relationship to multiple similar tables

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)

Upvotes: 0

Views: 39

Answers (1)

user8340738
user8340738

Reputation:

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)

Upvotes: 2

Related Questions