That fixed it - thanks so much - I was really stuck on this. I actually removed the col reference <anycolumn> and just referred to the table and this was perfect.
With the col reference I was getting strange references to 'OL' and '1L' - Merv On Oct 3, 4:10 am, Conor Davis <conor.edward.da...@gmail.com> wrote: > tavol wrote: > > I want to list the all 'Groups' that 'Users' belong to as indicated by > > the User2Group link table - as well as the Groups the user does not > > belong to. This returns the desired data set except it is not filtered > > for a particular user, > > > # output not filtered for user > > s.query(Group, User2Group).outerjoin(User2Group).all() > > # psuedo output - not filtered: > > # Group 1 <Yes> > > # Group 2 <Yes> > > # Group 2 <Yes> > > # Group 3 <Yes> > > # Group 2 <Yes> > > # Group 4 <Yes> > > # Group 1 <None> > > # Group 2 <None> > > # Group 3 <None> > > # Group 4 <None> > > > If I add a filter, then then the query seems to become more like > > inner join as both Group and User2Group are filtered for user_id = 5 > > > # groups not linked to id = 5 excluded > > s.query(Group).outerjoin(User2Group).filter(User2Group.user_id==5).all > > () > > > # Output too filtered: > > # Group 2 <Yes> > > # Group 3 <Yes> > > > Any suggestions how I can return a list of all groups and show where > > there is a User2Group record for e.g. User ID 5 > > > # Desired output: > > # Group 1 <None> > > # Group 2 <Yes> > > # Group 3 <Yes> > > # Group 4 <None> > > > thanks for any suggestions > > When you want to apply a filter to the right-hand side of the outer join > but not the left-hand side, you have to include the filter as part of > the outer join's ON clause. Here is a query that should work (I'm > assuming you want True/False as your second tuple element in the > results; not tested): > > s.query(Group, User2Group.<anycolumn> != None).outerjoin(( > User2Group, > (User2Group.group_id == Group.id) & (User2Group.user_id == 5))).all() > > which should roughly translate to this SQL: > > SELECT [Group columns], User2Group.<anycolumn> IS NOT NULL > FROM Group > OUTER JOIN User2Group ON User2Group.group_id = Group.id AND > User2Group.user_id = 5 > > Note the lack of a WHERE clause in the query and SQL. > > Hope it helps, > -Conor- Hide quoted text - > > - Show quoted text - --~--~---------~--~----~------------~-------~--~----~ You received this message because you are subscribed to the Google Groups "sqlalchemy" group. To post to this group, send email to sqlalchemy@googlegroups.com To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~----------~----~----~----~------~----~------~--~---
