SQLAlchemy - create expression for complex hybrid property

Question

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 in states):
        return State.query.get("DUMMY").text
    if all((state == "FAKE" or state == "DUMMY") for state in states):
        return State.query.get("FAKE").text
    return State.query.get("INVALID").text

And I want to query it in my resource like this:

valid_text = State.query.get("FAKE").text
return data_layer.model.query.filter_by(state=valid_text) # Where data_layer.model is MyModel

But I get an empty array. Doing simply just data_layer.model.query.all() gets me the data so the logic works.

I understand I may need to create an expression for my property instead, but every example I've found are for much simpler use cases.

I tried with this:

@state.expression
def state(cls):
    states = [dummy.state_id for dummy in self.dummies.all()]
    all_dummies = all(state == "DUMMY" for state in states)
    all_fakes_or_dummies = all(
        (state == "FAKE" or state == "DUMMY") for state in states
    )
    dummy_text = State.query.get("DUMMY").text
    fake_text = State.query.get("FAKE").text
    invalid_text = State.query.get("INVALID").text

    return case(
        [
            (
                all_dummies,
                dummy_text,
            ),
            (
                all_fakes_or_dummies,
                fake_text,
            ),
        ],
        else_=invalid_text,
    )

But my resource now returns sqlalchemy.exc.ArgumentError: Ambiguous literal: False. Use the 'text()' function to indicate a SQL expression literal, or 'literal()' to indicate a bound value.

I wonder how could I correctly implement this python logic to be compatible for SQLAlchemy, I guess that must be the problem. Also I wonder whether making such complex logic at a hybrid property is a good practice at all.

van · Accepted Answer · 2021-05-16 12:10:59Z

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.

Collectives™ on Stack Overflow

SQLAlchemy - create expression for complex hybrid property

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related