Pything/SQLAlchemy outer joins and custom selects

From Coder's Log

I've been trying to setup an outer join and filter records that don't exist using SQLAlchemy. The best I could come up with was a

select * from A where not exists(select from B where B.fkey=A.pkey and B.date='01/01/2009')

That i accomplished using the following syntax

session.query(A).filter(~A.childB.any(B.date=='01/01/2009'))

That worked pretty well until i had couple of hundred records in the database. One downside of using SQLAlchemy is difficulty in finding example for complex things. Here is what I figured out. for any complex sql you need to use the select object and the way use it and still end up with objects rather than result sets is

session.query(A). from_statement( outerjoin(A, B, and_(A.pkey==B.fkey,B.date=='01/01/2009')) .select()).all()

This worked out great, I get a perfect out join that works lightning fast. Overall when working with ORM frameworks you always have to aware of when to use the relationships defined in the model, and when to write your own SQL. The way I see it, anything beyond basic joins should be handled via custom SQL and SQLAlchemy makes this real easy