Issues

Issue #3058 new

Allow to inject expression-based columns into Query objects / Cls.attribute.with_expression(x)

Henrique Carvalho Alves
created an issue

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 AttributeError since query.selectable is read-only. I haven't found another way to inject an expression by reading the docs or the source.

  • Query.add_column isn't quite the same thing since you now get a KeyedTuple back, not a MyClass object with the injected attribute.

  • orm.column_property is 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.

Comments (13)

  1. Henrique Carvalho Alves reporter

    After raising the issue on IRC, I've found an adequate solution by using column_property together with bindparam.

    class MyClass(DeclarativeBase):
        my_column = column_property(some_sql_expression(bindparam('my_param', value='', type_=String)))
    
    db.session.query(MyClass).all().params(my_param='foo')
    

    This allows to have computed columns that depend on parameters at query time without falling back to KeyedTuples, playing nice with JSON serialization, etc.

    I still don't know how that interacts with the rest of SQLAlchemy or if it's even supposed to be supported, would like to know about it.

  2. Mike Bayer repo owner

    suppose I had this query:

    sess.query(func.now(), User, Address).select_from(User).join(User.addresses)
    

    What exactly would this hypothetical "append expression, but not to the tuple" feature look like, and what would it do? How could it be presented both in the API and in documentation that it is not confusing as to its purpose vs. that of add_column(), column_property(), etc.?

  3. Mike Bayer repo owner

    Furthermore. If our User class had an existing column, or an attribute, called "name", and we then used this new option, also featuring the word "name", should it overwrite the value of the original "name" column? What if our User object is already present in the Session identity map, and already has a loaded value for "name", and even further what if that column has pending changes on it that are unflushed? Do we ignore it, overwrite the value, etc. ? I'm of course trying to illustrate that what may seem like a "simple" idea to the new user of SQLAlchemy is not at all clear cut once the full functional paradigm of the ORM (e.g. unit of work, identity map features) are taken into account.

  4. Henrique Carvalho Alves reporter

    Mike Bayer Right, I see the problem.

    It seems that the best option to have dynamic columns is to do as I did - create a column_property and provide a value to a bindparam at query time.

    Is that supported, or do you see any obvious problems? If so, I'll make a recipe for the docs.

  5. Mike Bayer repo owner

    i agree that pre-declaring something on the mapped class is an easy way to do this. We can support it more fully, as a true "placeholder" that can be targeted more accurately. Most appropriately it would be like a loader option:

    class User(Base):
        # ...
    
        special_info = deferred_expression()
    
    s = sess.query(User, Address).select_from(User).join(User.addresses).
             options(with_expression(table.c.column, User.special_info))
    
  6. Henrique Carvalho Alves reporter

    I haven't tested yet if the following works, but would that proposed API be equivalent to this?

    class User(Base):
        special_info = deferred(column_property(bindparam('foo', type_=String)))
    
    sess.query(User).options(undefer(User.special_info)).params(foo='bar')
    

    And if so, what's the problem with this? Name clashes on params?

  7. Mike Bayer repo owner

    There's nothing wrong with how you're doing it there, you can stick with that. It's just for more of an "official" API we'd do something that doesn't rely on arbitrary string names / params / stuff like that. The string name requirement would be awkward in a large scale model where one has to make sure to name everything something unique.

  8. Log in to comment