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 [email protected].
> 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 [email protected].
To view this discussion on the web visit
https://groups.google.com/d/msgid/sqlalchemy/c75e99e3-9700-4fbf-a555-26d0aa518ae7%40www.fastmail.com.
