Dynamic column for a model

[Евгений Иванов]

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

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. 

so I can't find any proper way to do it, I don't want to use plain descriptor property as I have thousands of entries for the model and I don't think it's a good idea to emit a new query for each entry.

Here is a simplified example of my models:

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?

BR, Evgeny

[mike bayer]

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
>
> 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 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>.
> Visit this group at https://groups.google.com/group/sqlalchemy.
> For more options, visit https://groups.google.com/d/optout.

[Евгений Иванов]

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

}

}] 

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

[mike bayer]

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

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

[Евгений Иванов]

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.

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

and wouldn't it break if some models doesn't specify any bindparam but I bound it to query?

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)?

Thanks

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

[mike bayer]

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

> > > <mailto:sqlalchemy+...@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>
> > <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 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>.

[Евгений Иванов]

Thanks a lot for your help! I will check how I can use MapperOption to extract filter 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... 

for that kind of relation I used name "favorite_for" in my prev examples. What I need is a bool value indicating if this book is marked as favorite for the user requesting it via UI. So in last example I used name "favorite" because I managed to apply filtering to the relation with your help, so it will return list of one item or empty list which I can easily cast to bool.

Thanks once again.

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

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