Re: [sqlalchemy] How to do scalar sub selects with the ORM correctly?

['mike waites' via sqlalchemy]

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':  at 
0x7f72d7a451b8>}] 





On Thursday, 15 June 2017 18:22:16 UTC+1, Mike Bayer wrote:
>
>
>
> 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. 
>
>
>
>
> > 
> > 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 

Re: [sqlalchemy] How to do scalar sub selects with the ORM correctly?

[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.







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

Re: [sqlalchemy] How to do scalar sub selects with the ORM correctly?

['mike waites' via sqlalchemy]

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

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

Re: [sqlalchemy] How to do scalar sub selects with the ORM correctly?

[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+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.


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