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>}]
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 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
>
> > <
> 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
> > <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:>
> <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 <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.
