can't get it *exactly* as you have it without presenting some changes
to internals or other not simple patching. But I can get it pretty
close. Also why do we want to call with_expression(cls.thing()), and
not just cls.thing() or cls.thing.using(params)?
anyway here's one that looks like:
categories = s.query(Category).options(
with_expression(Category.number_of_acknowledged_posts, 1)
)
think of it like functools.partial() as the arguments follow the attribute.
the above use can probably be added as a @query_expression.expression
as you describe which the built-in with_expression() could look for,
though people are already so confused by hybrids as it is, maybe you
can spend some time w/ this to see if it's worth it
see below
from sqlalchemy import *
from sqlalchemy.orm import *
from sqlalchemy.ext.declarative import declarative_base
import sqlalchemy as sa
def with_expression(attr, *arg, **kw):
expr = attr.info['using'](attr.class_, *arg, **kw)
return sa.orm.with_expression(attr, expr)
def query_expression(fn):
attr = sa.orm.query_expression()
attr.info['using'] = fn
return attr
Base = declarative_base()
class Post(Base):
__tablename__ = 'post'
id = Column(Integer, primary_key=True)
text = Column(String)
category_id = Column(Integer, ForeignKey('category.id'))
category = relationship("Category")
class AcknowledgedPost(Base):
__tablename__ = 'acknowledged_post'
id = Column(Integer, primary_key=True)
post_id = Column(Integer, ForeignKey('post.id'))
customer_id = Column(Integer)
post = relationship("Post", backref="acks")
class Category(Base):
__tablename__ = 'category'
id = Column(Integer, primary_key=True)
x = Column(Integer)
y = Column(Integer)
@query_expression
def number_of_acknowledged_posts(cls, customer_id):
category_alias = sa.sql.expression.alias(cls.__table__)
return sa.select(
[sa.func.count(sa.distinct(Post.id)).label('number_of_acknowledged_posts')]
).select_from(
category_alias.join(
Post,
Post.category_id == category_alias.c.id
).join(
AcknowledgedPost,
AcknowledgedPost.post_id == Post.id
)
).where(
sa.and_(
AcknowledgedPost.customer_id == customer_id,
category_alias.c.id == Category.id
)
)
e = create_engine("sqlite://", echo=True)
Base.metadata.create_all(e)
s = Session(e)
cat1, cat2, cat3 = Category(), Category(), Category()
s.add_all([
cat1, cat2, cat3,
Post(category=cat1, acks=[AcknowledgedPost(customer_id=1)]),
Post(category=cat1, acks=[AcknowledgedPost(customer_id=1)]),
Post(category=cat1),
Post(category=cat2, acks=[AcknowledgedPost(customer_id=1)]),
Post(category=cat2, acks=[AcknowledgedPost(customer_id=1)]),
Post(category=cat3, acks=[AcknowledgedPost(customer_id=1)]),
])
s.commit()
categories = s.query(Category).options(
with_expression(Category.number_of_acknowledged_posts, 1)
)
for cat in categories:
print(cat.number_of_acknowledged_posts)
On Thu, Jun 7, 2018 at 11:50 AM, Mike Bayer <[email protected]> wrote:
> On Thu, Jun 7, 2018 at 11:21 AM, Stephen Fuhry <[email protected]> wrote:
>> Hi there! I'd like to propose an idea for an enhancement to the
>> query_expression API. I have found query_expression feature (introduced in
>> 1.2) extremely useful, however its current API is rather awkward and lacks
>> any sort of conventional organizational patterns for more common, albeit
>> advanced, use cases.
>>
>> I'd also like to add that query_expression seems to all but completely
>> eliminate the need for this horrifying, yet strangely beautiful (and very
>> powerful) strategy for adding calculated "columns" to a model that Mike
>> Bayer wrote up a good 5 years ago: https://stackoverflow.com/a/17349827
>> query_expression is also far more flexible, since the expression can be
>> modified at runtime to take session data into account (such as filtering by
>> a currently logged in user_id, for example)
>>
>> Just for a refresher, here's how the current API works (from the docs):
>>
>>
>> from sqlalchemy.orm import query_expression
>>
>> class A(Base):
>> __tablename__ = 'a'
>> id = Column(Integer, primary_key=True)
>> x = Column(Integer)
>> y = Column(Integer)
>>
>> expr = query_expression()
>>
>> from sqlalchemy.orm import with_expression
>> q = session.query(A).options(
>> with_expression(A.expr, A.x + A.y))
>>
>>
>>
>> This is really cool - but it is also a bit chaotic - there's no obvious
>> pattern for building "canned expressions." This means that developers must
>> roll their own "canned expression" pattern (if necessary) each time they
>> implement this. Let's consider this common scenario in a multi-tenant
>> blog-like application, and how I'd prefer to solve it with an improved
>> query_expression API:
>>
>>
>> import sqlalchemy as sa
>> from sqlalchemy.orm import query_expression
>>
>> class Post(Base):
>>
>> id = Column(Integer, primary_key=True)
>> text = Column(String)
>> category_id = Column(Integer, ForeignKey('category.id'))
>>
>>
>> class AcknowledgedPost(Base):
>> id = Column(Integer, primary_key=True)
>> post_id = Column(Integer, ForeignKey('post.id'))
>> customer_id = Column(Integer, ForeignKey('customer.id'))
>>
>>
>> class Category(Base):
>> __tablename__ = 'a'
>> id = Column(Integer, primary_key=True)
>> x = Column(Integer)
>> y = Column(Integer)
>>
>> @query_expression.expression
>> def number_of_acknowledged_posts(cls, customer_id):
>> category_alias = sa.sql.expression.alias(cls.__table__)
>> return sa.select(
>>
>> [sa.func.count(sa.distinct(Post.id)).label('number_of_acknowledged_posts')]
>> ).select_from(
>> category_alias.join(
>> Post,
>> Post.category_id == category_alias.c.id
>> ).join(
>> AcknowledgedPost,
>> AcknowledgedPost.post_id == Post.id
>> )
>> ).where(
>> sa.and_(
>> AcknowledgedPost.customer_id == customer_id,
>> category_alias.c.id == Category.id
>> )
>> )
>>
>>
>>
>> Now the query_expression API is much more coherent, and provides an obvious
>> way to pass in parameters for scenarios like the above:
>>
>>
>> from sqlalchemy.orm import with_expression
>> categories = session.query(Category).options(
>> with_expression(
>> Category.number_of_acknowledged_posts(current_user.customer_id)
>> )
>> ).all()
>>
>>>>> categories[0].number_of_acknowledged_posts
>> 22
>>
>>
>>
>> I'd be curious to know if anyone else has any better ideas for improving
>> this pattern. I'm not sure whether it's possible for it to work exactly how
>> I described off the top of my head (the whole decorator / class attribute
>> naming thing can get a little tricky) - but I think this should be at least
>> enough to start a conversation about the future of query_expression.
>
> I don't have time right now but I think you can make a little
> decorator right now that uses the existing feature to produce that
> exact effect. If I lose track since I am very overwhelmed right now
> please remind me.
>
>
>
>
>>
>> --
>> 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.
--
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.
