in set (0.00 sec)
I tried adding `anon_1.customer_id = 46` to the outer query's WHERE clause
since I'm pretty sure that's easily expressed with SQLAlchemy, but it didn't
help at all. Would it be easy to keep OrderCount defined as a relationship,
but somehow add a filter to the mapped select at runtime? Seems like then,
I'd be all set.
Thank you for the help.
On Wed, Sep 7, 2011 at 2:39 PM, Michael Bayer mike...@zzzcomputing.comwrote:
On Sep 7, 2011, at 5:09 PM, Sumeet Agarwal wrote:
Still getting the hang of SQLAlchemy terminology.
I didn't mean a simple deferred() column, but a deferred column_property()
which defines a scalar select(). Like in the example here
http://www.sqlalchemy.org/docs/orm/mapper_config.html#sql-expressions-as-mapped-attributes
.
My goal is to, instead of including a correlated subquery in the SELECT.
The column_property looks like
My goal is to..., missing a verb there :). If it were me, I'd think
you're asking to unwrap the correlated subquery into a plain JOIN. Since
that works more efficiently.
I'd rather do a separate query to load num_orders, rather than getting it
lazily or by using undefer(). It almost feels like I might want to define
num_orders as a relationship somehow? I dunno if what I would like to do
maps cleanly to a SQLAlchemy pattern.
Using relationship() is kind of the old school approach, but yup I think
that actually allows you to get exactly subqueryload() for an attribute, so
good idea ! a rare moment that SQLAlchemy amazes even me. here's that
since its faster for me to just illustrate
from sqlalchemy import *
from sqlalchemy.orm import *
from sqlalchemy.ext.declarative import declarative_base
Base= declarative_base()
class Customer(Base):
__tablename__ = 'customer'
id = Column(Integer, primary_key=True)
orders = relationship(Order)
@property
def num_orders(self):
return self._num_orders.count if self._num_orders else 0
class Order(Base):
__tablename__ = 'order'
id = Column(Integer, primary_key=True)
customer_id = Column(Integer, ForeignKey('customer.id'))
class OrderCount(object):
pass
order_counts = select([Order.customer_id,
func.count(Order.id).label('count')]).\
group_by(Order.customer_id).alias()
mapper(
OrderCount,
order_counts,
primary_key=order_counts.c.customer_id
)
Customer._num_orders = relationship(OrderCount, uselist=False)
e = create_engine('sqlite://', echo=True)
Base.metadata.create_all(e)
s = Session(e)
s.add_all([
Customer(orders=[Order(), Order(), Order()]),
Customer(orders=[Order(), Order()]),
Customer(orders=[]),
])
s.commit()
for c in s.query(Customer).options(subqueryload(_num_orders)):
print c.id, c.num_orders
On Wed, Sep 7, 2011 at 7:51 AM, Michael Bayer mike...@zzzcomputing.comwrote:
For a deferred() itself, we don't have an option that does this.Though
this is an unusual request. If you definitely want the deferreds to load,
what's the issue having them render inline into the original query ?The
advantage to subqueryload is primarily in that it loads multiple rows per
parent object efficiently, without re-fetching the full parent row many
times, or needing to use a less efficient OUTER JOIN. A deferred is
always one row per parent - and no join is needed anyway.
On Sep 6, 2011, at 6:49 PM, Sumeet Agarwal wrote:
I have a collection of deferred `ColumnProperty`s that I'd like to
start loading in subsequent queries. I know you can get this to happen
on the first access of a `ColumnProperty` that is deferred, but I
wonder if there's any way to specify this in the query.
For relationships, `joinedload[_all()]` has a counterpart
`subqueryload[_all()]`. Is there nothing similar we can do for
`ColumnProperty`?
--
You received this message because you are subscribed to the Google
Groups sqlalchemy group.
To post to this group, send email to sqlalchemy@googlegroups.com.
To unsubscribe from this group, send email to
sqlalchemy+unsubscr...@googlegroups.com.
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en.
--
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To post to this group, send email to sqlalchemy@googlegroups.com.
To unsubscribe from this group, send email to
sqlalchemy+unsubscr...@googlegroups.com.
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en.
--
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To post to this group, send email to sqlalchemy@googlegroups.com.
To unsubscribe from this group, send email to
sqlalchemy+unsubscr...@googlegroups.com.
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en.
--
You received this message because you are subscribed to the Google Groups
