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 <http://docs.sqlalchemy.org/en/latest/orm/mapped_sql_expr.html#query-time-sql-expressions-as-mapped-attributes> ): 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_expressionq = 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_expressioncategories = 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. -- 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.
