Alrighty I got that other case working, the issue is resolved and will be in the next 1.1 release which is 1.1.14.
On Wed, Aug 23, 2017 at 4:47 PM, Mike Bayer <[email protected]> wrote: > I've got the first half of a fix for this issue up in gerrit review > but unfortunately there are additional issues entirely on top of the > thing I just fixed specific to your example in that it involves > correlated subqueries that have to be matched. Was hoping to report a > fix today but again I have no ETA on your specific use case. > > On Sun, Aug 20, 2017 at 11:08 PM, Mike Bayer <[email protected]> wrote: >> On Sun, Aug 20, 2017 at 10:18 AM, Stephen Early >> <[email protected]> wrote: >>> I've cut my code down somewhat; I don't think it's minimal but it does >>> demonstrate the problem. It's at >>> https://gist.github.com/sde1000/89b6ed13205faf0f019431183d1511bf and also >>> pasted below: >> >> thanks. undefer_group() is not working off of an eagerload right now >> and >> https://bitbucket.org/zzzeek/sqlalchemy/issues/4048/undefer_group-from-relationship-path-has >> is added to address this (will work off of a lazyload() though). In >> addition a regression was also found in the 1.2 beta series related to >> this pattern as well. Unfortunately I don't have an ETA for the >> initial issue so you'll have to forego the use of undefer_group() in >> this situation. >> >> >> >>> >>> from sqlalchemy import create_engine >>> from sqlalchemy.ext.declarative import declarative_base >>> from sqlalchemy import Column, Integer, ForeignKey, Numeric >>> from sqlalchemy.schema import Sequence, MetaData >>> from sqlalchemy.sql.expression import text >>> from sqlalchemy.orm import sessionmaker >>> from sqlalchemy.orm import relationship, backref, column_property >>> from sqlalchemy.orm import defaultload, joinedload >>> from sqlalchemy.sql import select, func >>> >>> metadata = MetaData() >>> >>> Base = declarative_base(metadata=metadata) >>> >>> deliveries_seq = Sequence('deliveries_seq') >>> class Delivery(Base): >>> __tablename__ = 'deliveries' >>> id = Column('deliveryid', Integer, deliveries_seq, >>> nullable=False, primary_key=True) >>> >>> stock_seq = Sequence('stock_seq') >>> class StockItem(Base): >>> __tablename__ = 'stock' >>> id = Column('stockid', Integer, stock_seq, nullable=False, >>> primary_key=True) >>> deliveryid = Column(Integer, ForeignKey('deliveries.deliveryid'), >>> nullable=False) >>> delivery = relationship(Delivery, backref=backref('items', order_by=id)) >>> size = Column(Numeric(8, 1), nullable=False) >>> >>> stockout_seq = Sequence('stockout_seq') >>> class StockOut(Base): >>> __tablename__ = 'stockout' >>> id = Column('stockoutid', Integer, stockout_seq, >>> nullable=False, primary_key=True) >>> stockid = Column(Integer, ForeignKey('stock.stockid'), nullable=False) >>> stockitem = relationship(StockItem, backref=backref('out', order_by=id)) >>> qty = Column(Numeric(8, 1), nullable=False) >>> >>> StockItem.used = column_property( >>> select([func.coalesce(func.sum(StockOut.qty), text("0.0"))]).\ >>> correlate(StockItem.__table__).\ >>> where(StockOut.stockid == StockItem.id).\ >>> label('used'), >>> deferred=True, >>> group="qtys", >>> doc="Amount of this item that has been used for any reason") >>> StockItem.remaining = column_property( >>> select([StockItem.size - >>> func.coalesce(func.sum(StockOut.qty), text("0.0"))]).\ >>> where(StockOut.stockid == StockItem.id).\ >>> label('remaining'), >>> deferred=True, >>> group="qtys", >>> doc="Amount of this item remaining") >>> >>> engine = create_engine('sqlite://', echo=True) >>> metadata.bind = engine >>> metadata.create_all() >>> >>> sm = sessionmaker(bind=engine) >>> s = sm() >>> >>> # Add test data >>> delivery = Delivery() >>> item = StockItem(delivery=delivery, size=72) >>> used = StockOut(stockitem=item, qty=12) >>> >>> s.add_all([item, used]) >>> >>> s.commit() >>> >>> d_id = delivery.id >>> >>> print("\n\n\n\nTrying with no undefer option...") >>> # I expect this query to complete in two round-trips to the database, >>> # and it does. >>> s = sm() >>> d = s.query(Delivery).\ >>> filter_by(id=d_id).\ >>> options(joinedload("items")).\ >>> one() >>> print("used: {}; remaining: {}".format(d.items[0].used, >>> d.items[0].remaining)) >>> >>> print("\n\n\n\nTrying with undefer_group()...") >>> # I expect this query to complete in one round-trip to the database, >>> # but it takes two. >>> s = sm() >>> d = s.query(Delivery).\ >>> filter_by(id=d_id).\ >>> options(joinedload("items")).\ >>> options(defaultload("items").undefer_group('qtys')).\ >>> one() >>> print("used: {}; remaining: {}".format(d.items[0].used, >>> d.items[0].remaining)) >>> >>> print("\n\n\n\nTrying with separate undefer() calls...") >>> # I expect this query to complete in one round-trip to the database, >>> # and it does. >>> s = sm() >>> d = s.query(Delivery).\ >>> filter_by(id=d_id).\ >>> options(joinedload("items")).\ >>> options(defaultload("items").undefer('used').undefer('remaining')).\ >>> one() >>> print("used: {}; remaining: {}".format(d.items[0].used, >>> d.items[0].remaining)) >>> >>> >>> Stephen Early >>> >>> On Sunday, 20 August 2017 14:34:16 UTC+1, Mike Bayer wrote: >>>> >>>> I would need mappings / MCVE to work with this problem. >>>> >>>> On Aug 20, 2017 9:01 AM, "Stephen Early" <[email protected]> >>>> wrote: >>>>> >>>>> I'm having trouble with undefer_group(), which I'm sure used to work for >>>>> me but now doesn't! >>>>> >>>>> I've read the documentation regarding the changes to undefer_group() in >>>>> 0.9; I'm now using 1.0.14 >>>>> >>>>> I have a model, StockItem, with three column properties ('used', 'sold', >>>>> 'remaining') that are all deferred and in group 'qtys'. Let's say I do a >>>>> query like this (Delivery.items is a backref to StockItem): >>>>> >>>>> d = session.query(Delivery).\ >>>>> filter_by(id=int(deliveryid)).\ >>>>> options(joinedload_all('items.stocktype.unit'), >>>>> joinedload_all('items.stockline'), >>>>> defaultload("items").undefer_group('qtys')).\ >>>>> one() >>>>> >>>>> I would expect StockItem.used, StockItem.sold and StockItem.remaining to >>>>> be undeferred, but they are not. If I do this: >>>>> >>>>> d = session.query(Delivery).\ >>>>> filter_by(id=int(deliveryid)).\ >>>>> options(joinedload_all('items.stocktype.unit'), >>>>> joinedload_all('items.stockline'), >>>>> defaultload("items")\ >>>>> .undefer('used')\ >>>>> .undefer('sold')\ >>>>> .undefer('remaining')).\ >>>>> one() >>>>> >>>>> ...then it works as expected. >>>>> >>>>> The 'used', 'sold' and 'remaining' columns are added to the StockItem >>>>> class after it's defined, as follows: >>>>> >>>>> StockItem.used = column_property( >>>>> select([func.coalesce(func.sum(StockOut.qty), text("0.0"))]).\ >>>>> correlate(StockItem.__table__).\ >>>>> where(StockOut.stockid == StockItem.id).\ >>>>> label('used'), >>>>> deferred=True, >>>>> group="qtys", >>>>> doc="Amount of this item that has been used for any reason") >>>>> >>>>> Any ideas? >>>>> >>>>> Stephen Early >>>>> >>>>> -- >>>>> 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 post to this group, send email to [email protected]. >>>>> Visit this group at https://groups.google.com/group/sqlalchemy. >>>>> For more options, visit https://groups.google.com/d/optout. >>> >>> -- >>> 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 post to this group, send email to [email protected]. >>> Visit this group at https://groups.google.com/group/sqlalchemy. >>> For more options, visit https://groups.google.com/d/optout. -- 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 post to this group, send email to [email protected]. Visit this group at https://groups.google.com/group/sqlalchemy. For more options, visit https://groups.google.com/d/optout.
