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/CALbcBGJWKr-t2FxP3vGrWEFLfXS8YTOmMj6m5V_wYs2jjS2bbw%40mail.gmail.com. For more options, visit https://groups.google.com/d/optout.
