Just a little note to myself and to others who may be looking to solve
this. I couldn't get it to work using Mike's suggestions unfortunately, but
I found somewhat of a workaround by using deferred column_properties like
the code below. Note that you can actually just pass in a regular function
to *with_transformation*. I would love to hear if anyone can get it working
the way Mike suggested as it would be more clean to access the related
object directly.
import sqlalchemy as sa
import sqlalchemy.orm # noqa
from sqlalchemy.ext import declarative
Base = declarative.declarative_base()
Session = sa.orm.sessionmaker()
def customer_latest_shipment_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(name='latest_sku_shipment')
q = (
q.outerjoin(cte, cte.c.customer_id == Customer.id)
.options(sa.orm.undefer('latest_sku_shipment_date'))
.options(sa.orm.undefer('latest_sku_order_no'))
)
return q
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)
latest_sku_shipment_date = sa.orm.column_property(
sa.select([sa.text('latest_sku_shipment.shipment_date')]),
deferred=True,
)
latest_sku_order_no = sa.orm.column_property(
sa.select([sa.text('latest_sku_shipment.order_no')]),
deferred=True,
)
def __repr__(self):
return '<{no}: {name}>'.format(**vars(self))
class ProductSKUShipment(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',
echo=True)
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_shipment_transform)
)
# Print all the latest customer shipments (one in this case)
for customer in q:
print(customer.latest_sku_shipment_date, customer.latest_sku_order_no)
# Get all customers who've had a shipment @ Jan 1 2015 or later
print(q.filter(Customer.latest_sku_shipment_date >= '2015-01-01').one())
On Friday, February 19, 2016 at 4:26:15 PM UTC+1, Mike Bayer wrote:
>
> 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+...@googlegroups.com <javascript:>
> > <mailto:sqlalchemy+unsubscr...@googlegroups.com <javascript:>>.
> > To post to this group, send email to sqlal...@googlegroups.com
> <javascript:>
> > <mailto:sqlal...@googlegroups.com <javascript:>>.
> > 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.
