SQLAlchemy - Create Expression For Complex Hybrid Property

I have a hybrid property like this in MyModel: @hybrid_property def state(self): states = [dummy.state_id for dummy in self.dummies.all()] if all(state == 'DUMMY' for state

Solution 1:

In your case, the NOT EXIST-based query building should give you the result you desire and at the same time produce easy to read queries.

As you, I attempted to decompose the checks into some independent blocks and ended up with the following:

class MyModel(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    name = db.Column(db.String)

    # ...

    # Hybrid Properties
    @hybrid_property
    def has_only_dummies(self):
        # this is not optimal as need to reiterate over all objects
        states = [dummy.state_id for dummy in self.dummies.all()]
        return all(state == "DUMMY" for state in states)

    @hybrid_property
    def has_only_dummies_or_fake(self):
        # this is not optimal as need to reiterate over all objects
        states = [dummy.state_id for dummy in self.dummies.all()]
        return all(state in ("DUMMY", "FAKE") for state in states)

    @hybrid_property
    def state(self):
        # could reuse other hybrid properties here, but it is not efficient at all
        res = None
        states = [dummy.state_id for dummy in self.dummies.all()]
        if all(state == "DUMMY" for state in states):
            res = "DUMMY"
        elif all((state == "FAKE" or state == "DUMMY") for state in states):
            res = "FAKE"
        else:
            res = "INVALID"
        return res


    # Hybrid Expressions
    @has_only_dummies.expression
    def has_only_dummies(cls):
        subq = (
            exists()
            .where(Dummy.model_id == cls.id)
            .where(~Dummy.state_id.in_(["DUMMY"]))
        ).correlate(cls)

        return select([case([(subq, False)], else_=True)]).label("only_dum")

    @has_only_dummies_or_fake.expression
    def has_only_dummies_or_fake(cls):
        subq = (
            exists()
            .where(Dummy.model_id == cls.id)
            .where(~Dummy.state_id.in_(["DUMMY", "FAKE"]))
        ).correlate(cls)

        return select([case([(subq, False)], else_=True)]).label("only_dum_or_fak")

    @state.expression
    def state(cls):
        return db.case(
            [
                (cls.has_only_dummies, "DUMMY"),
                (cls.has_only_dummies_or_fake, "FAKE"),
            ],
            else_="INVALID",
        )

In this case you can build a queries like below including filtering:

q = session.query(MyModel, MyModel.has_only_dummies, MyModel.has_only_dummies_or_fake, MyModel.state)
q = session.query(MyModel, MyModel.state)
q = session.query(MyModel).filter(MyModel.state != "INVALID")

The MyModel.state is not exactly what you desire (it is state_id instead of text), but getting the text is another step on top which is easy to implement in case you really need it.