On 02/06/2017 04:38 PM, Евгений Иванов wrote:
why don't you use a relationship() with the bound parameter?
there's a
recipe for this
at https://bitbucket.org/zzzeek/sqlalchemy/wiki/UsageRecipes/GlobalFilter
<https://bitbucket.org/zzzeek/sqlalchemy/wiki/UsageRecipes/GlobalFilter>.
seems it's exactly what I need!
Two last questions:
1. Is it possible to specify "path" to get needed value from bound
param, so I can bind request instance for each query in my framework and
a model will get a value it needs. Something like:
|
favorite =relationship(
User,
primaryjoin=Book.id ==UserFavoriteBooks.book_id,
secondaryjoin=and_(
UserFavoriteBooks.user_id ==User.id,
User.id ==bindparam("request",lambada req:req.user.id)
)
)
|
query:
|
books =session.query(requested_class).params(request=request).all()
instead of params() I'd build a simple MapperOption that does what you
need. a bindparam() can use a lambda but it doesn't accept an argument
so that usage wouldn't work like that. Within your mapperoption you'd
extract the req.user.id and add it to params(). You would need a
MapperOption in any case if you wanted lazy loading to work (the recipe
is a little intricate in this regard).
|
and wouldn't it break if some models doesn't specify any bindparam but I
bound it to query?
you can have parameters in params() that aren't used, but also you can
look inside of query.column_descriptions to see if your entity is there.
That's all because I build query automatically in my framework base on
resource requested by user
2. Can I make a relationship that will result in bool value:
favorite = relationship(User, ...., collection_class=bool)?
well a "bool" is not a "collection". You can have a list of scalar
values with the association proxy
(https://docs.sqlalchemy.org/en/latest/orm/extensions/associationproxy.html#simplifying-scalar-collections)
but that doesn't make much sense for a "bool" because there are only two
possible values.
"favorite" here looks like you have a Book and you want a list of all
the Users who have this book as a favorite? that would just be the list
of User objects then...
Thanks
On Monday, February 6, 2017 at 10:44:42 PM UTC+3, Mike Bayer wrote:
On 02/06/2017 01:45 PM, Евгений Иванов wrote:
> By fake I mean the field is not present in db, but present in
model and
> calculated when model is loaded. Favorite is a fields for Book
model and
> can be represented by something similar to:
>
> class Book(Base):
> id = Column(...)
> name = Column(...)
> favorite_for = relationship(User, secondary=UserFavoriteBooks,
> backref=favorite_books)
> @property
> def favorite(self)
> return request.user.id <http://request.user.id> in [u.id
<http://u.id> for u in self.favorite_for]
>
> request is global here.
>
> But I wan't it to be calculated once model is loaded, I think that
could
> be achieved by column_property by select() with case() to check if
> current user is in the favorite_for list.
why don't you use a relationship() with the bound parameter?
there's a
recipe for this at
https://bitbucket.org/zzzeek/sqlalchemy/wiki/UsageRecipes/GlobalFilter
<https://bitbucket.org/zzzeek/sqlalchemy/wiki/UsageRecipes/GlobalFilter>.
I guess it's a little more up-front code than the column_property()
but once you have it, you get this cool effect:
obj = session.query(MyObject).\
options(WithFavoriteItem(request.params['favorite_id'])).\
one()
If column_property is not
> refreshed for every query it will be fine for this example (since
user
> is the same in scope of one http request, so doesn't change during
> session lifetime), but could be a problem for other models if I
compare
> (in where clause) to something with more dynamic nature.
Well your immediate use case is per-request. I'd propose YAGNI for
the
"what if? what if?" part of this because this is not really a common
use
case.
I just wonder
> if there is a mechanism in SQLAlchemy that allows to have model
fields
> that calculated at query time and can be configured before the
query is
> emitted.
that's what we're doing here w/ both the column_property you were doing
and the relationship.
If you want the column and/or relationship loaded all at once when you
do query(), and you're concerned it was already loaded, just do
populate_existing() and that will load it no matter what.
> I can extend my example: backend renders reply with list of users,
for
> each user there is a list of books they bought, and for each book
there
> is a favorite filed which tells if this book bought by this user is
> marked as favorite for him:
> [{
> name: "John Doe",
> books: [{
> name: "foo",
> favorite: true
> },
> {
> name: "bar",
> favorite: false
> }
> }]
> },
> {
> name: "Jane Roe",
> books: [{
> name: "foo",
> favorite: false
> },
> {
> name: "bar",
> favorite: true
> }
> }]
>
> On Monday, February 6, 2017 at 7:13:02 PM UTC+3, Mike Bayer wrote:
>
>
>
> On 02/06/2017 11:02 AM, Евгений Иванов wrote:
> > I need a dynamically calculated column for my model.
column_property
> > looks great for that, but problem here that it's compiled at
time
> when
> > model is mapped and I need to provide some value for
filtering at
> time
> > the request is executed. I thought that bindparam can solve
that
> problem
> > for me, but found that topic:
> >
>
https://bitbucket.org/zzzeek/sqlalchemy/issues/3620/new-query-doesnt-expire-column_property
<https://bitbucket.org/zzzeek/sqlalchemy/issues/3620/new-query-doesnt-expire-column_property>
>
<https://bitbucket.org/zzzeek/sqlalchemy/issues/3620/new-query-doesnt-expire-column_property
<https://bitbucket.org/zzzeek/sqlalchemy/issues/3620/new-query-doesnt-expire-column_property>>
>
> >
> > this is not really a great way to use column_property as
it is
> > treated like any other persisted attribute, and is not
reloaded
> > unnecessarily.
>
>
> you can use column_property with a bound parameter it just wont
> normally
> be refreshed if you query for the same object again when it's
already
> loaded. it depends on what you want things to do.
>
>
> > |
> > User:
> > id
> > name
> >
> > Book:
> > id
> > name
> >
> > UserFavoriteBooks:
> > user_id
> > book_id
> > |
> >
> > So I have many-to-many relation between users and books to
represent
> > list of favorite books for a user. Then some user queries a
book
> from DB
> > and want to know if it's in his favorite list, so I need to
query the
> > Book entry for db with some fake bool column "favorite"
which is
> checked
> > over the current user (who performed the request). Is it
possible
> to do
> > that in SQLAlchemy so the "favorite" fields is automatically
> filled with
> > correct value once I do a query for a Book?
>
> what's a "fake" column? if "favorite" is a column in
> UserFavoriteBooks
> you'd use the association object pattern, i dont see where the
> column_property/bind thing is used there.
>
>
>
> >
> > BR, Evgeny
> >
> > --
> > 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>
> <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 [email protected] <javascript:>
> > <mailto:[email protected]
<javascript:> <javascript:>>.
> > To post to this group, send email to [email protected]
> <javascript:>
> > <mailto:[email protected] <javascript:>>.
> > Visit this group at
https://groups.google.com/group/sqlalchemy
<https://groups.google.com/group/sqlalchemy>
> <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>
> <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
<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 [email protected] <javascript:>
> <mailto:[email protected] <javascript:>>.
> To post to this group, send email to [email protected]
<javascript:>
> <mailto:[email protected] <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 [email protected]
<mailto:[email protected]>.
To post to this group, send email to [email protected]
<mailto:[email protected]>.
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 [email protected].
To post to this group, send email to [email protected].
Visit this group at https://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.
