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
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 Homework
s 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))