[sqlalchemy] Re: Adding additional conditions to an outer join

[Toshio Kuratomi]

Bobby Impollonia wrote:

Outerjoin takes a second argument which is the join condition. If you
want it to have multiple conditions, you can combine them into a
single condition using and_:
table1.outerjoin(table2, and_(table1.something == table2.something,
table1.somethingelse == somevalue))

Gorgeous! I was trying to use outerjoin() on the mapper before which doesn't have the "onclause" parameter so I didn't think to look for this. Here's the completed code for anyone searching the list later::

    from sqlalchemy import and_, select




    print people.all()

[(User(admin,Admin User), u'approved'),
 (User(public,Public Man), None),
 (User(toshio,Toshio Kuratomi), u'approved')]

Thanks to Bobby and Kipb for the various parts of this solution!

-Toshio

RoleGroupJoin = PersonRolesTable.join(GroupsTable, and_(PersonRoles.group_id==Groups.id, Groups.name=='test')) PeopleJoin = PeopleTable.outerjoin(RoleGroupJoin, PersonRoles.person_id==People.id) stmt = select([PeopleTable, PersonRolesTable.c.role_status], from_obj=[PeopleJoin]).order_by(People.username) people = People.query.add_column(PersonRoles.role_status).from_statement(stmt)

signature.asc
Description: OpenPGP digital signature