Sqlalchemy returning relationship with nested join using marshmallow

Question

Currently I have a bunch of tables with clear relationships set up. However, I want one table (Application) that has a relationship to an Answer table and a Question table to only show answers that have Questions that are marked as active. I'm trying to do this via the Application model so that in Marshmallow it can return inside of application. I'm also trying to do this without producing an N+1 query.

Tables

application_question_table = db.Table(
    'application_questions',
    db.Column(
        'application_id',
        db.Integer,
        db.ForeignKey(
            'application.id',
            name="application_questions_application_id_fkey",
            ondelete="CASCADE"
        )
    ),
    db.Column(
        'question_id',
        db.Integer,
    db.ForeignKey(
            'question.id', 
            name="application_questions_question_id_fkey",
            ondelete="CASCADE"
        )
    )
)

class Application(Base):
    id = db.Column(db.Integer, primary_key=True)
    answers = db.relationship('Answer', back_populates="application", cascade=‘delete)
    active_answers = db.relationship("should only show answerswhere question is active")
    questions = db.relationship('Question', lazy="dynamic", secondary=application_question_table)


class Answer(Base):
    id = db.Column(db.Integer, primary_key=True)
    question_id = db.Column(db.Integer, db.ForeignKey('question.id', ondelete="SET NULL", name="answer_question_id_fkey"))
    question = db.relationship('Question', back_populates=“answers")


class Question(Base):
    id = db.Column(db.Integer, primary_key=True)
    answers = db.relationship("Answer", back_populates="question")
    applications = db.relationship('Application',lazy="dynamic", secondary=application_question_table)
    active = db.Column(db.Boolean, default=True)

Schema - Note the get visible questions is essentially what i want to happen. But i don't like doing it this way because it'll be an additional query for every single application i find.

class ApplicationSchema(ModelSchema):
    visible_answers = fields.Method('get_visible_answers')
    class Meta:
        model = Application

    def get_visible_answers(self, application):
        schema = ApplicationAnswerSchema(many=True)
        answers = Answer.query.join(
            Answer.application,
            Answer.question
        ).filter(
            Application.id == application.id,
            Question.active == True,
        ).all()
        return schema.dump(answers).data


class ApplicationAnswerSchema(ModelSchema):

    class Meta:
        model = Answer

My expected behavior is when I do something like

applications = Application.query.options(joinedload(Application.active_answers)).all()
schema = ApplicationSchema(many=True)
results = schema.dump(applications)

That there will only be one query and I can key into every application and get the visible answers.

The actual result i'm getting is that i'm getting an additional query emitted for every application.

Sqlalchemy returning relationship with nested join using marshmallow

0 Answers0