How to do scalar sub selects with the ORM correctly?

[mike waites]

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(...)

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!

[mike waites]

Interestingly I've just discovered the concept of Bundles.  http://docs.sqlalchemy.org/en/latest/orm/loading_columns.html#column-bundles

Im guessing this could be used to have it populate the model instance with the extra columns ?

[Simon King]

You might be interested in this thread:

https://groups.google.com/forum/#!msg/sqlalchemy/8z0XGRMDgCk/tazeIUsdDgAJ

Simon

> --
> 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.
> To post to this group, send email to sqlal...@googlegroups.com.
> Visit this group at https://groups.google.com/group/sqlalchemy.
> For more options, visit https://groups.google.com/d/optout.

[mike bayer]

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

> <mailto:sqlalchemy+...@googlegroups.com>.

> To post to this group, send email to sqlal...@googlegroups.com

> <mailto:sqlal...@googlegroups.com>.

[mike waites]

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?

> <mailto:sqlalchemy+unsub...@googlegroups.com>.

[mike bayer]

On 06/15/2017 11:09 AM, 'mike waites' via sqlalchemy wrote:
> 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?

The plan is that Dashboard will have an attribute is_user_default at all
times, that normally would just return None unless the query sets it to
have some value. The undefer() use above will be refined with a new
function with_expression() that is more specific to this setup.

There's not an option for the attribute to be added to the Dashboard
object *dynamically*, that is, "Dashboard.is_user_default" needs to be a
known name up front.

> > an email to sqlalchemy+...@googlegroups.com <javascript:>
> > <mailto:sqlalchemy+...@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

> <https://groups.google.com/group/sqlalchemy>.

> > For more options, visit https://groups.google.com/d/optout

> <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+...@googlegroups.com

> <mailto:sqlalchemy+...@googlegroups.com>.

[mike waites]

Ah right OK that make sense.  I was going to say that using undefer felt kind of odd given that there's no existing property so this makes much more sense.

Unfortunately I need to find a solution that works in the mean time until your patch is merged so whilst i have you maybe you can explain why my approach with bind params doesn't work (leveraging the approach in the post Simon) 

    @declared_attr

    def is_user_default(cls):

        tbl = UserDefaultDashboard.__table__

        user_default_stmt = select([tbl.c.id]).where(

            tbl.c.id == cls.id

        ).where(

            tbl.c.user_id == bindparam('request_user_id', lambda user_id: user_id or cls.user_id)

        )

        return orm.column_property(exists(user_default_stmt), deferred=True)

I've got a declared_attr setup.  Querying for this works exactly as i expect using `.params(request_user_id=user.id)` however, Creating a new instance of Dashboard and then accessing it's properties will cause this attr to be invoked with the bindparam being passed the lambda function directly.  At first i thought i could solve this by deferring the column_property but it seems whenever *any* property on the model is called is_user_default will be called as well??

Here's the query 

    @classmethod

    def get_user_dashboards(cls, user):

        stmt = db.session.query(cls).join(

            Person, Person.id == cls.user_id,

        ).join(

            Company, Company.id == Person.company_id,

        ).filter(

            or_(

                cls.user_id == user.id,

                and_(

                    cls.shared_with_company == true(),

                    Company.id == user.company.id

                ),

                cls.shared_with.any(Person.id.in_([user.id]))

            ),

            cls.is_deleted == false()

        ).params(

            request_user_id=user.id

        ).options(

            orm.undefer('is_user_default')

        )

        return stmt

Any point doing the following will raise a ProgammingError 

obj = Dashboard(**params)
session.add(obj)
session.commit()

# now accessing an attribute on obj
obj.user_id

ProgrammingError: (psycopg2.ProgrammingError) can't adapt type 'function' [SQL: 'SELECT EXISTS (SELECT user_default_dashboard.id \nFROM user_default_dashboard \nWHERE user_default_dashboard.id = dashboard.id AND user_default_dashb

oard.user_id = %(request_user_id)s) AS anon_1, dashboard.is_deleted AS dashboard_is_deleted, dashboard.name AS dashboard_name, dashboard.description AS dashboard_description, dashboard.user_id AS dashboard_user_id, dashboard.shared_with_c

ompany AS dashboard_shared_with_company, dashboard.id AS dashboard_id, dashboard.created_at AS dashboard_created_at, dashboard.updated_at AS dashboard_updated_at \nFROM dashboard \nWHERE dashboard.id = %(param_1)s'] [parameters: {'param_1

': 'fYW49zSa52J7cZcszR6A9V', 'request_user_id': <function <lambda> at 0x7f72d7a451b8>}] 

>      > <mailto:sqlalchemy+unsub...@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
>     <https://groups.google.com/group/sqlalchemy>.
>      > For more options, visit https://groups.google.com/d/optout
>     <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+...@googlegroups.com

> <mailto:sqlalchemy+unsub...@googlegroups.com>.