On Fri, May 24, 2019, at 6:17 AM, Manoj Mokashi 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.
so as far as contains_eager() + join() and explicitly being able to add conditions to a relatonship, a lot of people complain about this. there was an email thread about it regarding many-to-ones, and then also I proposed a feature for a one-to-many (should work with many-to-one also) version here: https://github.com/sqlalchemy/sqlalchemy/issues/4472#issuecomment-458995069 note that you *can* add conditions to a one-to-many relationship as detailed in that issue via "Class.relationship & (<some criteria>)", see the examples. I have it targeted at 1.4 but that is not necessarily where it will happen because I have a lot of other things going on for 1.4 and that feature is not a simple one. > > 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. there's still a join there. in the above proposal you'd use joinedload() and it's just one call. > 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] >>>>> > <mailto:sqlalchemy%[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] >>>>> <mailto:sqlalchemy%[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 >>> >>> <https://groups.google.com/d/msgid/sqlalchemy/CALbcBG%2B8WZoFGF_8T7OnakiQm6F83zUY%2BwGFvURjjFmCzX-XQA%40mail.gmail.com?utm_medium=email&utm_source=footer>. >>> 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 >> >> <https://groups.google.com/d/msgid/sqlalchemy/b20d6045-5a6d-4bbf-ab50-2e203a0d6950%40www.fastmail.com?utm_medium=email&utm_source=footer>. >> 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 > > <https://groups.google.com/d/msgid/sqlalchemy/CALbcBG%2B5XcftoxL5zOGUZFf2Vntso_oMX58rj6HXjgY4LQcQKw%40mail.gmail.com?utm_medium=email&utm_source=footer>. > 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/2857363f-0459-4870-96f5-db4b93c51c42%40www.fastmail.com. For more options, visit https://groups.google.com/d/optout.
