HowTo define hybridproperty.expressions referencing relationship attributes

[Gmoney]

Trying to understand the right way to define a hybridproperty.expression that references an 'sub-attribute' of a relationship attribute.  I have done it in another case by using the class name of the relationship attribute (Tire.weight vs. cls.tire.weight) and that's worked OK.  But it begs the question how to do it if that class (Tire) is used in multiple relationship attributes.  How does the .expression discern between the two.

Hopefully the example below explains better than my words probably have.  See the final .expression "weight_of_tires"... how to make it reference the front_tire attribute and rear_tire attribute - both of the same model class - distinctly?  This sample is using flask-alchemy - hopefully that doesn't confuse things.

class Tire(db.Model):

    __tablename__ = 'tire'

    id = Column(Integer, primary_key=True)

    weight = Column(Integer)

    size = Column(VARCHAR(50))

class Motorcycle(db.Model):

    __tablename__ = 'motorcycle'

    id = Column(Integer, primary_key=True)

    front_tire_id = Column(ForeignKey(Tire.id))

    rear_tire_id = Column(ForeignKey(Tire.id))

    front_tire = relationship(Tire, foreign_keys=[front_tire_id], lazy='joined')

    rear_tire = relationship(Tire, foreign_keys=[rear_tire_id], lazy='joined')

    @hybrid_property

    def weight_of_tires(self):

        return self.front_tire.weight + self.rear_tire.weight

    @weight_of_tires.expression

    def weight_of_tires(cls):

        # return Tire.weight + Tire.weight

        # return cls.front_tire.weight + cls.rear_tire.weight

[Mike Bayer]

this kind of issue should be approached by thinking in SQL.   the reason one wants to use a hybrid property at the class level is so that one could say:

session.query(Motorcycle).filter(Motorcycle.weight_of_tires > 30)

OK.  So what SQL would be needed for that to work?    it's actually not very straightforward, as the SQL we would most likely use without using a hybrid expression would be a JOIN to the Tire table twice using aliases, then the WHERE criteria would need to refer to those aliases:

ft = aliased(Tire)

rt = aliased(Tire)

session.query(Motorcycle).join(Motorcycle.front_tire.of_type(ft)).\

    join(Motorcycle.rear_tire.of_type(rt)).filter((ft.weight + rt.weight) > 30)

Because our hybrid expression is not able to imply a JOIN, for the above to work we would have no choice but to make use of correlated subqueries, so for hybrid expression to work, one way would be to construct two select() constructs that correlate back to Motorcycle and then use them as expressions, or slightly better a single correlated subquery that does both.  this is not too unlike the EXISTS query we get when we do an expression like Cls.relationship.has().

a rough sketch of the latter concept would look like:

@weight_of_tires.expression

def weight_of_tires(cls):

    ft = aliased(Tire)

    rt = aliased(Tire)

    return select([ft.weight + rt.weight]).where(cls.front_tire_id == ft.id).where(cls.rear_tire_id == rt.id).as_scalar()

note that the above query will generally be a poor performer compared to our pure SQL version that would use JOINs instead of correlated subqueries, however, I would suggest running a query plan on your target database to see if it is in fact an issue.

--

SQLAlchemy -

The Python SQL Toolkit and Object Relational Mapper

 

http://www.sqlalchemy.org/

 

To post example code, please provide an MCVE: Minimal, Complete, and Verifiable Example. See http://stackoverflow.com/help/mcve for a full description.

---

You received this message because you are subscribed to the Google Groups "sqlalchemy" group.

To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy+...@googlegroups.com.

To view this discussion on the web visit https://groups.google.com/d/msgid/sqlalchemy/3e4cd06e-8d7f-4471-81f9-6ecbf8f236ban%40googlegroups.com.

[Gmoney]

I think I follow you... definitely about how it would be done in SQL and if constructing a query manually.  I think I wasn't fully clear about what level of dynamic query building an .expression could drive.

If I'm understanding correctly, it sounds like in our existing simpler case, we may just be getting lucky that it works...  assume the same example but for a unicycle ;)  just 'tire' (now only a single relationship attribute of that type)

@weight_of_tires.expression

def weight_of_tires(cls):

        return Tire.weight

This actually works for us now in a couple situations.  But I'm guessing it's because we are already referencing the unicycle.tire relationship attribute which triggers the join.  The .expression itself would not trigger the join on it's own and if used in a query filter alone, it wouldn't work - maybe end up with a cartesian join without an ON clause.

Am I on the right track there?

[Gmoney]

Actually ignore that last message.  I see why that case is working for us now... we are introducing the 'Tire' join explicitly for another reason in the case I was thinking of, so that .expression accidentally (on our part) just happens to work.  If I just query for unicycle.weight_of_tires by itself, it has the same issues.

Think I'm getting it... thanks so much for the explanation.