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. On Tuesday, February 7, 2017 at 12:53:53 AM UTC+3, Mike Bayer wrote: > > > > 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:> <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:> > <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] <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. > > 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.
