That's an amazingly quick response! Thank you very much. Stephen Early
On Thursday, 24 August 2017 16:02:41 UTC+1, Mike Bayer wrote: > > 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] > <javascript:>> 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] > <javascript:>> wrote: > >> On Sun, Aug 20, 2017 at 10:18 AM, Stephen Early > >> <[email protected] <javascript:>> 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] <javascript:>. > >>> To post to this group, send email to [email protected] > <javascript:>. > >>> 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.
