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
--~--~---------~--~----~------------~-------~--~----~
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
-~----------~----~----~----~------~----~------~--~---
