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. On Thursday, December 17, 2020 at 11:01:17 AM UTC-5 Gmoney wrote: > 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/10679cc3-fb1e-4817-bc49-79f6f096dacan%40googlegroups.com.
