mardi 29 septembre 2015

How to do class-level queries for recursive @hybrid_property in SQLAlchemy

I have two classes in SQLAlchemy such as:

class Local(Base):
    id = Column(Unicode, primary_key=True)
    denom = Column(String)
    parent = Column(String)
    children = relationship("Local", backref=backref('parent', remote_side=[id]))

    @hybrid_property
    def anp(self):
        if self.denom.startswith(u"ANP_"):
            return self.denom[:6]
        elif self.parent:
            return self.parent.anp
        return None


class Equipament(Base):
    id = Column(Unicode, primary_key=True)
    local = relationship("Local", backref=backref('eqps', order_by=id))

    @hybrid_property
    def anp(self):
        if self.local is None:
            return None
        else:
            return self.local.anp

This works when checking for the anp property such as when equipment_instance.anp, but how do I build a proper query when trying to list all available equipments which meet a certain criteria for the property?

For example, I may try:

>>> loc = db.session.query(db.Local).first()
>>> loc.anp
u'ANP_MF'

But I can't do:

>>> loc = db.session.query(db.Local).filter(db.Local.anp==u'ANP_MF').first()
Traceback (most recent call last):
...
TypeError: Boolean value of this clause is not defined

Alternatively, I might use:

>>> db.session.query(db.Local).filter(db.Local.denom.like(u"ANP_%")).first()

But then I'd have to recurse into its children and join results manually.

So, is it possible to fix this so a simpler query might be used? I'm using SQLAlchemy 1.0.5 with SQLite.

Aucun commentaire:

Enregistrer un commentaire