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:
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]
> <javascript:>> 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] <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.
