HI Simon, thanks or your input. Yes, that was one question i forgot to add : how to prevent caching of addresses in this case. >From my experiment, one can use session.commit/rollback/expire_all or expire(entity) specifically. That might mean caching is related to the transactions and not session ? Have you run your query ? I thought a condition was not allowed for relations, i.e should be outerjoin Address rather than User.addresses.
On Fri, May 24, 2019 at 4:26 PM Simon King <[email protected]> wrote: > (Below is just my opinion, feel free to ignore it) > > I think this example goes against some of the philosophy of > SQLAlchemy, in that "User.addresses" is not really intended to be > filtered. Since a given user can only exist once in a session, if you > load that user with a filtered "addresses" property, any subsequent > code that uses the same session to load that user from the database > will also get that filtered view, whether they wanted it or not. > > Also, I'm not sure what happens if the user has *already* been loaded > into the session; I don't know if your query will overwrite the > "user.addresses" property, or if it will leave it untouched. > > Instead, I think you should probably use something like: > > def get_city_addresses(session, city): > q = session.query(User, Address).outerjoin(User.addresses, > Address.city==city).order_by(User.id) > for user, addresses in itertools.groupby(q, key=lambda (u, a): u): > yield user, list(addresses) > > Simon > > On Fri, May 24, 2019 at 11:17 AM Manoj Mokashi <[email protected]> > wrote: > > > > About DRY : > > If i define a join condition in a relationship, but still have to define > it again in the queries, it seems to go against DRY. > > If the condition changes, i would have to change it everywhere. I > suppose we could have method like joinAddress(query) to do it in a single > place. > > > > Consider getting only Mumbai city addresses of all users. > > qry_users = session.query(User).outerjoin(User.addresses, > Address.city=='Mumbai').options(contains_eager(User.addresses)).all() > > Currently, the above is not allowed, i.e. specify additional condition > for a relation in join. > > So we have to user Address entity and add the join condition again. > > qry_users = session.query(User).outerjoin(Address, (Address.user_id == > User.id) & > (Address.city=='Mumbai')).options(contains_eager(User.addresses)).all() > > > > If in this case, it were allowed to add the condition to the relation, > it would be nice. > > > > But it still forces me to use a join, whereas my relation could be set > to load differently. > > I know query api is pretty complex and flexible, and i am new to > sqlalchemy, so i probably don't understand many things well. > > > > I was thinking that if we had a options(filterrelated(User.addresses, > Address.city=='Mumbai')), it would fetch Users, > > and for each User, it would set this filter condition in User.addresses. > > Later, when the addresses relation is to be loaded by whatever way, it > would see this filter and apply it. > > My usage then becomes : > > qry_users = session.query(User).options(filterrelated(User.addresses, > Address.city=='Mumbai')).all() > > > > > > > > On Thu, May 23, 2019 at 11:46 PM Mike Bayer <[email protected]> > wrote: > >> > >> > >> > >> On Thu, May 23, 2019, at 8:04 AM, Manoj Mokashi wrote: > >> > >> >>Was aiming for DRY( don't repeat yourself) for the relation : once i > declare the relation, i should not have to join to it again, to filter it. > >> > >> > >> DRY is about unnecessary repetition that makes it difficult to change > parts of the code later, because certain elements are repeated in many > places. That's not the case here as there are two distinct API > operations being performed on the object. > >> > >> > >> > >> To be clearer, > >> The relationship could be loaded lazy or selectin, not necessarily a > join. > >> It should load as defined, but with the filter applied. > >> > >> > >> filters don't apply to lazy or selectin loads or any other kind of load > other than contains_eager() which is against the Query as it stands, so I'm > not sure what you mean. > >> > >> > >> > >> regards, > >> manoj > >> > >> On Thu, May 23, 2019 at 10:41 AM Manoj Mokashi <[email protected]> > wrote: > >> > >> Hi Michael, > >> > >> Thanks for the quick reply as usual. > >> You have understood the use case correctly. > >> Was aiming for DRY( don't repeat yourself) for the relation : once i > declare the relation, i should not have to join to it again, to filter it. > >> The transform looks useful. > >> > >> Another thing : Is it possible to specify which relation columns to > fetch when i declare the relation ? > >> > >> regards, > >> manoj > >> > >> On Wed, May 22, 2019 at 12:05 PM Mike Bayer <[email protected]> > wrote: > >> > >> On Wed, May 22, 2019 at 1:02 AM Manoj Mokashi <[email protected]> > wrote: > >> > > >> > Hi Michael, > >> > > >> > It would be nice to have something like > options(filterrelated(relation, filter)) > >> > That way, since the join is already specified in the relation, we > don't have to add it again. > >> > Or is that difficult due to lazyloading ? > >> > >> hi there - > >> > >> you're responding to a message that is 11 years old. Can you start > >> from scratch and specify what you are looking for? > >> > >> "filterelated" as you describe would appear to correspond to: > >> > >> > >> query(Thing).outerjoin(Thing.relationship).filter(Thing.something == > >> 'bar').options(contains_eager(Thing.relationship)) > >> > >> that is, you are asking for three separate things, so there's three > >> separate methods to call. I don't see it as straightforward from an > >> API perspective to add various shortcut functions that perform these > >> tasks in a different way since this spreads confusion. > >> > >> If you'd like you make your own "filterrelated" function, do this: > >> > >> def filterrelated(relation, filter): > >> def transform(q): > >> return > q.outerjoin(relation).filter(filter).options(contains_eager(relation)) > >> return transform > >> > >> > >> then you use it like this: > >> > >> my_query = my_query.with_transformation(filterrelated(relation, filter)) > >> > >> that way you can have the funciton you want and SQLAlchemy's official > >> API doesn't have to have a confusing array of functions that all do > >> similar yet different things. > >> > >> > >> > >> > >> > > >> > Btw, when we use contains_eager() with add_entity(), it creates two > entities in the output instead of just the main one. > >> > Is that expected ? > >> > > >> > regards, > >> > manoj > >> > > >> > On Thursday, March 20, 2008 at 7:53:57 PM UTC+5:30, Michael Bayer > wrote: > >> >> > >> >> > >> >> On Mar 19, 2008, at 8:31 PM, Fotinakis wrote: > >> >> > >> >> > > >> >> > > >> >> > SELECT * > >> >> > FROM users > >> >> > LEFT OUTER JOIN > >> >> > ( SELECT * FROM addresses WHERE type = 1 ) > >> >> > AS addresses ON users.id = addresses.uid > >> >> > > >> >> > I can do this: > >> >> > > >> >> > query = > >> >> > session > >> >> > .query > >> >> > (User > >> >> > ).add_entity > >> >> > (Address).outerjoin(addresses).filter(Address.type=='home') > >> >> > > >> >> > But, that filters on the entire query, not just on the joined sub- > >> >> > query, generating something like this: > >> >> > > >> >> > SELECT * > >> >> > FROM users > >> >> > LEFT OUTER JOIN addresses ON users.id = addresses.uid > >> >> > WHERE addresses.type = 1 > >> >> > ORDER BY hosts.mac > >> >> > > >> >> > Because it's a one-to-many relationship, this query only returns > the > >> >> > users that have a home addresses ... and _excludes_ users totally > who > >> >> > have an address, but one that is not of type 'home'. I need it to > >> >> > return all users regardless (hence the LEFT JOIN) and just join > >> >> > addresses of type 1. > >> >> > > >> >> > >> >> you'd probably want to put the criterion in the ON clause: > >> >> > >> >> > >> >> session > >> >> .query > >> >> (User).add_entity(Address).select_from(users.outerjoin(addresses, > >> >> and_(Address.type=='home', Address.user_id=User.id))) > >> >> > >> >> alternatively you can shove the actual subquery in there in a few > >> >> ways, one of them is like this: > >> >> > >> >> sel = addresses.select().where(Address.type=='home') > >> >> session.query(User).add_entity(Address).outerjoin(('addresses', > >> >> sel)) > >> >> > >> >> or otherwise spell out the join to the subquery using select_from() > >> >> again. > >> >> > >> >> > >> > -- > >> > 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. > >> > To view this discussion on the web visit > https://groups.google.com/d/msgid/sqlalchemy/f10335a2-9b95-4f1c-881b-03202d697121%40googlegroups.com > . > >> > 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 a topic in the > Google Groups "sqlalchemy" group. > >> To unsubscribe from this topic, visit > https://groups.google.com/d/topic/sqlalchemy/2r-CzHOnGx4/unsubscribe. > >> To unsubscribe from this group and all its topics, 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. > >> To view this discussion on the web visit > https://groups.google.com/d/msgid/sqlalchemy/CA%2BRjkXFmGu8vUR4%3D88vP%2BZ%3Dvi-bgOjPwCgx6r912XX_MF6-VOQ%40mail.gmail.com > . > >> 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. > >> To view this discussion on the web visit > https://groups.google.com/d/msgid/sqlalchemy/CALbcBG%2B8WZoFGF_8T7OnakiQm6F83zUY%2BwGFvURjjFmCzX-XQA%40mail.gmail.com > . > >> 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 a topic in the > Google Groups "sqlalchemy" group. > >> To unsubscribe from this topic, visit > https://groups.google.com/d/topic/sqlalchemy/2r-CzHOnGx4/unsubscribe. > >> To unsubscribe from this group and all its topics, 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. > >> To view this discussion on the web visit > https://groups.google.com/d/msgid/sqlalchemy/b20d6045-5a6d-4bbf-ab50-2e203a0d6950%40www.fastmail.com > . > >> 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. > > To view this discussion on the web visit > https://groups.google.com/d/msgid/sqlalchemy/CALbcBG%2B5XcftoxL5zOGUZFf2Vntso_oMX58rj6HXjgY4LQcQKw%40mail.gmail.com > . > > 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 a topic in the > Google Groups "sqlalchemy" group. > To unsubscribe from this topic, visit > https://groups.google.com/d/topic/sqlalchemy/2r-CzHOnGx4/unsubscribe. > To unsubscribe from this group and all its topics, 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. > To view this discussion on the web visit > https://groups.google.com/d/msgid/sqlalchemy/CAFHwexefniB1%2B3_DWzyjrm8B%2Biwcb64PJZVzft8oTH%3DjLy8sDg%40mail.gmail.com > . > 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. To view this discussion on the web visit https://groups.google.com/d/msgid/sqlalchemy/CALbcBG%2BZa%2BednRBT_R2Jn3SqoVPGJGgf1%3DwD7v%2BczTL3u%2BZBzw%40mail.gmail.com. For more options, visit https://groups.google.com/d/optout.
