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?
On Thursday, December 17, 2020 at 10:42:28 AM UTC-5 Mike Bayer wrote:
> 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.
>
>
>
>
>
>
>
> On Thu, Dec 17, 2020, at 10:21 AM, Gmoney wrote:
>
> 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
>
>
> --
> 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
>
> <https://groups.google.com/d/msgid/sqlalchemy/3e4cd06e-8d7f-4471-81f9-6ecbf8f236ban%40googlegroups.com?utm_medium=email&utm_source=footer>
> .
>
>
>
--
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+unsubscr...@googlegroups.com.
To view this discussion on the web visit
https://groups.google.com/d/msgid/sqlalchemy/cf1dd91d-bdf7-4960-a35a-e5e6d93603ean%40googlegroups.com.
