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.
