python - SQLAlchemy - using hybrid property in queries - Stack Overflow

admin管理员组

文章数量:1194132

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

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

• python
• sql
• sqlalchemy

Share Improve this question Follow asked Jan 24 at 5:34 frimannfrimann 5355 bronze badges

Add a comment  | 

1 Answer 1

Sorted by: Reset to default 1

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

本文标签： pythonSQLAlchemyusing hybrid property in queriesStack Overflow