Thanks Mike. I will definitely give this patch a try. I think this is exactly what we need. Simon, thanks so much for sharing that post as well, bindparam had completely escaped my attention and that will actually solve my immediate use case.
@mike was just wondering if you could clarify something in your proposed update on 1.2 that you linked. a1 = s.query(A).options(undefer(A.x, expression=literal(17))).first() To me what this is doing is setting the property x on the Model A with the result of expression. So given a case where we have a scalar sub query it might take the form of.. a1 = s.query(Dashboard).options(undefer(Dashboard.is_user_default, expression=user_default_subquery).first() Where is_user_default is not defined on the Dashboard Model currently but will be set once the query is evaluated with the result of whatever is set to expression? On Thursday, 15 June 2017 14:53:26 UTC+1, Mike Bayer wrote: > > > > On 06/15/2017 08:25 AM, 'mike waites' via sqlalchemy wrote: > > Hi > > > > This is something i've been wrestling in different ways for some time so > > i thought it was time that i sought a definitive answer on what the > > "proper" way to do this is with the ORM. > > > > Often you'll want to have some kind of scalar sub select that computes a > > column in your query. This can be achieved easily by doing the > following > > > > | > > session.query(Model,scalar_sub_query).filter(...) > > this is likely another candidate for: > > > https://bitbucket.org/zzzeek/sqlalchemy/issues/3058/allow-to-inject-expression-based-columns > > > which is theoretically the very last bug I am to fix for 1.2 which I am > feverishly trying to get done. > > if you can try out the patch there and see if it applies to your use > case too, then that's another vote for me to do this one. > > > > > | > > > > When passing additional column properties to the Query constructor like > > this results in SQLAlchemy giving you a named tuple back. In some cases > > this is fine but notice the first argument passed was Model, this is > > because we also want the ORM to do its normal thing of populating an > > instance of Model for us from the result of the query. > > > > In some cases this can be worked around by using declared_attr. This > > will allow us to define a field that is configured at import time from a > > function or something like that. This, in my experience, is suitable in > > rare occasions. The moment you need something that is not setup at > > import time makes this unsuitable. IE suppose your column requires a > > user id to figure out the result. > > > > The next thing that comes to mind is hybrid_property. This is a lot > > closer to what we want in most cases, the function is deferred until > > query time however, to my knowledge there's no way to have SQLAlchemy > > automatically populate a Model instance property from a hybrid_property > > which result in use coming back to the original problem. Specifying > > columns explicitly results in a named tuple when we simply want our > > model instance populated. > > > > There's no doubt a way to do this but i've given up trying to find it. > > > > Hope someone can help! > > > > -- > > 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 sqlalchemy+...@googlegroups.com <javascript:> > > <mailto:sqlalchemy+unsubscr...@googlegroups.com <javascript:>>. > > To post to this group, send email to sqlal...@googlegroups.com > <javascript:> > > <mailto:sqlal...@googlegroups.com <javascript:>>. > > 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 sqlalchemy+unsubscr...@googlegroups.com. To post to this group, send email to sqlalchemy@googlegroups.com. Visit this group at https://groups.google.com/group/sqlalchemy. For more options, visit https://groups.google.com/d/optout.