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 <[email protected]>
> wrote:
> 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 [email protected].
> > To unsubscribe from this group, send email to
> > [email protected].
> > 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 [email protected].
> To unsubscribe from this group, send email to
> [email protected].
> 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 [email protected].
> To unsubscribe from this group, send email to
> [email protected].
> 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 [email protected].
To unsubscribe from this group, send email to
[email protected].
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en.
