pythonsqlsqlalchemy

SQLAlchemy - using hybrid property in queries


Say I have a Question which belongs to some Homework. A Question can be correctly answered, and Homework is passed if all Questions are correct.

class Question(Base):
    __tablename__ = 'questions'
    id: Mapped[int] = mapped_column(primary_key=True)
    answered_correctly: Mapped["bool"] = mapped_column()
    homework: Mapped["Homework"] = relationship(back_populates="questions")
    homework_id: Mapped[int] = mapped_column(ForeignKey("homework.id"))


class Homework(Base):
    __tablename__ = 'homework'
    id: Mapped[int] = mapped_column(primary_key=True)
    answered_correctly: Mapped["bool"] = mapped_column()
    questions: Mapped[list["Questions"]] = relationship(
        back_populates="homework", default_factory=list
    )

    @hybrid_property
    def passed(self):
        return all([question.answered correctly for question in self.questions])

How do I get this to work in an SQL query? I know that I have to implement passed.expression, but I'm unclear on what exactly it should return.

I have an SQL query that I can use to find homework that isn't passed - do I have to translate this query to SQLAlchemy's API, & that's what passed.expression is supposed to return?

SELECT homework.id, from homework
JOIN question on question.id = homework.id
WHERE homework.id = &1
GROUP BY homework.id
HAVING COUNT(CASE WHEN question.answered_correctly = FALSE THEN 1 END) > 0

Solution

  • The hybrid expression needs to return the where clause criteria that would select the rows that you want to have returned. In this case you want to return any Homeworks that only have questions that are answered correctly. An exists query will do this, for example:

    import sqlalchemy as sa
    ...
    
    sa.select(Homework).where(~Homework.questions.any(Question.answered_correctly.is_not(False))
    

    This will generate this SQL:

    SELECT homework.id 
    FROM homework 
    WHERE (NOT (EXISTS (SELECT 1 
    FROM questions 
    WHERE homework.id = questions.homework_id AND questions.answered_correctly IS NOT 1))) IS 1
    

    If you can guarantee that answered_correctly is never null then you can change the is_not(True) to is_(False), which will generate the more readable

    SELECT homework.id 
    FROM homework 
    WHERE NOT (EXISTS (SELECT 1 
    FROM questions 
    WHERE homework.id = questions.homework_id AND questions.answered_correctly IS 0))
    

    So your expression should look like this:

    class Homework(Base):
       ...
       @passed.inplace.expression
        @classmethod
        def _passed_expression(cls):
            return ~cls.questions.any(Question.answered_correctly.is_not(True))