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.
