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 <mike...@zzzcomputing.com> wrote: > On Sun, Aug 20, 2017 at 10:18 AM, Stephen Early > <s...@individualpubs.co.uk> 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" <s...@individualpubs.co.uk> >>> 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 sqlalchemy+...@googlegroups.com. >>>> To post to this group, send email to sqlal...@googlegroups.com. >>>> 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 sqlalchemy+unsubscr...@googlegroups.com. >> To post to this group, send email to sqlalchemy@googlegroups.com. >> 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 sqlalchemy+unsubscr...@googlegroups.com. To post to this group, send email to sqlalchemy@googlegroups.com. Visit this group at https://groups.google.com/group/sqlalchemy. For more options, visit https://groups.google.com/d/optout.
