CODEWITHSUNDEEP
pythonsqlalchemy
nPn
nPn

Reputation: 16768

How do run a query within a sqlalchemy hybird expression?

Have a class Run that has a "has many" relationship to a class Tag. I need to be able to filter runs based on a function of the tags (tag_collection).

Below is a snippet of my code. In this case I am using the automapper, but I don't think that matters too much.

Base = automap_base()

class Run(Base):
    __tablename__ = 'runs'
    macro_id = Column(ForeignKey('macros.id'))  

    @hybrid_property
    def tag_count(self):
        return(len(self.tag_collection))

    @hybrid_property
    def default_region(self):
        return(reduce(lambda  memo, t: memo or t.name == "region" and t.value == "default", self.tag_collection, False))

    @default_region.expression
    def default_region(cls):
        # how do I do the query here?

class Tag(Base):
    __tablename__ = 'tags'
    run_id = Column(ForeignKey('runs.id'))

If I get an instance of a Run, I can make use of the default_region property, but when I run a query, and try to filter using the default_region property, I either get the Run class passed in (as self) in the @hybird_property decorated default_region method or I end up in the @default_region.expression decorated default_region method, which also receives the Run class.

Example query:

session.query(Run).filter(Run.default_region == True).all()

I think I need to do a query in the @default_region.expression decorated default_region method, but I don't see how to get a handle on the session. I know about Session.object_session(someobject), but I think someobject needs to be an instance of the orm class not the class itself.

I think I am either completely wrong about my methodology of the filtering, or I need to somehow get a handle to the session object in that @default_region.expression decorated default_region method.

Is there a better way to accomplish this filtering?

How do a get the session in the@default_region.expression decorated default_region method?

Upvotes: 2

Views: 937

Answers (1)

Martijn Pieters
Martijn Pieters

Reputation: 1125078

This looks to me to be a Correlated Subquery Relationship Hybrid. When Run.default_region is added as a filter, you want to update the query to add

WHERE EXISTS (
    SELECT 1 FROM tags
    WHERE run_id = runs.id
      AND name = "region"
      AND value = "default")

Return that as an expression, with the exists() function:

from sqlalchemy.sql import exists, and_

@default_region.expression
def default_region(cls):
    return exists([1]).where(and_(
        Tag.run_id == cls.id,
        Tag.name == 'region',
        Tag.value == 'default'))

Note that you want to avoid using == True or == False here, there is no need! Just use the property directly:

session.query(Run).filter(Run.default_region).all()

or use ~ to negate:

session.query(Run).filter(~Run.default_region).all()

By defining the .expression method for the property, you have told SQLAlchemy to use that method when accessing the property on the class, so the context is always the class there. Without defining .exists (or a .comparator), Run.default_region would call the default_region getter (the method decorated with `@hybrid_property) as a class method, so only then would that method have to then work with either an instance or a class being passed in. For some hybrid property implementations, that happens to work by virtue of how they use attributes on the context.

As for the implementation of the default_region instance property, I'd not use reduce() there; use the any() function:

@hybrid_property
def default_region(self):
    return any(
        t.name == "region" and t.value == "default"
        for t in self.tag_collection)

any() stops iterating over the supplied generator expression when a match has been found.

Upvotes: 3

Related Questions