Allow to inject expression-based columns into Query objects / Cls.attribute.with_expression(x)
I'm trying to do something that, in principle, should be simple: inject a expression-based column into an ORM-based Query. I've tried this:
query = db.session.query(MyClass).all() # Using `now` just as an example, ideally it's something that takes a parameter # so it can't be replaced by `orm.column_property` some_expression = func.now() selectable = query.selectable # The following works as expected, injecting the expression as a column # the AnnotatedSelect object... selectable.append_column(some_expression.label('some_column')) # ... but I can't set it back on the query query.selectable = selectable query = query.order_by('some_column')
It fails with
query.selectable is read-only. I haven't found another way to inject an expression by reading the docs or the source.
Query.add_columnisn't quite the same thing since you now get a
KeyedTupleback, not a
MyClassobject with the injected attribute.
orm.column_propertyis of limited usefulness since you can't pass arguments only known at runtime instead of class-evaluation time.
I thought SQLAlchemy made things like this a breeze, but I've spent days without finding an adequate solution. Either I'm missing something, or an API for this is badly needed.