In general, if you want objects to be populated as part of the normal
loading process there needs to be a relationship() to catch it. This
can be achieved in this specific case like the following:
class CustomerLatestShipmentTransformer(hybrid.Comparator):
@property
def cte(self):
def transform(q):
subq = (
q.session.query(
ProductSKUShipment.customer_id,
ProductSKUShipment.shipment_date,
ProductSKUShipment.order_no,
)
.distinct(ProductSKUShipment.customer_id) # Postgres
specific
.order_by(
ProductSKUShipment.customer_id,
ProductSKUShipment.shipment_date.desc(),
)
)
cte = subq.cte()
return q.options(
sa.orm.contains_eager('_latest_sku_shipment')).\
outerjoin(cte, cte.c.customer_id == Customer.id)
return transform
class Customer(Base):
__tablename__ = 'customer'
id = sa.Column(sa.Integer, primary_key=True)
no = sa.Column(sa.Text, nullable=False)
name = sa.Column(sa.Text, nullable=False)
def __repr__(self):
return '<{no}: {name}>'.format(**vars(self))
# you'd want to make this into a full blown "latest element"
# relationship if you need it to be accessed in any other way
# besides the special query here
_latest_sku_shipment = sa.orm.relationship(
"ProductSKUShipment", lazy="noload")
@hybrid.hybrid_property
def latest_sku_shipment(self):
return self._latest_sku_shipment
@latest_sku_shipment.comparator
def latest_sku_shipment(cls):
return CustomerLatestShipmentTransformer(cls)
also thanks for reminding me about query.with_transformation()! I had
zero recollection of this method or the recipe in hybrids! it's like
I'm reading it for the first time, very strange.
On 02/19/2016 07:17 AM, Jacob Magnusson wrote:
Hi,
I have this case where I want to be able to access a hybrid_property
that points to a related model which is further filtered using a cte
(utilizing a Transformer
<http://docs.sqlalchemy.org/en/latest/orm/extensions/hybrid.html#building-transformers>)
without having to trigger additional database requests. So what I'm
trying to achieve looks something like this:
|
# Python code
customers
=session.query(Customer).with_transformation(Customer.latest_sku_shipment.cte).all()
# Jinja2 code
{%forcustomer incustomers %}
<h1>{{customer.name }}</h1>
<p>Latestshipment,{{customer.latest_sku_shipment.shipment_date }},was
sent to customer on {{customer.latest_sku_shipment.apa }}</p>
{%endfor %}
|
Here's an example I've built that shows that it tries to access the
regular property. Is there any way that it can get the data from the
query that has already been executed?
|
importsqlalchemy assa
fromsqlalchemy.ext.declarative importdeclarative_base
fromsqlalchemy.ext importhybrid
Base=declarative_base()
Session=sa.orm.sessionmaker()
classCustomerLatestShipmentTransformer(hybrid.Comparator):
@property
defcte(self):
deftransform(q):
subq =(
q.session.query(
ProductSKUShipment.customer_id,
ProductSKUShipment.shipment_date,
ProductSKUShipment.order_no,
)
.distinct(ProductSKUShipment.customer_id)# Postgres specific
.order_by(
ProductSKUShipment.customer_id,
ProductSKUShipment.shipment_date.desc(),
)
)
cte =subq.cte()
returnq.outerjoin(cte,cte.c.customer_id ==Customer.id)
returntransform
classCustomer(Base):
__tablename__ ='customer'
id =sa.Column(sa.Integer,primary_key=True)
no=sa.Column(sa.Text,nullable=False)
name =sa.Column(sa.Text,nullable=False)
def__repr__(self):
return'<{no}: {name}>'.format(**vars(self))
@hybrid.hybrid_property
deflatest_sku_shipment(self):
# sess = sa.orm.object_session(self)
# return (
# sess.query(ProductSKUShipment)
# .filter_by(customer_id=self.id)
# .order_by(ProductSKUShipment.shipment_date.desc())
# .first()
# )
raiseRuntimeError('I want to be loaded from my query!')
@latest_sku_shipment.comparator
deflatest_sku_shipment(cls):
returnCustomerLatestShipmentTransformer(cls)
classProductSKUShipment(Base):
__tablename__ ='productskushipment'
def__repr__(self):
return'<{sku} {order_no} ({shipment_date})>'.format(**vars(self))
id =sa.Column(sa.Integer,primary_key=True)
sku =sa.Column(sa.Text,unique=True)
quantity =sa.Column(sa.Integer,nullable=False)
order_no =sa.Column(sa.Text,nullable=False)
shipment_date =sa.Column(sa.DateTime(timezone=True),nullable=False)
customer_id =sa.Column(
sa.Integer,
sa.ForeignKey(Customer.id,ondelete='CASCADE'),
nullable=False)
customer =sa.orm.relationship(
Customer,
backref=sa.orm.backref('sku_shipments',passive_deletes='all'),
)
customer =Customer(
no='123',
name='My customer',
)
sku_shipment =ProductSKUShipment(
customer=customer,
order_no='12345',
sku='20090-100',
quantity=1,
shipment_date='2015-01-01 08:34',
)
sku_shipment_2 =ProductSKUShipment(
customer=customer,
order_no='12345',
sku='10570-900',
quantity=1,
shipment_date='2016-02-19 13:03',
)
engine =sa.create_engine('postgresql://localhost/cachedproptest')
Session.configure(bind=engine)
Base.metadata.create_all(bind=engine)
session =Session()
session.add(customer)
session.flush()
q =(
session.query(Customer)
.with_transformation(Customer.latest_sku_shipment.cte)
# .options(sa.orm.joinedload('latest_sku_shipment'))
)
forcustomer inq:
print(customer.latest_sku_shipment)
|
--
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
<mailto:sqlalchemy+unsubscr...@googlegroups.com>.
To post to this group, send email to sqlalchemy@googlegroups.com
<mailto:sqlalchemy@googlegroups.com>.
Visit this group at https://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.
--
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.
